Jump to content

Archived

This topic is now archived and is closed to further replies.

help_lucky

Problem with excel sheet upload into sqlserver

Recommended Posts

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..

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.