Excel Linked Server

With my new found knowledge of importing Excel data into SQL tables, I came across the scenario of importing a table with no headers. In my case there are column headers, but a spacer row was added to separate it from the actual data. In addition, there are additional tables below also separated by a spacer row to differentiate categories. It is a matter of formatting the tables for look where the simple tables just don't appeal to the eye.

For your sake and not simplicity, I will detail how to create a linked server here.

To link to an Excel workbook and import with no headers, I created a linked server by the following these steps:

1. Open Microsoft SQL Server Management Studio Express
2. Connect to your server which has the database in which you wish to create a table.
3. In the Object Explorer tab, expand Server Objects.
4. Right-click Linked Servers.
5. Select New Linked Server.
6. Under the General page, type in a name for the Linked Server.
7. Select Other data source for Server Type.
8. Select Microsoft Jet 4.0 OLE DB Provider.
9. For Product Name, type in Excel.
10. Type in the full path and filename of the Excel workbook to which you are linking in Data Source.
11. In the Provider String, type Excel 8.0; HDR=NO.
12. Select OK.

You should now have a linked server to your Excel workbook and it will not import the first row as headers.

In my case, my tables were separated by spacer rows. I opened the worksheet and highlighted each table. I defined names for each and used these names to import each table rather than the worksheet name.

Example:

SELECT * INTO mySQLTable FROM myExcelLinkedServer...myDefineNamedTable

Notice there are no brackets or the $ symbol. Brackets and the $ are used when importing a worksheet, not a named range.

If I had only one table and it started in cell A1, I could use the following:

SELECT * INTO mySQLTable FROM myExcelLinkedServer...[myExcelWorksheet$]