Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    11

    Unanswered: import data from excel to SqlServer

    Hello,

    I want to import data from an excel sheet to SqlServer....
    I use a linked server...
    I execute the following code:

    EXEC sp_addlinkedserver 'ExcelSource',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\MyExcel.xls',NULL,
    'Excel 5.0'
    GO

    sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
    GO

    SELECT * FROM ExcelSource...Sheet1$
    GO

    and I get the error:

    Server: Msg 7314, Level 16, State 1, Line 2
    OLE DB provider 'ExcelSource' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.
    OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='Sheet1$'].

    When I execute the command:

    select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls',Sheet1$)

    I get the same error...

    Can anyone help me?

    Thanks
    Korina

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Try using OPENQUERY.

    SELECT * FROM OPENQUERY('ExcelSource','SELECT * FROM SHEET1$')
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2004
    Posts
    11
    I get the error:

    Server: Msg 7403, Level 16, State 1, Line 2
    Could not locate registry entry for OLE DB provider 'c:\book1.xls'.
    OLE DB error trace [Non-interface error: Provider not registered.].

    What I am doing wrong?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Make the following changes.

    sp_addlinkedserver 'ExcelSource6',
    'Excel',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\MyExcel.xls',
    NULL,
    'Excel 8.0'

    SELECT * FROM OPENQUERY(ExcelSource6,'SELECT * FROM [Sheet1$]')
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Oct 2004
    Posts
    11
    and now I get the error:

    Server: Msg 7399, Level 16, State 1, Line 8
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
    [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Mine works. A couple of questions for you.

    Do you or someone else or another program have the file open?

    Do you realize the path you define (c:\MyExcel.xls) is relative to the server and not to your client machine? This the servers C:\ drive.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Oct 2004
    Posts
    11
    The file is close and it is placed on the specified server drive...
    Do you have any other idea?

    I would be gratefull because I need it as soon as possible.

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Take a look at this:

    http://support.microsoft.com/default.aspx?scid=314530

    Ahhh Google..... Who needs to know anything anymore? programming before high speed internet access was such a pain.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •