October 16, 2006 at 9:41 am
Greetings,
I'm trying to select all records from an Excel 2000 spreadsheet (using SQL Server 2000) with the SQL below. However, for some reason I keep receiving the error: "[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'SurveyResults'. Make sure the object exists and that you spell its name and the path name correctly.]"
SQL:
Select ... FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=\\server3\tables\Survey_Results_(10-13-06).xls', 'Select * from SurveyResults')
I have verified that the sheetname is "SurveyResults" and that the workbook is in the correct path. Any suggestions? [Note: I have also tried putting brackets around the sheetname, but this still fails].
Thanks in advance!
October 17, 2006 at 6:34 am
That could be because 2000 is not 8 more like 6 but here is another code you could modify and try. Hope this helps.
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
Kind regards,
Gift Peddie
October 17, 2006 at 9:46 am
Thanks so much for the code. I appreciate it. I also got it working using the original code. I simply had to place a "$" sign after the sheet name (as in the example below).
Select ... FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=\\server3\tables\Survey_Results_(10-13-06).xls', 'Select * from SurveyResults$')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply