CODEWITHSUNDEEP
pythonexcelpandascsv
Varun
Varun

Reputation: 25

How do I create a new sheet in excel for every csv file I have in my folder

import os
import pandas as pd
from glob import glob
import pathlib
import fileinput
import sys
import xlsxwriter

def csv_folder_input(folder):
    path = sys.path[0]
    path = path + "/" + folder
    os.chdir(path)
    counter = 1
    for filename in os.listdir(path):
        if filename.endswith(".csv"):
            with open(filename, 'r') as csvfile:
                df = pd.DataFrame(csvfile)
                with pd.ExcelWriter('output.xlsx') as writer:
                    df.to_excel(writer, sheet_name='sheet '+str(counter), index=False)
                    writer.save()
                    counter = counter + 1

Currently it overrides each excel file sheet, but I want each CSV file to make a new sheet on excel

Upvotes: 2

Views: 145

Answers (2)

williamr21
williamr21

Reputation: 147

It re-writes the existing excel file because the ExcelWriter is defined inside the loop. You need to create an excel only once by defining it outside the loop and should add sheets to them using the loop. The below code worked for me

def csv_folder_input(folder):
path = sys.path[0]
path = path + "/" + folder
os.chdir(path)
counter = 1
with pd.ExcelWriter('output.xlsx') as writer:
    for filename in os.listdir(path):
        if filename.endswith(".csv"):
            with open(filename, 'r') as csvfile:
                df = pd.DataFrame(csvfile)
            df.to_excel(writer, sheet_name=filename, index=False)
            print(f"Added sheet to excel: {filename}")
            counter = counter + 1
writer.save()
writer.close()

Upvotes: 1

Harshwardhan Nandedkar
Harshwardhan Nandedkar

Reputation: 253

def csv_folder_input(folder):
    path = sys.path[0]
    path = os.path.join(path,folder)
    os.chdir(path)
    counter=1
    writer = pd.ExcelWriter('output.xlsx')
    for filename in os.listdir(path):
        if filename.endswith(".csv"):
            print(filename)
        with open(filename, 'r') as csvfile:
            counter=counter+1
            print(counter)
            df = pd.read_csv(csvfile)
            df.to_excel(writer,sheet_name=os.path.splitext(filename)[0]+'_'+str(counter),index=False)
    writer.save()
    writer.close()

I have just modified your function. Please note that to read your Dataframe into a csv, pd.read_csv() is the function.

You have used pd.DataFrame(csv_file), which I believe is the incorrect way to read it.

You will find your output.xlsx in the same path as your folder.

Upvotes: 1

Related Questions