SQL Server Performance Forum – Threads Archive
Bulk Insert from Excel
Hi, I’m having a slightly annoying problem with importing data from Excel in SQl. My data is in columns A and B of Sheet1 of an Excel spreadsheet. The code I have come up with is: bulk insert [db].[user].table_tfrom ‘\addressexcel.xls’
with
(
datafiletype = ‘char’,
firstrow = 2,
fieldterminator = ‘ ‘,
rowterminator = ‘
‘
) This works fine for an equivalent text file, but I don’t want to have to export from Excel to the text file and then import to SQL. I’m fairly sure that I’ll have to do something with altering the delimiters. Both columns contain text data. Thanks
http://www.sqlteam.com/item.asp?ItemID=3207 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks, but I can already do the process fine for a txt file, just not an xls.
Are you getting any error in this case?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
not syntax wise. I am getting (0 rows affected) rather than the usual (770 rows affected) that the text version creates.
An Excel sheet is not a flat text file, so try OPENQUERY, OPENROWSET or OPENDATASOURCE instead.
]]>