how to read an excel file(.xlsm) with multiple sheets using for loop?

Question

Excel sheet format is similar. Problem:- Make a loop to transverse every excel sheet(.xlsm format) and combine into one dataframe.

import pandas as pd
import io
from google.colab import files
uploaded = files.upload()
for _, sheet in workbook.items():
  df_full = pd.DataFrame()  
    df = pd.read_excel(io.BytesIO(uploaded['202009 Testing - September - Diamond Plod Day & Night MKY021.xlsm']),sheet_name=None,header=8,usecols='BE,BH',nrows=4)
    df1 = pd.read_excel(io.BytesIO(uploaded['202009 Testing - September - Diamond Plod Day & Night MKY021.xlsm']),sheet_name=None,header=3)
 df_full = df_full.append(sheet)
 df1_full = df1_full.append(sheet)


df=df.assign(PlodDate='D5')
df['PlodDate']=df1.iloc[0,3]
df=df.assign(PlodShift='D6')
df['PlodShift']=df1.iloc[1,3]
df =df.rename({'Qty.2':'Loads','Total (L)':'Litres'},axis=1)
df = df.reindex(columns=['PlodDate','PlodShift','Loads','Litres','DataSource'])
df=df.assign(DataSource='Name of the Source File')
df

Code in Google Colab

Excel File Attached

Does this answer your question? [Using Pandas to pd.read\_excel() for multiple worksheets of the same workbook](https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook) — Aleka, Feb 12 '21 at 05:42

how to read an excel file(.xlsm) with multiple sheets using for loop?

0 Answers0