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 is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

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.