Import Excel Into Database In Python (Simple Example)

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 Import Excel Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create your own project folder, e.g. D:\xlsdb, unzip the code inside this folder.
  • Open the terminal (or command line), navigate to your project folder cd D:\xlsdb.
  • As usual, create a virtual environment if you don’t want to 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 s2_create.py.
  • Launch python s4_server.py for the import example.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

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.

 

PART 1) USERS’ TABLE

s1_users.sql
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.

 

PART 2) CREATE THE DATABASE

s2_create.py
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error

# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "s1_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 s1_users.sql to create the users table.

 

 

PART 3) DUMMY EXCEL

s3_users.xlsx
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 4) IMPORT EXCEL INTO THE DATABASE

s4_import.py
# (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("s3_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.

 

 

USEFUL 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!

Leave a Comment

Your email address will not be published.