How To Import An Excel File Into SQL Server Using Python?

A step-by-step tutorial on importing data from Excel documents into SQL Server databases using few lines of powerful Python scripts.

In this tutorial, we will learn how to import data from an Excel file into a SQL Server table using Python code. We’ll use three main stages: create an Excel file containing sample data, create a table in SQL Server that fits our data structure, and import the Excel data into the SQL Server table. To achieve this, we will employ the power of Python’s openpyxl and pyodbc libraries. By the end of this programming tutorial, you will master importing data from Excel documents into a SQL Server database, using the versatility and efficiency of Python.

Table of Contents

1. Prepare a sample Excel file with sample data

To demonstrate how to import data from an Excel file to a SQL Server table using Python, we first need a sample Excel file with data. In this example, we will use an Excel file named employees.xlsx containing employee data with the following headers.

ID
Name
Department
Salary

Here’s a sample of the data for the Excel file, technically you simply need to copy and paste it into an Excel table, in a sheet of a new Excel document.

ID	Name	Department	Salary
1	John Smith	Sales	50000
2	Jane Doe	Marketing	60000
3	Bob Johnson	IT	70000
4	Alice Wong	HR	55000

Sample data for Excel to import into SQL Server

Save the Excel file to a directory, for example, in my case, I use this Windows path: C:\data\employees.xlsx

***Excel file to import into a SQL Server table using a Python script***

2. Create a SQL Server table to store the Excel data

Before importing the data from the Excel file, we need to create a table in SQL Server to store the data. We will create a simple table named employees with four columns:

id
name
department
salary

Use SQL Server Management Studio (SSMS) or execute the SQL query using the pyodbc library in Python to create the table in your SQL Server database.

CREATE TABLE dbo.employees (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   department VARCHAR(50),
   salary INT
);

3. Python script to import data from Excel into SQL Server

Now, we will use the openpyxl library to read data from the Excel file and the pyodbc library to insert the data into the SQL Server table. The following Python code example demonstrates how easy it is to read data from the employees.xlsx file and insert it into the employees table. Of course you need to adjust the code in case of specific constraints.

# Expert-Only.com
import openpyxl
import pyodbc

# Load the Excel workbook and select the active worksheet
workbook = openpyxl.load_workbook("C:\\data\\employees.xlsx")
worksheet = workbook.active

# Connect to the SQL Server database
connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=localhost;'
                            'Database=Expert-Only;'
                            'Trusted_Connection=yes;')

cursor = connection.cursor()

# Read data from the Excel file and insert it into the SQL Server table
for row in range(2, worksheet.max_row + 1):
    id = worksheet.cell(row=row, column=1).value
    name = worksheet.cell(row=row, column=2).value
    department = worksheet.cell(row=row, column=3).value
    salary = worksheet.cell(row=row, column=4).value

    cursor.execute("INSERT INTO dbo.employees (id, name, department, salary) VALUES (?, ?, ?, ?)",
                   id, name, department, salary)

# Commit the changes and close the connection
connection.commit()
connection.close()

To import the Excel document into the SQL Server table, in the code above, we perform 5 main operations:

We first load the Excel workbook and then select the active worksheet.
Then, we connect to the SQL Server database using the pyodbc library.
We loop through the rows in the Excel worksheet, starting from row 2 to ignore the header row.
And we read the data from each cell in the row.
Finally, we insert the data into the employees table using an INSERT INTO SQL Server query and commit the changes to the database.

After executing the script, check the data from the Excel file successfully imported into the SQL Server table.

*Select data imported from the Excel file into the SQL Server table with SSMS*

About importing Excel data into SQL Server with Python

You have successfully learned how to import data from an Excel file into a SQL Server table using a Python script. By following the different steps outlined in this coding tutorial, you can now read data from an Excel file and insert it into a SQL Server table with proper formatting using Python. This skill can be useful for a wide range of data analysis, reporting tasks, and data migration projects. Continue to explore and practice with these libraries and tools to enhance your Python data manipulation skills.

To go further, consider the following additional resources. Explore the Pandas library for more sophisticated data manipulation and analysis tasks in Python. Learn how to manage Excel files with advanced formatting using the openpyxl library. Indeed, by combining the knowledge from these resources, you can create more advanced data processing workflows, automate data import and export tasks, and develop custom solutions for your data management needs using Python and SQL Server.