Problem with excel sheet upload into sqlserver

Question

Hello Everyone,

I need to upload excel sheet in to the database. Which i am doing with the query

SELECT * INTO temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls',
    'SELECT * FROM [qry_BA_Controlling (Report)$]')

Here C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls is the path from where the excel file needs to be fetched. qry_BA_Controlling (Report) is the name of the worksheet.

So on executing the query, a table with name 'temp' is created. With records that are populated from excel.

Now here i have a date field in excel. sometimes the values of this field are not uploaded properly into the temp table. The values for this date field are set to NULL eventhough they have values in EXCEL.

EDIT I have modified my query so,

Insert into temp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls',
     HDR=YES', 'SELECT * FROM [qry_BA_Controlling (Report)$]')

Here temp is an existing table, i have defined the date type of the field [creation date] to varchar and uploaded the excel. Then i used convert to change the datatype to the correct format..

update temp set [Creation date] = CONVERT (varchar,[Creation date],101)

Even now it is populating NULL values..Or this conversion needs to be done while uploading. if so, please let me know.

I think the reason behind this is, the first few values of this column are spaces and after that it has values..As it determines the datatype by checking first few rows..Then it is assigning NULL..Please suggest me an alternative.

Answer 1

Firstly beware this approach will not work on x64 editions of SQL Server as there is no x64 MS Excel driver available. I've seen people get caught out with this when writing something on 32bit and then deploying to x64 in production.

You're using a SELECT INTO which I think means that the Excel driver is looking at the first 100 rows (or similar) and determining that this field is a date.

I think it's most likely that SQL Server is then getting the date format wrong, you may find that the values that are NULL are ones that don't conform to US datetime formatting. So we (Europeans) would accept 31/7/2010 however this is unacceptable MM/dd/yyyy format.

My suggestion would be to pre-define the temp table with all fields either int, float, or varchar. Then manually convert them (using a view if convenient) to the correct types. If I am correct about the date format being the issue you can use the CONVERT function to force the correct date format to be used.

Answer 2

I have modified the query so, now it is able to import the data from excel.

EXEC('SELECT * INTO temp FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @ba_bm_status + ';HDR=YES;IMEX=1'',' + '''SELECT * FROM [qry_BA_Controlling (Report)$]'')');

I have included IMEX=1, which tells the driver to use import mode.