Results 1 to 8 of 8

Thread: How to query an excel file by using openrowset

  1. #1
    Join Date
    Mar 2007
    Posts
    8

    How to query an excel file by using openrowset

    Hi everyone,

    I'm trying to query an excel file and I get a mistake. The query is as follows:

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\ExcelFile.xls', 'select * from Sheet1')

    and I get the following error message:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
    [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

    I'm thanking any help that you can give.

    Thanks,

    Oscar.

  2. #2
    Join Date
    Feb 2007
    Posts
    6

    Import from Excel

    Hi Oscarj_col

    I suspect from what I've tried that the error is due to syntax.

    Try the following form where I've altered the sheet reference.

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\ExcelFile.xls', 'select * from [Sheet1$]')

    It worked for me.

    Good luck!

  3. #3
    Join Date
    Mar 2007
    Posts
    8
    Hi citrus.

    I didn't have luck, I'm getting the same message. is it possible that I don't have some necessary software installed? you know it?

    Thanks again.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Is excel file on sql server's c:\ drive? If not, you have to use unc name for file path and ensure sql service account has permission to access that path.

  5. #5
    Join Date
    Mar 2007
    Posts
    8
    Quote Originally Posted by rmiao
    Is excel file on sql server's c:\ drive? If not, you have to use unc name for file path and ensure sql service account has permission to access that path.

    EXCELLENT!!!!!!


    Sometimes when I spend a lot of time working, I can't see things that are very simple.

    Thanks dear forum partners, specially rmiao. That was the problem.

    Regards.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Glad to know and Happy SQLing!

  7. #7
    Join Date
    Jan 2007
    Posts
    2

    About OpenRowSet()

    Oscar

    See if you find the file using master.dbo.xp_cmdshell 'dir C:\ExcelFile.xls'

    If the Sql Server find the file so, open the file in Excel and see the name of Sheet.

    Wrote in your query analyser the script below:

    SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\ExcelFile.xls";
    Extended properties=Excel 8.0')...Plan1$

    --My Excel file name ExcelFile.xls and the name of the sheet is Plan1

    If your have the message about permission, see the KB below
    http://support.microsoft.com/kb/327489

    bye
    Fernando

  8. #8
    Join Date
    Mar 2007
    Posts
    8
    Thanks Fernando, It helps me a lot.

Posting Permissions

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