Post reply

How to import data from Excel file on local machine to Sql Server using Query

  • February 28, 2010 at 7:48 am

    #386407

    Hi !

    I am trying to import excel table into my sql database.

    My excel file is on my local machine and I tried this code :

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=Q:\Documents and Settings\Administrator\My Documents\myfile.xls', 'SELECT * FROM [QPrintPolicyRenew$]')

    I keep getting this error :

    Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    Can anyone help ??

  • February 28, 2010 at 9:12 am

    #1126262

    This might help you.

    http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx

    Basically you need to add to your openrowset command is: ;HDR=No

    Excel 8.0;DATABASE=c:\temp\items.xls;IMEX=1;HDR=No

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 28, 2010 at 10:05 am

    #1126277

    Thanks for your reply !

    I've tried this :

    SELECT *

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=Q:\Documents and Settings\Administrator\My Documents\myfile.xls;IMEX=1;HDR=No',

    'SELECT * FROM [QPrintPolicyRenew$]')

    and I'm still getting this :

    Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

  • February 28, 2010 at 10:39 am

    #1126287

    gil_500

    Is the EXCEL work book on the same server as is your instance of SQL Server?

    It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine. The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 2, 2010 at 2:14 pm

    #1127413

    Your server Is 32-Bit or 64-Bit?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy