Select * from openrowset returns Null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select * from openrowset returns Null values

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-09 : 11:55:05

Hi,
When I run the following, columns F10, F11 and F12 have Null values while data is present in the Excel file.

SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,'' AS [Date Upload]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\blmcik\BWAsset\BW_GWC.xls;IMEX=1;HDR=YES', 'SELECT * FROM [Report 1$]')
WHERE [F1] <> 'Clli Code'

I also try the following with same results:

SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,'' AS [Date Upload]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
     'EXCEL 12.0;Database=\\blmcik\BWAsset\BW_GWC.xls;
     HDR=YES;IMEX=1','SELECT * FROM [Report 1$]')
WHERE [F1] <> 'Clli Code'

Any Idea?
Thanks!

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-09 : 12:17:52

I found out.
I changed the cells format to text for those 3 columns in the Excel files and everything worked fine now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources