Saturday, May 23, 2009

[SQLServer2005] Select from Excel Spreadsheet for SQL-Rejects

This is DBA bread and butter - not being a DBA and having no will whatsoever to become one (I am too weak) I always forget the syntax for this procedure, so here we go.

In order to be able to query an excel spreadsheet we need to enable ad hoc distributed queries, to do so follow the procedure in this other post.

Once that's sorted - assuming you have an excel spreadsheet with column names (in the example I have Column1, Column2, Column3, Column4) in the first row - this is how you go about selecting the spreadsheet content into a temporary table:

USE myDB

-- drop temp table
DROP TABLE #tempTable

-- select spreadsheet content into #tempTable
SELECT S.[Column1], S.[Column2], S.[Column3], S.[Column4]
INTO #tempTable
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\mtSpreadsheet.xls;HDR=YES',
'select * from [Query1$]') AS S;

-- check if the stuff is there
select *
from #tempTable


That's it. Hope it helps some other fellow SQL-Reject.

3 comments:

Liduário said...

Unfortunately it did not work.
Error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Johnny Idol said...

You might wanna have a look at this link which provides some troubleshooting guidelines -->
http://blogs.msdn.com/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

Liduário said...

It´s work but only excel 2003 or previous version.
Thanks.