Python use case – Import data from excel to sql server table – SQL Server 2017


If we need to import data from an excel file into SQL Server, we can use these methods:

  1. SQL Server Import Export Wizard
  2. Create an SSIS package to read excel file and load data into a SQL Server table
  3. Use T-SQL OPENROWSET query
  4. Use the read_excel method of Python’s pandas library (Only available in SQL Server 2017 onwards)

In this post “Python use case – Import data from excel to sql server table – SQL Server 2017”, we are going to learn that how we can use the power of Python in SQL Server 2017 to read a given excel file in a SQL table directly. With the integration of Python in SQL Server 2017, we can use the pandas read_excel method to read a given excel file with lots of customizations in SQL Server.

Assume that we have an excel file named EmployeeList.xlsx as this. Click here to download the sample excel file.

Sample excel file data

Sample excel file data

Let’s create the dbo.EmployeeList table to store the excel file data using this script:

IF OBJECT_ID('dbo.EmployeeList') IS NOT NULL
	DROP TABLE dbo.EmployeeList
GO
CREATE TABLE dbo.EmployeeList
(
	ID INT,
	[Name] VARCHAR(512),
	DOB DATE,
	City VARCHAR(512),
	DateOfJoining DATE
)
GO

Now, use this script to read and load the date into the dbo.EmployeeList table from the excel file using pandas read_excel method.

INSERT INTO dbo.EmployeeList
EXEC sp_execute_external_script
@language = N'Python',
@script =
N'
#Line 1
import pandas as pd

#Line 2
OutputDataSet = pd.read_excel("D:\\EmployeeList.xlsx", sheet_name = "Sheet1", header = 0, names = ["ID", "Name", "DOB", "City", "DateOfJoining"])
'
GO

How it is working

Let’s discuss the used Python code line by line.
#Line 1

import pandas as pd

In this line, we are just importing the pandas module under alias pd.

#Line 2

OutputDataSet = pd.read_excel("D:\\EmployeeList.xlsx", sheet_name = "Sheet1", header = 0, names = ["ID", "Name", "DOB", "City", "DateOfJoining"])

In this line, we are using the read_excel method of pandas library to read the excel file data from Sheet1. Also, we are using the Insert statement to directly insert the data into dbo.EmployeeList table from the stored procedure “sp_execute_external_script“.

Let’s have a look at the table data now.

SELECT * FROM dbo.EmployeeList
GO
Table Data

Table Data

Thanks for the reading. Please share your inputs in the comments.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.