Blue Flower

Databases are not just for storing data. If you know SQL, you begin to miss its features while working with Excel. Sure you can sort your data in Excel, but if you need to sort by an expression on several fields you have to add a calculated column. Combining data from several sheets is even more problematic. Sure you can run SQL queries on Excel file, but it is rather complicated.


But if you have scientific Python installed you can convert Excel file in SQLite database in just 8 lines of code and use free GUI tools to run SQL queries on it. Each sheet of Excel file is converted in a table, and sheet name must be a legal SQLite table name (i.e. no whitespace, but you can use underscore). The first row in each sheet must provide legal column names. Here is conversion code:

import sqlite3
import pandas as pd
#Name of Excel xlsx file. SQLite database will have the same name and extension .db
for sheet in wb:
    wb[sheet].to_sql(sheet,con, index=False)

The example uses Pandas library, which in turn uses several dependency packages, so it is not going to work with plain vanilla Python. You need a Scientific Python distributions to run it. Is it worth downloading and installing few hundred megabytes Scientific Python just to convert Excel file into an SQL database? Probably not. But you can do so much more with it! You can take a look at volume 3 of my Data Science in Python series to see what you can do combining Python and SQL database.

Leave comment
Please register to add comment