Import data from Excel to SQL Server with SSMS

In this SQL Server tutorial, here is how to Import data from an Excel file to a database, with the SQL Server wizard, without SQL 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 an Excel file with the SQL Server wizard without writing code?

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.

Fichier Excel à importer avec SQL Server depuis l'onglet Clients
Fichier Excel à importer avec SQL Server depuis l’onglet Clients

Run the “SQL Server Import and Export Wizard” task

1- First, in SQL Server Management Studio, you must right-click on the database where the table that will host the content to be imported is located, then click on Task 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- 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, 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- Now indicate the location of the file on your computer. To do this, click on Browse and select the file from which you want to import the data into the database.

4- 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.

5- 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 or by entering a SQL query to execute in Excel. In our case, we will choose the tabs to select.

Selecting the Excel tabs to load with the column mapping

6- Next, check the tabs to be imported and choose which table to integrate them into. In this case it will be the Customers table.

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

8- 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- 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- 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.

Exécution avec succès de l'import des données Excel vers SQL Server
Exécution avec succès de l’import des données Excel vers SQL Server

11- Finally, the data contained in the Excel file “Client” and in the Clients tab have been imported into the MSSQL table.

Voici le résultat les données sont bien importées depuis le fichier Excel vers la table SQL Server
Voici le résultat les données sont bien importées depuis le fichier Excel vers la table SQL Server

Visit our twitter page : https://twitter.com/ExpertOnlyCom

Be the first to comment

Leave a Reply