Python convert all sheets of excel to csv

Question

I am using the following code to convert excel to csv file:

import glob 
path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx')
for excel in path_to_excel_files:
 out = excel.split('.')[0]+'.csv'
 df = pd.read_excel(excel)
 df.to_csv(out) 

But the problem with this code is that it takes data only from the first sheet and not other sheets. I have 4 sheets and want them to be converted to csv too. Please help. 

Comments

Try the below code this will work
import pandas as pd

data = pd.read_excel('sample1.xlsx', sheet_name=None)


# loop through the dictionary and save csv

for sheet_name, df in data.items():

    df.to_csv(f'{sheet_name}.csv')

Answer 1

You will have to parse through the sheets and then get data. Refer to the following code:

import pandas as pd

df = pd.DataFrame()
xlfname = 'Productivity Report.xlsx'
xl = pd.ExcelFile(xlfname)

for sheet in xl.sheet_names:
df_tmp = xl.parse(sheet)
df = df.append(df_tmp, ignore_index=True,sort=False)

print(len(df))

csvfile = 'sample.csv'
df.to_csv(csvfile, index=False)

Comments

I believe he is looking for multiple excel workbook. your code work for single workbook. Isn't it ?

---

Hi, @There,

Yes, it is.