I'm trying to convert an Excel document into a table in SQL 2005. I found the link below and am wondering if it looks like a solution. If so, what would the @excel_full_file_name syntax be and where would the path be relative to?
http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html
You can use the BULK INSERT T-SQL command if you just want a pure sql solution. You have to save the file as csv/text first.
BULK
INSERT YourDestinationTable
FROM 'D:\YourFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Alternatively, you can try OPENROWEST - again , a pure T-SQL solution.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=D:\YourExcelFile.xls', 'Select * from YourExcelFile')
It really depends on how much control and flexibility you want, the SSIS route will have benefits over these methods.