How can I 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.

Solution 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 Server Management Studio (SSMS).

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, and 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.

Solution 2

You can also use OPENROWSET to import an Excel file in SQL Server.

SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                        'Excel 12.0;Database=C:\temp\MySpreadsheet.xlsx',
                        'SELECT * FROM [Data$]')

Comments

[MarshallMa]

Hi thanks for this interesting answer lol. But it's 2018 now and I have problem using the latest import wizard. I tried importing xls and csv file but the column names (written in UTF-8 English) were messed and I couldn't get it right. Do you mind writing a newer answer?

[Spyder]

Hi @MarshallMa, I am using the latest 2018 version of Sql Server Management Studio (v17.8.1), so I repeated the above steps, everything stays exactly the same and the above steps still work. If you are struggling with column names, check step 3 above (there is a checkbox to indicate "First row has column names")

[Avinash]

got this Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

[Prafulla]

@Avinash, apologies for delayed reply. For the mentioned message you need to download and install ACE provide for Microsoft.ACE.OLEDB.12.0. If you are importing .xls file then you can use the query. SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

[Vanquished Wombat]

The SQL Import wizard is frustrating and you can't save the config unless you have SIS. The OPENROWSET approach looked useful but is a dependency & security nightmare. I have found good mileage on an AWS desktop by creating an ODBC link to the SQL Server then using MS Access with a linked table. My use-case is getting test data into SQL so all I need to do is set up my test data in Excel then cut & paste into the Access table and voila > data in SQL Server table. Entirely repeatable, plus you can edit / delete the data from Access too.

[Vanquished Wombat]

More on the Access import route. The main frustration is lining up your data format in the Excel data with the SQL format realised via Access linked table. Access complains about the data being too long for the target columns with no specific pointers to the actual erroneous column. Besides confirming column sequence, and string lengths in Excel, check your dates which can appear in Excel as say DD/MM/YY but are a full date format behind the scenes.

[DaFi4]

if you get an error about ACE you need the driver microsoft.com/en-us/download/details.aspx?id=54920

[Vikas Lalwani]

Thanks, i tried using using above query, there were some issues, like running ad-hoc query and then oledb error for which I had to use SSMS in Administrator mode, I have mentioned details in our article qawithexperts.com/article/sql/…