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 9, 2019 in Python by Shruti
7,558 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 9, 2019 by Omkar
• 69,220 points
I believe he is looking for multiple excel workbook. your code work for single workbook. Isn't it ?

Hi, @There,

Yes, it is.

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
3,476 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
• 58,020 points
2,573 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
• 58,020 points
1,042 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, 2019 in Python by Mian Tanzeel
18,186 views
0 votes
2 answers
+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 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,456 views
0 votes
1 answer
+5 votes
6 answers

Lowercase in Python

You can simply the built-in function in ...READ MORE

answered Apr 11, 2018 in Python by hemant
• 5,790 points
4,109 views
–1 vote
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, 2019 in Python by Omkar
• 69,220 points
1,295 views
+1 vote
2 answers

Python convert extracted excel file to csv

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

answered Aug 30, 2019 in Python by Mian Tanzeel
7,005 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP