Problem with excel sheet upload into sqlserver

[helplakshmi]

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.

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 or suggest me an alternative approach..

[ck9663]

Is it NULL before or after you update it?

~~ CK

[helplakshmi]

@ck9663
Once i upload the excel file, the value of this date field is NULL. Even after update the value is NULL itself.

[ck9663]

How does your date field in excel look like? Is it an actual date? Check the dateformat setting on your sql server.

Good Luck!!!

~~ CK

[Jerry Winston]

Can you post a sample of what you get back when you run this:

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

The upload is working if you're getting back something like this:

2010-05-09 00:00:00.000
2010-05-10 00:00:00.000
2010-05-11 00:00:00.000
2010-05-12 00:00:00.000
2010-05-13 00:00:00.000

Here's the catch. The data type for the [Creation date] in your temp table is varchar. The length of the varchar field directly affects how much of the date string from the Excel file you can store before the data is truncated. If your [Creation date] field is defined as VARCHAR, you're only keeping the first character of the date string from Excel. Further, the line:

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

does not convert datetime to varchar because the [Creation date] field is ALREADY a varchar type. If you want to store a formatted date value in a varchar type field you would need to convert your varchar to a datetime and apply the datetime formatting with something like this:

UPDATE temp
SET [Creation date] = CONVERT(varchar,convert(datetime,[Creation date]),101)

The size, VARCHAR(20), I chose is completely arbitrary. However, the minimum size required depends on the date format you choose. VARCHAR(20) will work fine with some formats(101,102,104) but will be too short for others (109,113). If Excel date data was stored in a datetime type field in the SQL table, the field length would be consistent and post-import UPDATEing would become irrelevant because the storage format would be consistent as well. you would need to format the [Creation date] field only for your output:

SELECT convert(varchar,[Creation date],101) FROM [temp]