How to import an Excel file into SQL Server using Python?

In this tutorial, we will learn how to import an Excel file into a SQL Server table using Python. Let’s delve into the process of manipulating data with Python scripts. Our journey will encompass three main stages: crafting a sample Excel file containing data, constructing a table in SQL Server to accommodate the data, and seamlessly importing 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 conclusion of this tutorial, you will possess a solid grasp of importing data from an Excel file into a SQL Server database, harnessing the versatility and efficiency of Python.

1. Prepare a sample Excel file with some 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 in the Excel file:

IDNameDepartmentSalary
1John SmithSales50000
2Jane DoeMarketing60000
3Bob JohnsonIT70000
4Alice WongHR55000

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
Excel file to import into a SQL Server table using a Python script

2. Create a SQL Server table to store the 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
CREATE TABLE dbo.employees (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   department VARCHAR(50),
   salary INT
);

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.

3. Import the data from the Excel file 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 example code demonstrates how to read data from the employees.xlsx file and insert it into the employees table:

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()

In the code above, we perform a few actions:

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

After executing the script, you should see 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
Select data imported from the Excel file into the SQL Server table with SSMS

Conclusion on 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 steps outlined in this 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 three following additional resources:

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.

From our Python tutorials on importing data to SQL Server database

  1. Exporting a SQL Server table to an Excel file with Python
  2. How to import data from CSV to SQL Server in Python ?
  3. Tutorial to import XML documents into SQL Server in Python

Leave a Comment

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

Scroll to Top