How to use Python to extract Excel Sheet data

Question

I have many folders, each folders contains 1 excel file like 1Aug2022, 2Aug2022...

I want python to Read thru all Folders, and only open the excel file name like 19AUG2022, the excel file have many sheets inside like IP-1*****, IP-2*****, IP-3*****. Then go to sheets with (IP-2*****) to extract 2columns of data.

How can I do it in python?

Does this answer your question? [Reading an Excel file in python using pandas](https://stackoverflow.com/questions/17063458/reading-an-excel-file-in-python-using-pandas) — alex, Sep 13 '22 at 10:06

score 0 · Answer 1 · answered Sep 13 '22 at 09:56

You can use pandas package: https://pandas.pydata.org/

an example is

import pandas as pd
your_excel_path = "your/path/to/the/excel/file"
data = pd.read_excel(your_excel_path, sheet_name = "19AUG2022") # If you want to read specific sheet's data
data = pd.read_excel(your_excel_path, sheet_name = None) # If you want to read all sheets' data, it will return a list of dataframes

score 0 · Answer 2 · answered Sep 13 '22 at 09:59

As Fergus said use pandas.

The code to search all directorys may look like that:

import os
import pandas as pd

directory_to_search = "./"
sheet_name = "IP-2*****"
for root, dirs, files in os.walk(directory_to_search):
    for file in files:
        if file == "19AUG2022":
            df = pd.read_excel(io=os.path.join(root, file), sheet_name=sheet_name)

How to use Python to extract Excel Sheet data

2 Answers2