Python convert all sheets of excel to csv

0 votes

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. 

Feb 8 in Python by Shruti
322 views

1 answer to this question.

0 votes

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)
answered Feb 8 by Omkar
• 67,660 points

Related Questions In Python

0 votes
1 answer

How can I convert a list of dictionaries from a CSV into a JSON object in Python?

You could try using the AST module. ...READ MORE

answered Apr 17, 2018 in Python by anonymous
797 views
0 votes
1 answer

Convert string list of dict from csv into JSON object in python

You can use the ast module Ex: import ast s = """[{'10': ...READ MORE

answered Sep 12, 2018 in Python by Priyaj
• 56,940 points
602 views
0 votes
1 answer

Convert string list of dict from csv into JSON object in python

You can use the ast module Ex: import ast s = """[{'10': ...READ MORE

answered Sep 24, 2018 in Python by Priyaj
• 56,940 points
72 views
+1 vote
2 answers

Python convert XLS and XLSX file to csv

XLSX tables are usually created in MS ...READ MORE

answered Aug 30 in Python by Mian Tanzeel
3,674 views
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 6 in Python by Neha
• 330 points

edited Jul 8 by Kalgi 346 views
+4 votes
6 answers
0 votes
1 answer

Python convert excel file to csv

Here you go: import glob path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx') for ...READ MORE

answered Feb 8 in Python by Omkar
• 67,660 points
118 views
+1 vote
2 answers

Python convert extracted excel file to csv

Some services require table data in CSV ...READ MORE

answered Aug 30 in Python by Mian Tanzeel
1,347 views