CODEWITHSUNDEEP
sqlexcelsql-server-2005database-table
madcolor
madcolor

Reputation: 8170

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?

http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html

Upvotes: 5

Views: 15104

Answers (4)

Cheeso
Cheeso

Reputation: 192477

This example copies data from SQL to Excel. But it is just a matter of swapping the OleDb providers to get it to work in the opposite direction.

Upvotes: 0

Coolcoder
Coolcoder

Reputation: 4036

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.

Upvotes: 7

D3vtr0n
D3vtr0n

Reputation: 2909

I would suggest using an SSIS/DTS Package, to convert. It's much easier.

SSIS Excel example

** note that this example is using the wizard. you can schedule the SSIS/DTS package as a job to run, on your SQL box.

Upvotes: 1

BenAlabaster
BenAlabaster

Reputation: 39836

Glancing over the code, I would expect it to be the full path name of the excel document:

For example: c:\path\to\my\excel\document.xls

I haven't installed the procedure though or run it, so I could be wrong - but that's what it appears to be at first glance.

Upvotes: 2

Related Questions