SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excel to SQL OpenRowSet returns NULL values		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
168 Posts
Posted - 11/12/2012 :  22:50:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  06:47:25  Show Profile  Reply with Quote
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.
Go to Top of Page

infodemers
Posting Yak Master

Canada
168 Posts
Posted - 11/13/2012 :  08:01:56  Show Profile  Reply with Quote
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!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  08:34:06  Show Profile  Reply with Quote
Usually I open the excel sheet, right-click the columns of interest and from "Format Cells" menu select Text and save.
Go to Top of Page

infodemers
Posting Yak Master

Canada
168 Posts
Posted - 11/13/2012 :  14:09:16  Show Profile  Reply with Quote

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....
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  14:14:46  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000