Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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_TempSELECT 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.