How to import excel data into sqlite database in python?

Question

I have data in multiple excel files, I have to import excel specific sheet data into an SQLite database using python.

I prepared these excel files every week so once the table is created into SQLite database and one week file is imported then every week I have to append excel file.

Answer 1

I have simply written below code into the juypitor notebook and it imports my excel data into sqlite3 database. working fine.

import sqlite3
import pandas as pd

con = sqlite3.connect('cps.db')
wb = pd.read_excel('CPS\CPS.xlsx',sheet_name = None)

for sheet in wb:
    wb[sheet].to_sql(sheet,con,index=False)
con.commit()
con.close()

Answer 2

You can try openpyxl to read Excel(.xlsx) files. Then you can use Sqlite3 module to write the data into the sqlite3 database.

openpyxl is a third party module, so you have to use pip to install it.

pip install openpyxl

sqlite3 module is built in the default python modules, so you don't need to pip for it.

There are tons of examples on how to use openxl on its web page. You'll find the way how to read the data in your Excel files.