How To Import Excel In SQL Table

In this article, we will learn how to import excel file in the SQL table.

Below is a sample of 5 records from the excel file (The first row has column names):

Data in Excel

Now, let’s follow the below steps to import data from excel file to SQL Server table.

Step 1:

Firstly, Open Microsoft SQL Server Management Studio and decide in which existing database (DBEmployee) you want to import excel data or you also can create a new database.

Step 2:

Now, Go to the Start menu, search for ‘SQL Server‘ and then open SQL Server 2017 Import and Export Data.

Step 3:

Click on the Next > button.

Step 4:

Now, ‘Choose a Data Source‘ dialogue box will open. Choose Microsoft Excel from the Data source drop-down list.

If missing Microsoft Excel as option in the Data source drop-down list?

Had to install this driver: https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

After that it works 🙂

Step 5:

Now, Click on the Browse… button, Choose your Excel (.xlsx) file from the browse dialogue box. Also, make sure the Excel version is correct and then click on the Next > button.

Step 6:

Now, ‘Choose a Destination‘ dialogue box will open. Choose SQL Server Native Client 11.0 from the Destination drop-down list.

Step 7:

Now, Choose your database (DBEmployee) from the Database drop-down list and then click on the Next > button.

Step 8:

Now, ‘Specify Table Copy or Query‘ dialogue box will open. Select Copy data from one or more tables or views and then click on the Next > button.

Step 9:

Now, ‘Select Source Tables and Views‘ dialogue box will open. Choose the sheet in which your data is present and then click on the Next > button.

Step 10:

Now, ‘Save and Run Package‘ dialogue box will open. Select the option Run immediately and then click on the Next > button.

Step 11:

Now, ‘Complete the Wizard‘ dialogue box will open. Click on the Finish button.

Step 12:

The execution was successful. Thus, we have learned to import data from excel file to SQL Server table.

Click on the Close button.

Step 13:

Now we need to ensure, that our data is imported correctly into the SQL table from excel?

To check, run a Select query.

Step 14:

After importing we can rename the table name, using SQL Management Studio. By default, the Sheet name of the Excel will be taken by SQL as the table name (Sheet1$).

In the SQL Management Studio, point to the table and then right-click on the table name (Sheet1$). Then click on the Rename option and give a new name to the table.

Also, check How To Export Excel In SQL Table