-
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.
-
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!
-
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.
-
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.
-
-
Glad to know and Happy SQLing!
-
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
-
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
-
Forum Rules
|
|