Import from Excel to SQL Server using Python

Problem

There are many ways to load data from Excel to SQL Server, but sometimes it is useful to use the tools you know best. In this article, we look at how to load Excel data into SQL Server using Python.

Solution

Follow this guide to see how to use Python to load data from Excel into a SQL Server database.

Tools Used

• SQL Server Instance. You can create one following this guide.
• Python, Version 3.11.0.
• Visual Studio Code, Version 1.72.1.
• Windows 10 PC or Windows Server 2019/2022.

Database Setup – Create Test Database and Table

There are several ways to create databases and tables in SQL Server, but below we will walk through how you can use SQLCMD to create the database if you don’t have SQL Server Management Studio or Azure Data Studio.

Open a new Windows Command Prompt or start a new terminal session from Visual Studio Code by pressing CTRL + SHFT + `.

To start SQLCMD, use the following command sqlcmd -S <yourServerName> -E to log into SQL Server. The -S parameter is the SQL Server instance, and the -E parameter says to use a trusted connection.

sqlcmd -S <yourServerName> -E

Once logged in, create a new database with the following command:

CREATE DATABASE ExcelData;
GO

Use this SQLCMD command to confirm the creation of the database:

SELECT name FROM sys.databases
GO

The image below is the output that shows the databases available in the SQL Server instance.

To use the new database, use this command as follows:

USE ExcelData;
GO

The command prompt will notify you of the change as shown below.

Now you can create a table in the database:

CREATE TABLE EPL_LOG(ID int NOT NULL PRIMARY KEY);
GO

Great! You have created a table named EPL_LOG with ID as the primary key. We only need the first column and the load program will create the remaining columns based on the source file.

Engine Configuration

The engine marks the starting point of your SQLAlchemy application. The engine describes the connection pool and dialect for the BDAPI (Python Database API Specification), a specification within Python to define common usage patterns for all database connection packages, which in turn communicates with the database specified.

Click CTRL + SHFT + ` in Visual Studio Code to open a new terminal.

Use the npm command below in the terminal window to install the SQLAlchemy module.

npm install sqlalchemy

Create a Python file called DbConn.py and add the code below and update the data source values to match your needs. This is the SQLAlchemy engine that will communicate with SQL Server on behalf of Python.

// DbConn.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
 
conn = urllib.parse.quote_plus(
    'Data Source Name=MssqlDataSource;'
    'Driver={SQL Server};'
    'Server=POWERSERVER\POWERSERVER;'
    'Database=ExcelData;'
    'Trusted_connection=yes;'
)
 
try:
    coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
    print("Passed")
 
except:
    print("failed!")

Writing to SQL Server

We will use Pandas which is a fast, powerful, flexible, and easy-to-use open-source data analysis and manipulation tool built on the Python programming language. Pandas can read Excel data into the Python program using the pandas.read_excel() function.

To make things easy for this demo, store the Excel file in the Visual Studio Code project folder, so we don’t need to specify the path. This enables you to ignore the io (any valid string path) parameter of the read_excel() function.

We will also use openpyxl as the engine for reading the Excel files. Run the pip command below in the terminal window to install openpyxl.

pip install pandas openpyxl

Create another Python file named ExcelToSQL.py and add the code below. This will be the code that reads the Excel file and write to the database table we created.

//ExcelToSQL.py
from pandas.core.frame import DataFrame
import pandas as pd
from DbConn import coxn
 
df = pd.read_excel('sportsref_download.xlsx', engine = 'openpyxl')
 
try:
   df.to_sql('EPL_LOG',con=coxn,if_exists='replace')
except:
    pass
    print("Failed!")
else:
    print("saved in the table")
print(df)

Next, click on the Play button in Visual Studio Code at the top right corner to run the script. The output of the data will appear on your terminal.

To check if the data has been stored in your database, open SSMS, then select the data from the table. You could also use SQLCMD to connect to the instance and run the following code.

USE ExcelData;
GO
SELECT * FROM EPL_LOG

The image below shows the data is now in the database.

Conclusion

Python does a great job acting as the middleman between Excel and SQL Server. You can translate any static Excel data into a more flexible dataset by moving it to a database that is more accessible and easier to integrate with other systems.

Move your Excel data to SQL Server with this approach. Since pandas stores the data in a DataFrame, it is easy to manipulate and change the data before sending it to SQL Server.

Next Steps

• Learn how to Load API Data to SQL Server Using Python and Generate Report with Power BI.
• New to Python? Go through Introducing Python User Defined Functions to SQL Server Professionals.
• If you want to learn more about T-SQL, SQL Cheat Sheet for Basic T-SQL Commands.
• Get an Introduction to SQL Server’s sqlcmd utility.