Welcome to a tutorial on how to import an Excel file into the database in Python. So you have an Excel file that needs to “go into” a database? Well, it’s a simple process of reading the Excel file and importing it into the database – Read on for the example!
ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
TABLE OF CONTENTS
DOWNLOAD & NOTES
Firstly, here is the download link to the example code as promised.
QUICK NOTES
- Create a project folder, e.g.
D:\xlsdb
, unzip the code inside this folder. - Navigate to the project folder in the command line
cd D:\xlsdb
, create a virtual environment to not mess up your other projects.virtualenv venv
- Windows –
venv\scripts\activate
- Mac/Linux –
venv/bin/activate
- Get all the required packages –
pip install openpyxl
. - Create the dummy database by running
python S1B_create.py
. - Launch
python S3_server.py
for the import example.
EXAMPLE CODE DOWNLOAD
Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
PYTHON IMPORT EXCEL INTO DATABASE
All right, let us now get into the example of importing an Excel file in Python.
STEP 1) DUMMY DATABASE
1A) USERS’ TABLE
CREATE TABLE users (
uid INTEGER,
name TEXT NOT NULL,
email TEXT NOT NULL,
tel TEXT NOT NULL,
PRIMARY KEY("uid" AUTOINCREMENT)
);
For this example, we will be working with a simple users’ table.
uid
User ID. Primary key and auto-increment.name
The user’s full name.email
The user’s email address.tel
User’s telephone number.
1B) CREATE THE DATABASE
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "S1A_users.sql"
# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
os.remove(DBFILE)
# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")
To keep things simple without having to install anything else, we will be using SQLite – This script will create users.db
and import S1A_users.sql
to create the users
table.
PART 2) DUMMY EXCEL
Jo Doe | jo@doe.com | 465785 |
Joa Doe | joa@doe.com | 123456 |
Job Doe | job@doe.com | 234567 |
Joe Doe | joe@doe.com | 345678 |
Jog Doe | jog@doe.com | 578456 |
Joh Doe | joh@doe.com | 378945 |
Joi Doe | joi@doe.com | 456789 |
Jon Doe | jon@doe.com | 987654 |
Jor Doe | jor@doe.com | 754642 |
Joy Doe | joy@doe.com | 124578 |
Next, we have a whole bunch of users in an Excel to import into the users
table.
PART 3) IMPORT EXCEL INTO THE DATABASE
# (A) INIT
# (A1) LOAD MODULES
import sqlite3, os
from sqlite3 import Error
from openpyxl import load_workbook
# (A2) SETTINGS
DBFILE = "users.db"
# (B) OPEN DATABASE & EXCEL FILE
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
book = load_workbook("S2_users.xlsx")
sheet = book.active
# (C) IMPORT ROWS & COLUMNS
for row in range(1, sheet.max_row + 1):
sql = "INSERT INTO `users` (`name`, `email`, `tel`) VALUES "
sql += f"('{sheet.cell(row, 1).value}', '{sheet.cell(row, 2).value}', '{sheet.cell(row, 3).value}')"
cursor.execute(sql)
print(sql)
conn.commit()
conn.close()
Lastly, I don’t think this needs a line-by-line explanation… This is doing the exact “read Excel file and import them into database”. But just one thing though – Python cannot read Excel files natively, so we are using openpyxl
here. Read their documentation if you want to learn more, links below.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
HOW ABOUT MYSQL, MSSQL, MONGODB, ETC…
There are a ton of databases in the world, this tutorial will never end if I went through each and every one. So this is your homework – Go through the basics of the database on your own, install your own preferred database management tool, change the SQL for the users
table, and update s4_import.py
to connect to the database of your choice instead.
IMPORT EFFICIENCY
A quick note that while the above example works, importing line-by-line is also very inefficient. So I will leave this as “another homework exercise” – Change the insertion to do by batches of 5 instead. That is, INSERT INTO `users` (`name`, `email`, `tel`) VALUES (ROW), (ROW), (ROW), (ROW), (ROW)
Shouldn’t be too difficult. Just introduce a count
flag to “hold off” cursor.execute(sql)
until there are 5 entries.
LINKS & REFERENCES
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!