Import data from Excel to SQL Server with SSMS and the Import and Export wizard

SQL Server tutorial on how to Import data from an Excel file into a database, using the SQL Server wizard and without code.

To do this, use the Data Import Wizard from SQL Server Management Studio. In this example, here is how to import data from a source Excel file to a SQL Server target table. To Import an Excel file with SSMS, this is the easiest guided option.

How to import data from Excel with the SQL Server wizard ?

The Excel file for the example is shown in the screenshot below. Here is the Clients tab which contains the data to be loaded into the database. Here is an article on where and how to download SQL Server.

Excel file to import into the SQL Server database
Excel file to import into the SQL Server database

1. Run the SQL Server Import and Export Wizard task

First, in SQL Server Management Studio:

  1. Right-click on the database where the table that will be hosting the data is stored
  2. Then click on Task
  3. And then on Import Data to open the SSMS wizard.
Importer un fichier Excel avec SQL Server depuis l'assistant Import de données SQL Server
Importer un fichier Excel avec SQL Server depuis l’assistant Import de données SQL Server

2. Select the Data Source type

In the SQL Server Import and Export Wizard window:

  • First select the source of your load, you will have the choice between a multitude of media file types.
  • In our case it is an Excel file, so we choose Microsoft Excel.
Sélection du type de données source pour l'import depuis Excel vers SQL Server
Sélection du type de données source pour l’import depuis Excel vers SQL Server

3. Input the Excel file path to import

Now indicate the location of the file on the computer or the server. To do this, click on Browse and select the file from which you want to import the data into the database.

Browse Windows and select the Excel file path to import in SQL Server
Browse Windows and select the Excel file path to import in SQL Server

4. Select the target database

Now select the database to be used for importing data from the Excel file and then click on Next. Here the target database is a 2008 R2 version but it also works with SQL Server 2012, SQL Server 2016 and SQL Server 2019.

Select the target SQL Server target database
Select the target SQL Server target database

5. Select the Excel file import method

Next, choose how to select your data within your Excel file. You will have the choice between:

  • Selecting the Excel tabs to import by ticking them.
  • Entering a SQL query to execute in Excel.

In our case, we will choose the tabs to select.

6. Select the Excel tabs to load

Next, check the tabs to import and choose which table to integrate them into. In this case it will be the table named Clients.

7. Configure the column mappings from Excel to MS SQL

Sometimes there are inconsistencies between the Excel columns format and the database columns in the table. Ignore this format difference in the “on Error” column. To do this, choose Ignore.

Configure the Excel file mapping from Excel to SQL Server
Configure the Excel file mapping from Excel to SQL Server

8. Execute the package created by the Import and Export Wizard

It is possible to save the package in SSIS format for later use. We will thus check the run immediately box to proceed directly to the import of the Excel file into the SQL Server database.

Indeed, to import the same Excel file again with SSMS, simply save the SSIS package generated automatically by the SSMS wizard and run it again. To do this, check the Save SSIS Package box.

9. Check the summary of the Excel file import

A summary of the operations performed is displayed. Read this summary carefully to check the parameters. Then click on the Finish button to launch the import.

Dernière validation pour importer un fichier Excel avec SQL Server
Fin de la configuration pour importer un fichier Excel avec SQL Server

10. Check the import execution results and the data

A screen is displayed with details of all the data transfer steps. The result of the Excel import is then displayed. In this case, all the steps were successful.

Successful execution of the Excel file import into the SQL Server database
Successful execution of the Excel file import into the SQL Server database

Finally, the data contained in the Excel file Client and in the Clients tab have been imported successfully into the MS SQL table.

Voici le résultat les données sont bien importées depuis le fichier Excel vers la table SQL Server
Table with the imported data from Excel

Be the first to comment

Leave a Reply

Your email address will not be published.


*