Python + CSV + xlsx + Pandas : Merging multiple CSV files into one multisheet CSV

Question

I want to produce a multisheet CSV file from a multisheet xlsx file. For that I wrote this code:

xls = xlrd.open_workbook(r'Smallys ORDER.xlsx', on_demand=True)
df_list = []

names = xls.sheet_names()
names.remove('EVENT')

for name in names:
    prod = pd.read_excel('Smallys ORDER.xlsx', name, index_col=None)
    prod.to_csv(name + '.csv', encoding='utf-8', index=False) 
    df_list.append(prod)

df_final = pd.DataFrame()  

for df in df_list:
    df_final.append(df)

df_final.to_csv('smallys.csv', encoding='utf-8', index=False)

It successfully converts the individual xlsx sheets to csv files. But cannot produce the multisheet csv.

this print(df_final) outputs this :

Empty DataFrame
Columns: []
Index: []

long long int · Accepted Answer · 2019-03-25 11:10:30Z

2

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.append.html mentions that the append method returns a new object. So it has to be stored into a variable. Since you are not storing, your df_final is always empty. Try adding df_final = df_final.append(df)

answered Mar 25, 2019 at 11:10

long long int

233 bronze badges

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

2 Comments

Debbie Over a year ago

It combines all the csv's into one csv file, one sheet. No multi-sheet csv produced.

alexytsu Over a year ago

@Debbie What exactly are you trying to achieve by a "multisheet" csv? The original "Smallys ORDER.xlsx" file is multisheet because of attributes special to .xlsx files. A .csv is simply a file full of data delimited by commas and has no support for multisheet.

Collectives™ on Stack Overflow

Python + CSV + xlsx + Pandas : Merging multiple CSV files into one multisheet CSV

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related