Willem Hoek

Howto Import Excel file into SQLite and back to Excel again

May 11, 2024

Howto Import Excel file into SQLite and back to Excel again

This post describes the approach and solution to the Jane Street Puzzle of December 2022.

F1: file "Excel" "file" fill LIGHTGREEN;
arrow right "read" "excel" ;
P1: box rad 10px "Pandas" "dataframe";
arrow "to" "sql";
cylinder "SQLite" fill LIGHTBLUE

Caption: text "Import Excel file" bold with .n at 0.5cm below P1.s 

C1: cylinder "SQLite" at 3cm below 1st file fill LIGHTBLUE;
arrow "read" "sql query" ;
P2: box rad 10px  "Pandas" "dataframe";
arrow "to" "excel";
file "Excel" "file"  fill LIGHTGREEN;
Caption: text "Export Excel file" bold with .n at 0.5cm below P2.s 

Steps

Here is a visual showing the backtracking in action and related Python code to generate the image.

Using Python

Since Jane Street is a major user of OCaml, I decided to use OCaml for this puzzle.

The code, listed below, is quite short. Less than half the code solve the actual puzzle. The rest of the code is used to print out the evaluated paths and solution.

Simply copy / paste the source code below into the online OCaml Compiler and execute to see the results.

Load file in SQLite.

import pandas as pd
import sqlite3

db = r'C:\data\db\sapetl.db'
db = r'/home/willem/data/sapetl.db'
file_in = r'C:\data\sap\mara.xlsx'
file_in = r'/home/willem/data/sap/mara.xlsx'
table = "mara"

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

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

print(df)

Export from SQLite to Excel

import pandas as pd
import sqlite3

sql = "select * from mara"
file_out = r'C:\data\sap\mara_export.xlsx'
file_out = r'/home/willem/data/sap/mara_export.xlsx'

# execute SQL with results in 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)

print(df)

References and further reading

[1] SQLite
https://www.sqlite.org/

[2] DB Browser for SQLite
https://sqlitebrowser.org/

[3] Python
https://www.python.org/

[4] Python Pandas
https://pandas.pydata.org/

[5] Sample code

Solving the Jane Street puzzle of December 2022

Why I created Scrumdog - a program to download Jira Issues to a local database

Jane Street puzzle Feb 2021 SOLVED! OCaml to the rescue

Solving the Jane Street puzzle of Dec 2020 - Backtracking with OCaml

Automate your Jira reporting with Python and Excel

Willem Hoek

Howto Import Excel file into SQLite and back to Excel again