The key to this is OPENROWSET - which allows SQL to access Excel sheets (amongst other things), either directly, or to load the data into a table for further SQL manipulation at leisure.
Lets say we have an excel sheet "C:\Data\Orders.xls" and want to query the data from a worksheet title "Today".
The version of Excel you have makes a small difference to the code required, but for Excel 97 up to 2003 you can run the following:
SELECT * -- INTO #Orders -- [Use this if you want to store the data into a temp table] FROM OPENROWSET -- [let the magic begin!] ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\Data\Orders.xls', 'SELECT * FROM [Today$]') |
The HDR=YES reference implies the first row of the data in the worksheet is a header row, which will be used to name the data columns.
In Excel 2007 and later, you need a slightly different combination of provider name and data source.
Also note that the path path of the file in this statement is local to the SQL Server you are connected to - and not the machine you are actually sat at.
So if the file is on your local workstation, you may need to create a share to enable the SQL Server to access the data.
For example, an Excel 2007 file "Orders.xlsx" is on a local workstation "MachineX", that is in a shared folder called "Data".
This time you can access the data by using:
SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=\\MachineX\Data\Orders.xlsx', 'SELECT * FROM [Today$]') |
Certainly easier than messing about with data import wizards.
