Importing data from Excel, using SQL.
note: In SSMS, there is a wizard in that builds an SSIS package, however that can be less flexible + more work to maintain, than a simple SQL script.
DROP table SQLServerTable1
select *
into SQLServerTable1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testExportToExcel_edited.xls;HDR=NO;IMEX=1',
'SELECT * FROM [testExportToExcel$]')
--note the name of the spreadsheet is 'testExportToExcel'
select * from SQLServerTable1
DROP table SQLServerTable1
note: In SSMS, there is a wizard in that builds an SSIS package, however that can be less flexible + more work to maintain, than a simple SQL script.
DROP table SQLServerTable1
select *
into SQLServerTable1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testExportToExcel_edited.xls;HDR=NO;IMEX=1',
'SELECT * FROM [testExportToExcel$]')
--note the name of the spreadsheet is 'testExportToExcel'
select * from SQLServerTable1
DROP table SQLServerTable1
notes:
OPENROWSET() can be quite problematic to use.
In particular, the errors coming back can be totally useless.
Tips:
- use SysInternals processexp.exe to monitor SQL Server file activity, to see is it encountering permissions errors
- check is the Excel file actually at the right location, and is the sheet name correct (with a $ suffix)
- check are there any 'garbage' rows in the Excel spreadsheet that cannot be parsed by Jet
- try changing the internal Excel version "Excel 8.0". For example Excel 2003 is internal version 11.
reference: http://support.microsoft.com/kb/321686
reference: http://support.microsoft.com/kb/321686
Comments
Post a Comment