SQL

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

Yasin Panwala

Yasin Panwala is a Web Developer and Author at TheCodeHubs. He has experience in Web Developing and Designing and also in Writing. He has got his skills in working on technologies like .NET Core, ADO.NET, AJAX, Angular, AngularJS, ASP.NET, ASP.NET MVC, Bootstrap, C#, CSS, Entity Framework, Express.js, GraphQL, HTML, JavaScript, JQuery, JSON, LINQ, Microsoft Office, MongoDB, MySQL, Node.js, PostgreSQL, SQL, SQL Server, TypeORM, TypeScript, Visual Basic .NET, Web API. He also got his skills in working with different integration and some known versioning tools. He is always ready to learn new things and he always tries his best on tasks that are assigned to him and gives the best possible outputs.

Recent Posts

Testing hk

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Operation

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

TETS NEW

test

3 years ago