Willem Hoek

How-to Import an Excel file into SQLite and back to Excel again

Sep 17, 2024

YouTube demo:

Introduction

What we want to do

Load data from an Excel file into a SQLite database and then also the reverse – export data from SQLite to Excel file.

Why?

Excel is awesome, but sometimes it is faster to use SQL for data analysis. However, you need the ability to move data quickly between Excel and SQLite. Hence this post.

Skillset required

Beginner level Python and SQL.

Tools used

Python: I use/recommend the Anaconda distribution.
Python pandas library: It is included in the Anaconda distribution, else you can install it with pip install pandas.
SQLite: To view and interact with the SQLite file, I use the excellent open source program DB Browser for SQLite.

Code

Load Excel file in SQLite

import pandas as pd
import sqlite3

# if using Windows
db = r'C:\data\sap\sqlite.db'                   # SQLite database file
file_in = r'C:\data\sap\mara.xlsx'              # Excel data file

# if using Linux or macOS
db = r'/home/willem/data/sap/sqlite.db'         # SQLite database file
file_in = r'/home/willem/data/sap/mara.xlsx'    # Excel data file

# read Excel file into a dataframe
df = pd.read_excel(file_in, na_filter=False)

# create database table from dataframe
table = "mara"
conn = sqlite3.connect(db)
df.to_sql(table, conn, if_exists='replace', index=False)
conn.close()

Export SQLite data to Excel file

import pandas as pd
import sqlite3

# if using Windows
db = r'C:\data\sap\sqlite.db'                       # SQLite database file
file_in = r'C:\data\sap\mara_out.xlsx'              # Excel data file

# if using Linux or macOS
db = r'/home/willem/data/sap/sqlite.db'             # SQLite database file
file_out = r'/home/willem/data/sap/mara_out.xlsx'   # Excel data file

sql = "select *, length(maktx) as maktx_len from mara"

# get data from SQLite in Pandas dataframe
conn = sqlite3.connect(db)
df = pd.read_sql_query(sql, conn)
conn.close()

# export dataframe to Excel spreadsheet
df.to_excel(file_out, index=False)

Export SQLite data to Excel file - CUSTOMIZE the Excel file

If you require more complex Excel files to be created, you may want to look at XlsxWriter. XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.

You may need to install the XlsxWriter library first with pip install xlsxwriter.

import pandas as pd
import sqlite3
import xlsxwriter

db = r'/home/willem/data/sap/sqlite.db'
file_out = r'/home/willem/data/sap/mara-snazzy.xlsx'
sql = "select matnr, maktx from mara "

# Get data from SQLite in Pandas dataframe
con = sqlite3.connect(db)
df = pd.read_sql_query(sql, con)
con.close()

# Create Excel file
row = 1
col = 0
workbook = xlsxwriter.Workbook(file_out)
header = workbook.add_format({'bold': True, 'bg_color': '#D8E4BC'})
worksheet = workbook.add_worksheet('Summary')
worksheet.write(row, col, 'Material', header)
worksheet.write(row, col + 1, 'Description', header)
row += 1
for index, dat in df.iterrows():
    worksheet.write(row + index, col, dat['matnr'])
    worksheet.write(row + index, col + 1, dat['maktx'])
worksheet.set_column('B:B', 50)
worksheet.set_zoom(200)
workbook.close()