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.
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.
Now, Go to the Start menu, search for ‘SQL Server‘ and then open SQL Server 2017 Import and Export Data.
Click on the Next > button.
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 🙂
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.
Now, ‘Choose a Destination‘ dialogue box will open. Choose SQL Server Native Client 11.0 from the Destination drop-down list.
Now, Choose your database (DBEmployee) from the Database drop-down list and then click on the Next > button.
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.
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.
Now, ‘Save and Run Package‘ dialogue box will open. Select the option Run immediately and then click on the Next > button.
Now, ‘Complete the Wizard‘ dialogue box will open. Click on the Finish button.
The execution was successful. Thus, we have learned to import data from excel file to SQL Server table.
Click on the Close button.
Now we need to ensure, that our data is imported correctly into the SQL table from excel?
To check, run a Select query.
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