How to convert multiple sheets in an excel workbook to csv files in python

Question

I have a excel workbook that contains 8 sheets with different alphabetical names. i want to create csv files for each of these sheets and store it in a folder in python. Currently i am able to do this for a single sheet from the workbook but i am struggling to make a workflow on how to convert multiple sheets and store them as csv in a single folder. Here is my code for now:

import pandas as pd
my_csv=r'C:\Users\C\arcgis\New\NO.csv'
data_xls = pd.read_excel(r"C:\Users\C\Desktop\plots_data1.xlsx", "NO", index_col=0)
p=data_xls.to_csv(my_csv, encoding='utf-8')

Martin Evans · Accepted Answer · 2022-03-25 09:38:34Z

6

If you want to get all of the sheets, you can pass sheet_name=None to the read_excel() call. This will then return a dictionary containing each sheet name as a key, with the value being the dataframe. With this you can iterate over each and create separate CSV files.

The following example uses a base filename with the sheetname appended, e.g. output_sheet1.csv output_sheet2.csv:

import pandas as pd

for sheet_name, df in pd.read_excel(r"input.xlsx", index_col=0, sheet_name=None).items():
    df.to_csv(f'output_{sheet_name}.csv', index=False, encoding='utf-8')

It assumes that all of your sheetnames are suitable for being used as filenames.

answered Mar 25, 2022 at 9:38

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

How to convert multiple sheets in an excel workbook to csv files in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related