Converting Excel file with multiple sheets into multiple csv files using Jupyter notebook

Question

I have an excel file that contains multiple sheets. I want to convert these sheets into separate CSV files.

I tried this code and got an ordered dictionary of the sheets. Now I need to save them as CSV files in one step, instead of having to save each one manually in a separate step

xls = pd.ExcelFile('file.xlsx')
sheets = {}
for sheet_name in xls.sheet_names:
    sheets[sheet_name] = xls.parse(sheet_name)

Quang Hoang · Accepted Answer · 2020-08-06 20:28:17Z

4

You can use to_csv to save dataframe as csv file:

# I prefer reading excel with pd.read_excel
# passing `sheet_name=None` returns a dictionary 
# with the form {sheet_name: dataframe}
data = pd.read_excel('file.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')

answered Aug 6, 2020 at 20:28

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Over a year ago

what does the f before{sheet_name} stand for or do?

Quang Hoang Over a year ago

@Michael that f-string in Python 3.

user2682863 Over a year ago

for those unfamiliar with pandas, pd comes from import pandas as pd

Collectives™ on Stack Overflow

Converting Excel file with multiple sheets into multiple csv files using Jupyter notebook

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related