How to Convert Excel file into Database tables in Python

Are you trying to create a database from an excel file? Here is a quick python tutorial that will teach you how you can create a SQLite database from an excel file.

Convert Excel file into database table in Python

The easiest way to convert an excel file into a database table in python is to use df.to_sql() function. df.to_sql() in python convert a data frame into SQLite database. But first, you have to convert your excel file to dataframe.

Follow these steps to convert an Excel file into Sqlie database

Step No 1: Convert Excel file into Dataframe

The first step in the process of conversion of an Excel file to SQLite database is to convert excel file into a data frame. The best way to convert an Excel file into a data frame is to use the read_excel() function. The read_excle() function in the python pandas module converts an excel file into a pandas dataframe.

import pandas as pd
import sqlite3
df = pd.read_excel('excel_file.xls')
print(df)

Step No 2: Convert Dataframe to SQL database

Once we have the pandas data frame we can use the df.to_sql() function to convert dataframe to a SQL database.

Below is the code to convert excel file to sqlite database using pandas.

import pandas as pd
import sqlite3

db = sqlite3.connect('sqlite.db')
dfs = pd.read_excel('excel_file.xls', sheet_name=None)
for table, df in dfs.items():
    df.to_sql(table, db)
    print(f'{df} inserted successfully')

Output of the code:

 inserted successfully

Convert Multiple Excel Workbooks to a Database table in Python

This is the best method to convert an excel file having multiple workbooks. First, get the name of the workbooks and then insert them into the database.

Check the following code to convert excel file to SQLite database.

import sqlite3
import pandas as pd

con=sqlite3.connect('database.db')
wb=pd.ExcelFile('excel_file.xls')
for sheet in wb.sheet_names:
        df=pd.read_excel('excel_file.xls',sheet_name=sheet)
        df.to_sql(sheet,con, index=False,if_exists="replace")
con.commit()
con.close()

Summary and Conclusion

We have learned how we can convert excel files to a database tables in python. These two are the best way to do the job done. If you have any questions please let me know in the comment section.