Converting Excel data to SQL(PostgreSQL) Table in Python

Excel to SQL Tables


In the real world, data is mostly in the format of files i.e. Excel sheets. These excel sheets are not well structured and it contains multiple sheets, which is very complex to handle and hard to visualize (larger data) with using cross sheets with some complex conditions. SQL does this task very easily with schema structure and joins.

SQL is a Structured Query Language, which is the base of all the SQL Database. for e.g. PostgreSQL, MongoDB, Microsoft SQL Server, etc.

SQL is much faster than Excel sheets. It can take hours to do a task in Excel and in SQL it takes minutes to perform the same task.

Every big company uses databases to handle data. So there is a requirement of converting this excel sheet data into SQL Tables.

Steps to convert the Excel data to SQL Table


Check that you have all the required libraries.

# To create the data frame into Table

from sqlalchemy import create_engine

#For manipulating data frame

import pandas as pd


First load the excel sheet.


index_col = 0 => It will set first column as index column.

header =[1,4] => It will set first 4 row as header which is column names.


Format the excel sheet so that columns contain the same datatypes and also handle the missing values.

You have four main options for converting types in pandas:

1. to_numeric() - provides functionality to safely convert non-numeric types (e.g. strings) to a suitable numeric type. (See also to_datetime() and to_timedelta().)

2. astype() - convert (almost) any type to (almost) any other type (even if it's not necessarily sensible to do so). Also allows you to convert to categorial types (very useful).

3. infer_objects() - a utility method to convert object columns holding Python objects to a pandas type if possible.

4. convert_dtypes() - convert DataFrame columns to the "best possible" dtype that supports pd.NA (pandas' object to indicate a missing value).

Handling Missing Values:


fillna => It will fill the null value with the mean of that column.

For string type, we can replace it with string to handle missing or null values.


Create Engine in sqlalchemy

engine = create_engine(r’postgresql://user_name:password@server_ip_address:port_number/Database_name’, echo = True)

create_engine => It will create the engine for postgresql that will connect the server to the postgreSQL .

Type your username, password, server IP address, Port number, and Database Name to connect that specified server.


to_sql => It will convert the Excel sheet into specified table name.

if_exists=>It will replace the Table to handle multiple execution of program.

Replace will drop the table before inserting new values.

To Execute SQL query using the following command:

engine.execute(“Write the SQL Query”)

You can read more about the to_sql from the below link:

Read more about sqlalchemy:




Lead Data Scientist at Tata Power Ltd.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

~@@Covid-19: How will all of this affect business

Extract specific string from an Excel column

Healthy Apps That You Can Try.

Recursion or Loops?

Win365 — KHuyến mãi thưởng lớn tại nhà cái

“The Best” of the Best Practices for Test Automation

Transforming IT for the COVID-19 Disrupted Business Context

Emergency Coding: Getting Things Done When Lives Depend On It

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vikas Maurya

Vikas Maurya

Lead Data Scientist at Tata Power Ltd.

More from Medium

How to Rename Column Headings with Pandas

Python — How to improve code

What I Learned at Work this Week: Merge with Pandas

5 Lines of Code: Pandas DataFrame to SQL Server