I often find myself having to upload data in Excel files into temp tables in SQL Server in order to do ad hoc updates or comparisons. Users will commonly submit an Excel file containing data to verify, insert, update or what not. I knew there was a way to have SQL Server read data directly form Excel, so finally I got round to trying that out to make these ad hoc tasks easier. Here is how you do it:

First, make sure the data in the XL sheet looks nice and tidy, i.e. that each column has a header in the first row that has a meaningful name for your query. Try to avoid strange characters or lengthy names. For instance, SKU and CasesPerPallet. Clear out breaks in the data or other things that interrupt the flow of numbers, like subtotals, headings etc. The data should all be in a contigous block under the row header. Save the file in Excel 97-2003 workbook format.

Now, copy the file to the file system of the SQL Server (not your own local computer), for instance to D:\Data\CSPallet.xls.

Next, open a query window and try selecting from the file like this:

select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=d:\data\cspallet.xls;Extended Properties=Excel 8.0')...Sheet1$

Note the path and filename in the connection string.

You may get an error when running this if you have not turned on access to distributed ad hoc queries on your SQL server. It is off by default. You can do that like this:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

If you want to join your data to data in the SQL server you can do something like this:

select mi.SKU, mi.CasesPerPallet, cp.CasesPerPallet as SandrasCasesPerPallet

from ii_masteritem mi

join OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=d:\data\cspallet.xls;Extended Properties=Excel 8.0')...Sheet1$ cp on cp.SKU = mi.SKU

where mi.CasePerPallet <> cp.CasesPerPallet

In this case, I am selecting all items from my II_MasterItem table that have a Case Per Pallet count that differs from what was in the Excel sheet.

Hope this helps you run ad hoc joins to XL data more easily!