How to import an Excel file into SQL Server?

Question

I have data in an Excel file - actually xlsx format since it is now 2020. My requirement is to get this data into SQL Server as follows:

1. ad hoc, the use-case being feeding tables with test data, or infrequent data loads of small amounts of data (say < 3k rows), and

2. In a repeatable, robust, and possibly automated way for a production system.

If possible, those posting answers should give a thought to the security and config overheads of their proposals.

I implore readers to allow this apparently-vague question to remain open as I hope to collect suggestions (hopefully with links to examples) for all of the options and thus create a collated list.

Answer 1

There are many articles about writing code to import an excel file, but this is a manual/shortcut version:

If you don't need to import your Excel file programmatically using code you can do it very quickly using the menu in SQL Management Studio.

The quickest way to get your Excel file into SQL is by using the import wizard:

1. Open SSMS (Sql Server Management Studio) and connect to the database where you want to import your file into.
2. Import Data: in SSMS in Object Explorer under 'Databases' right-click the destination database, select Tasks, Import Data. An import wizard will pop up (you can usually just click 'Next' on the first screen).

3. The next window is 'Choose a Data Source', select Excel:

   • In the 'Data Source' dropdown list select Microsoft Excel (this option should appear automatically if you have excel installed).

   • Click the 'Browse' button to select the path to the Excel file you want to import.

   • Select the version of the excel file (97-2003 is usually fine for files with a .XLS extension, or use 2007 for newer files with a .XLSX extension)
   • Tick the 'First Row has headers' checkbox if your excel file contains headers.
   • Click next.

4. On the 'Choose a Destination' screen, select destination database:
   • Select the 'Server name', Authentication (typically your sql username & password) and select a Database as destination. Click Next.

5. On the 'Specify Table Copy or Query' window:

   • For simplicity just select 'Copy data from one or more tables or views', click Next.

6. 'Select Source Tables:' choose the worksheet(s) from your Excel file and specify a destination table for each worksheet. If you don't have a table yet the wizard will very kindly create a new table that matches all the columns from your spreadsheet. Click Next.

7. Click Finish.