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.

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

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