Post reply

Help troubleshooting data import from Excel into existing table

  • November 10, 2008 at 2:59 am

    #71675

    Hi everybody,

    I am trying to load data from and excel 2000 file into its respective table, but I get the following error: "[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]"

    Here's the T-SQL

    Insert Into MyTable

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

    'Excel 8.0;Database=C:\BlankDB.xls', 'SELECT * FROM [Sheet1$]')

    The fiel, and the sheet do exist, I have tried changing sheet's name, but get the same error.

    Can anybody advise?

    Thanks.

  • November 10, 2008 at 4:41 am

    #896198

    Insert Into MyTable

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

    'Excel 8.0;Database=C:\BlankDB.xls', Sheet1$)

  • November 10, 2008 at 4:54 am

    #896205

    chek the link, and modify it: http://www.mssqltips.com/tip.asp?tip=1202.

  • November 12, 2008 at 2:07 am

    #897382

    Thanks. I'll look into it.

  • November 13, 2008 at 5:36 am

    #898006

    The single quotes around the sheet name are part of the name, for example:

    FROM [DEFS]...['Table Download$']

    You will see that if you chose "Script table as/SELECT to/New Query Editor Window" after right clicking on the table (tab) name for the Linked Server.

  • November 13, 2008 at 6:43 am

    #898036

    mutekinohito (11/10/2008)

    Hi everybody,

    I am trying to load data from and excel 2000 file into its respective table, but I get the following error: "[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]"

    Here's the T-SQL

    Insert Into MyTable

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

    'Excel 8.0;Database=C:\BlankDB.xls', 'SELECT * FROM [Sheet1$]')

    The fiel, and the sheet do exist, I have tried changing sheet's name, but get the same error.

    Can anybody advise?

    Thanks.

    More informations

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

    Madhivanan

    Failing to plan is Planning to fail

  • November 13, 2008 at 11:58 pm

    #898418

    I see you're using the normal denotation c:\...

    This means your file is in the root directory of the C drive of the SQL server machine, and the userid used to start the SQL server instance must have read access to that file. If the file is on the machine where you run your query analyzer, your filename should be like '\\machine\sharename\blankDB.xls'

    I hope this is of assistance !

  • November 14, 2008 at 2:15 am

    #898449

    Thans, I was indeed using a remote machine to run the script. It worked just fine when I did it inthe server itself.

    I'll try again from my machine using your suggestion.

    Again, thank you very much. You guys do know your stuff 🙂

    Alex.

Viewing 8 posts - 1 through 7 (of 7 total)

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