Import 10 csv files and export as 10 worksheets of 1 xlsx

I have 10 csv files and want to save all the files as 10 worksheets of 1 xlsx file.

data1.csv,data2.csv,.......,data10.csv.

Attempt

import glob
import numpy as np
import pandas as pd

all_datasets = pd.DataFrame()
for x in glob.glob("*.csv"):
    df = pd.read_csv(x)

# I want to export the corresponding csv files as 10 worksheets of 1 xlsx 

#initialze the excel writer
writer = pd.ExcelWriter('all_datasets_combinedworksheets.xlsx', engine='xlsxwriter')


frames = {'sheetName_1': df1, 'sheetName_2': df2,
        'sheetName_3': df3,'sheetName_4': df4}

for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step
writer.save()

I’m open to other approach, please share your code, thanks in advance

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

1. You’re overwriting the variable df in every iteration of your loop.
2. You don’t have any variables called df1, df2 etc. but you’re trying to use them to create your frames dictionary.
3. You never use all_datasets.

Try:

import os
frames = {f: pd.read_csv(f) for f in os.listdir(path) if f.endswith(".csv")}
writer = pd.ExcelWriter('all_datasets_combinedworksheets.xlsx', engine='xlsxwriter')

for sheet, frame in frames.items():
    frame.to_excel(writer,sheet_name=sheet)

writer.save()

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0