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.
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()
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.
If you want to write the sheet to a single table.
import sqlite3
import pandas as pd
cxn = sqlite3.connect('mydb.db')
wb = pd.read_excel('myxl.xlsx',sheet_name = 'mysheet')
wb.to_sql(name='mytable',con=cxn,if_exists='replace',index=True)
cxn.commit()
cxn.close()
The easiest way to achieve this would probably be to export your Excel files to CSV files, and use the CSV module to read this data in your Python program.
You can then use the sqlite module to write this data in your database.
