Python — How to Read Multiple Excel Sheets or Tabs

Ryan Arjun
4 min readFeb 13, 2021

As a data engineer or python developer and you have to read the data from an excel data file having multiple sheets or tabs and save the data into other data sources like SQL Server database or SQLite then it can be easily possible in Python.

There is no limitation of rows in csv or text file format but in case of excel file, there are only 1000000 rows allowed in per excel sheet or tab.

As we know, Python is one of the fastest growing programming languages. Whether it’s data manipulation with Pandas, creating visualizations with Seaborn, or deep learning with TensorFlow, Python seems to have a tool for everything.

Many programming languages have sign available as a built-in function. Python doesn’t, but we can define our own!

In the below Python code, we are using SQLite to store the data from an excel data file having multiple sheets or tabs. As you know that, we can not open this excel file with multiple sheets due to having around billions of records and if you try to open this file then your system would be hangout or freezed.

We have to use the below python libraries to accomplish this task —

#pandas is a Python package that provides fast, flexible, and expressive data structures designed to make working with structured (tabular, multidimensional, potentially heterogeneous) and time series data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python.

import pandas as pd

#SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String

SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards.

import sqlite3
import os
from sqlalchemy import MetaData
from sqlalchemy.orm import mapper

After importing all the above python libraries, we have to set the excel file path from your…

--

--

Ryan Arjun

BI Specialist || Azure || AWS || GCP — SQL|Python|PySpark — Talend, Alteryx, SSIS — PowerBI, Tableau, SSRS