I had to import information from an Excel file with datasheets that had 40+ columns. Using SSIS could be a bit tricky sometimes, so I decided to use a linked server. This feature works well. It's fast and less of a headache than SSIS. Originally designed for connecting to other databases, you can use it to import information by linking to a file. This is how I went about it (this is for SQL Server 2005) in importing an Excel (.xls) file.
- Under Server Objects in your instance, create a new Linked Server:
- Under the General section, pick an appropriate name for your linked server. Pick the OLE DB provider for Excel documents:
- Since I'm using this on a local machine, I don't have to worry about security too much. Select "Be made without using a security context" under the Security section.
- Select your Server Options. I suggest these settings for local access.
- Hit OK to create it. You'll see the following objects:
SQL Server reads a spreadsheet in a workbook as a table. So now that we've created our linked server, let's see how to query them.
-- Querying three spreadsheets. SELECT * FROM Hardware...['CORE PROBONO$'] SELECT * FROM Hardware...['ET013-PartialRackElevation$'] SELECT * FROM Hardware...[ILO_TEMPLATE$]
Since I don't always want to rely on the linked server, create tables into my general database where I slice and dice data.
-- Import data from a linked server into a database table SELECT * INTO General.dbo.Elevation FROM Hardware...['ET013-PartialRackElevation$']