Excel Spreadsheets (2003 format)
The Excel spreadsheet examples assume that an Excel spreadsheet (2003 format – .xls) has been created at C:\Temp\test.xls. Sheet1 has two columns: name and object_id. Sheet 2 has the same two columns, and the sheet has been renamed to have a space: “Sheet 2”. (In Excel objects, $ represents worksheets, no $ represents named ranges)
32-bit, using the Microsoft.Jet.OLEDB.4.0 driver with OPENDATASOURCE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- insert into an excel spreadsheet file - file must exist with proper column names and be closed INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source = C:\Temp\test.xls;Extended Properties = Excel 8.0')...[Sheet1$] (object_id, name) SELECT object_id, name FROM sys.tables; INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\test.xls')... [Sheet1$] (object_id, name) SELECT object_id, name FROM sys.tables; -- select from excel spreadsheet file SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source = C:\Temp\test.xls;Extended Properties = Excel 8.0')...[Sheet1$] |
32-bit, using the Microsoft.Jet.OLEDB.4.0 driver with OPENROWSET:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- works with spaces in the worksheet name INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\test.xls', 'select * from [Sheet1$]') (object_id, name) SELECT object_id, name FROM sys.tables; SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\test.xls', 'select * from [Sheet1$]'); -- will not work with spaces in the worksheet name SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\test.xls', [Sheet1$]); -- to use a range of cells, change [Sheet1$] to 'select * from [Sheet1$C1:D1]' |
32-bit, using the MSDASQL driver:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
INSERT INTO OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Database=C:\Temp\test.xls;', 'select * from [Sheet1$]') (object_id, name) SELECT object_id, name FROM sys.tables; INSERT INTO OPENDATASOURCE('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Database=C:\Temp\test.xls;')...[Sheet1$] (object_id, name) SELECT object_id, name FROM sys.tables; -- select from file SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Temp\test.xls;', 'select * from [Sheet1$]'); |
32/64-bit using the Microsoft.ACE.OLEDB.12.0 driver:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\test.xls')...[Sheet1$] -- works with space in worksheet name INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\test.xls', 'SELECT * FROM [Sheet 2$]') (object_id, name) SELECT object_id, name FROM sys.tables; SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\test.xls', 'SELECT * FROM [Sheet 2$]'); |
Retrieving Excel Spreadsheet names using the Microsoft.ACE.OLEDB.12.0 driver:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
EXECUTE sp_addlinkedserver 'ExcelShare', 'Jet 12.0', 'Microsoft.ACE.OLEDB.12.0', '\\<full pathname of excel file>.xlsx', NULL, 'Excel 12.0'; -- requires SSMS being run as an administrator EXECUTE master.sys.sp_tables_ex ExcelShare; -- to dynamically do something with the sheet names: DROP TABLE IF EXISTS #ExcelTables; CREATE TABLE #ExcelTables ( TableCat sysname NULL, TableSchema sysname NULL, TableName sysname NULL, TableType sysname NULL, Remarks varchar(8000)); -- requires SSMS being run as an administrator INSERT INTO #ExcelTables ( TableCat, TableSchema, TableName, TableType, Remarks) EXECUTE master.sys.sp_tables_ex ExcelShare; |
