November 15, 2007 at 2:52 pm
I am trying to use this statement to read the data from an xls file for processing:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\exceltemp\xlsfile.xls', 'Select * from [xlsfile$]')
And most of the data is being read properly, but some cells are coming in as null despite having a value in the excel file. I do not know if it would matter, but all the data which is not being read properly is numeric data stored in cells with number of general. Does anyone have any idea why this would happen and more importantly how to fix it?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 15, 2007 at 9:40 pm
Hi timothyawiseman ,
Check this link you have the solution http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx
Your query will be
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\exceltemp\xlsfile.xls;IMEX=1', 'Select * from [xlsfile$]')
Regards,
Ahmed
November 17, 2007 at 11:31 pm
That works perfectly. Thank you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 19, 2011 at 9:44 am
Wow! that is still helpful 4 years later!
Great tip.
Thanks alot! 🙂
Sylvain Audet
CEO - MyDevPartner.Com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply