madcolor madcolor - 10 months ago 50
SQL Question

Creating a SQL table from a xls (Excel) file

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?

Answer Source

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.

INSERT YourDestinationTable
        FROM 'D:\YourFile.csv'
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'

Alternatively, you can try OPENROWEST - again , a pure T-SQL solution.

'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.