Import Excel data into a table with T-SQL

Question

Import Excel data into a table with T-SQL

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
November 30, 2009 at 3:44 am

#206787

Hi,
On my SQL2005 I can import data from a XLS to a table with TSQL using this code:
SELECT * INTO [mytable] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=\\webcl\test\test.xls', [adresses$])
On SQL2008 (running on Win Server 2008) I get this error:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
On internet I read lots of stuff about this not being possible for 64 bit SQL... is that true?
I need to do it with T-SQL because my customers first upload a XLS, then press a button that loads the data in a SQL Table.
Please help!
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I'm going to make a guess that your SQL 2005 was 32-bit and your SQL 2008 is 64-bit..

Unfortunately I am not aware of any way around this using the OPENROWSET method, I'm not even sure of a way that you could pull it off using SQL CLR. But you might be able to pull it off using SSIS (using the 32-bit DTExec) and xp_cmdshell or service broker with external activation..

CEWII

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

"I need to do it with T-SQL because my customers first upload a XLS, then press a button that loads the data in a SQL Table."

If it is possible to save the xls file as a tab separated text file you can use bulk insert and T-SQL.

You can add a small macro in the workbook. In fact you can also include the bulk insert command

in that macro.

/Gosta

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Gosta Munktell (12/8/2009)
"I need to do it with T-SQL because my customers first upload a XLS, then press a button that loads the data in a SQL Table."
If it is possible to save the xls file as a tab separated text file you can use bulk insert and T-SQL.
You can add a small macro in the workbook. In fact you can also include the bulk insert command
in that macro.

Unfortunately what he wants and what is possible may not be compatible. The method that you are proposing requires the customer to do something different or requires them to transform the file, not sure how palatable that will be either. I know this could be done the way I suggested, but even I am not much of a fan of that method..

CEWII