Excel to SQL 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

Excel to SQL OpenRowSet returns NULL values

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-12 : 22:50:19

I try to insert data from Excel file to SQL 2008 DB table and it returns NULL values for columns F8 to F12 while I see data in the Excel File.

INSERT INTO tbl_BW_RawData_Temp
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;HDR=YES;IMEX=1', 'SELECT * FROM [Report 1$]')

Any Idea?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 06:47:25

Does that happen for every row, or only for some rows?

I have seen this happen when, for a given column, your data is numeric in the first few rows and then has non-numeric data in subsequent rows. In such cases, Excel samples the data in the first few rows and determines that the data type is numeric. Then when it encounters non-numeric data, it returns nulls instead of the non-numeric data.

If that is the case, you can format the excel sheet/columns as text and run the query again.

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-13 : 08:01:56

You got it right Sunitabeck!

Now I need to figure out how to format the cells a in Text format before I insert it with an OpenRowSet select.

Thanks a lot!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 08:34:06

Usually I open the excel sheet, right-click the columns of interest and from "Format Cells" menu select Text and save.

infodemers
Posting Yak Master

183 Posts

Posted - 2012-11-13 : 14:09:16

I meant to say , how to get it done from a SQL code action.
Like from a vb.net Script Task or something else before I run the Insert Into select from openrowset....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 14:14:46

I don't think there is any SQL native commands that will let you control that. You could use the Excels COM object model to access the range and format it. For reasons completely not clear to me, I derive very little pleasure from working with the Excel object model, so I can't offer any code snippets, but I saw that there are plenty when I googled.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources