Post reply

Bulk insert from excel 2003 failed

  • January 4, 2008 at 9:07 am

    #123095

    INSERT INTO dbo.ImportTest

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

    'Excel 8.0;Database=C:\test.xls', [Sheet1$])

    Got the following error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    I have this one excel file that I need to import into a table. I tried the above statemnt and it failed... Couldn't figure out why.

    Any one can help me? Thanks in advance!

  • January 4, 2008 at 9:54 am

    #764897

    Check this link.

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;296711

    Also try changing the name of the sheet1$ or just put one sheet instead of 3 and check when you are executing the excel file is closed.

    SQL DBA.

  • January 4, 2008 at 10:20 am

    #764909

    $sanjayattray (1/4/2008)

    Check this link.

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;296711

    Also try changing the name of the sheet1$ or just put one sheet instead of 3 and check when you are executing the excel file is closed.

    check when you are executing the excel file is closed.

    This helped! thanks

  • January 4, 2008 at 7:59 pm

    #765019

    How did it help? What was the problem?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 6, 2008 at 9:32 pm

    #765210

    Save the Excel sheet as Csv format ant try this way

    BULK INSERT AdventureWorks.Sales.SalesOrderDetail

    FROM 'f:\orders\lineitem.tbl'

    WITH

    (

    FIELDTERMINATOR =' |',

    ROWTERMINATOR =' |'

    )

    It ill Surely help u

  • January 9, 2008 at 9:11 am

    #766276

    Hello Comunity

    I´am curious about this kind of method to import into SQL 2005 an Excel Sheet.

    I try this script :

    INSERT INTO dbo.st

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

    'Excel 8.0;Database=C:\test.xls', [sheet1$])

    but i have the following error :

    37000(7405)[Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (0,09 secs)

    Someone could help me

    Many thanks

    Luis Santos

  • January 9, 2008 at 5:08 pm

    #766436

    but i have the following error :

    37000(7405)[Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (0,09 secs)

    Should be fairly obvious... you need to turn these options ON for the query to work... lookup the two settings and the SET command in Books Online to learn more.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 9, 2008 at 9:33 pm

    #766459

    Jeff Moden (1/4/2008)

    How did it help? What was the problem?

    I had the file open. Once I closed it, it imported fine...:)

  • March 21, 2008 at 3:34 am

    #792375

    I have the same issue, but I'm sure my file is closed. Could it have something to do with credentials (even though the excel file is on the same server as the SQL db). I don't have Excel installed because it's a server. Please don't say that it requires that to work ?

    Been searching for a while now..... 🙁

    Kind regards,

    Koen

  • March 21, 2008 at 5:54 pm

    #792755

    Yan Gao (1/9/2008)

    Jeff Moden (1/4/2008)

    How did it help? What was the problem?

    I had the file open. Once I closed it, it imported fine...:)

    Heh... I had the same problem the first couple of times I did something similar... 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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