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:

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

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

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.

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:

• 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.
• Discover other techniques to import and export data to and from SQL Server using different file formats and tools.

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

How to zip and unzip files with the Python zipfile module ?