0

The data in the excels is saved in annual folders (2020, 2021, 2022). Each year has sub folders for the 12 months (01-2022, 02-2022, 03-2022) and each monthly folder has sub folders for each day (01-01-2022, 01-02-2022, 01-03-2022). The excels are saved for each day. I need to read data from a specific common tab of these excels.

This is the code i have so far; but its not giving me any output:

filepath = '\\\\MUMPRR09\\1-Sector Reports\\2022'

if filepath.endswith(".xlsm") and sheet_name("ABC"):
            df = pd.read_excel(files,sheet_name='ABC')
            a = df.iloc[0][0]  
            company.append(a)
Bhagyashree
  • 1

1 Answers1

0

you might want to use glob and pandas for finding and opening your files

from glob import glob
from os.path import join
import pandas as pd

# join function joins pieces of a file path correctly for any OS
# glob is able to find files based on wildcards (the asterix allows 'anything')
files = list(glob(join(
    "202*",  # allow year starting with 202x
    "*",  # allow any month
    "*",  # allow any day
    "*.xlsm"  # allow any file ending with .xlsm
)))

print(files)

# you might want to sort your paths (?)
for file in sorted(files):
    # check this for excel help https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook
    xls = pd.ExcelFile(file)
    df = pd.read_excel(xls, 'Sheet1')
Klops
  • 951
  • 6
  • 18