CODEWITHSUNDEEP
pythonpandasappendxlsxwriter
JDEqualzero
JDEqualzero

Reputation: 63

ExcelWriter overwriting sheets when writing

This program should take the contents of individual sheets and put them into one excel workbook. It almost does that, but it is overwriting instead of appending new sheets into the final workbook. I read that pandas excel writer is the way to go with this, any ideas as to why its having this behavior?

import xlwt, csv, os
from openpyxl import load_workbook
import pandas as pd
from pandas import ExcelWriter

csv_folder = r'C:\Users\Me\Desktop\Test_Folder\\'

for fil in os.listdir(csv_folder):

    if '.xlsx' not in fil:
        continue
    else:
        pass

    df = pd.read_excel(csv_folder+fil, encoding = 'utf8')

    file_name = fil.replace('.xlsx','')

    writer = pd.ExcelWriter('condensed_output.xlsx', engine = 'xlsxwriter')
    df.to_excel(writer, sheet_name = file_name)
    writer.save()
    #writer.close()

Upvotes: 4

Views: 2305

Answers (1)

mauve
mauve

Reputation: 2763

Make sure the writer.save() is outside of the loop. Also be aware of the character limit on sheetnames, so if the file names are the same up to a certain point, you run the risk of writing over a sheetname that way as well. 

import xlwt, csv, os
from openpyxl import load_workbook
import pandas as pd
from pandas import ExcelWriter

csv_folder = r'C:\Users\Me\Desktop\Test_Folder\\'
writer = pd.ExcelWriter('condensed_output.xlsx', engine = 'xlsxwriter')
for fil in os.listdir(csv_folder):

    if '.xlsx' not in fil:
        continue
    else:
        pass

    df = pd.read_excel(csv_folder+fil, encoding = 'utf8')

    file_name = fil.replace('.xlsx','')


    df.to_excel(writer, sheet_name = file_name) 

writer.save() #make sure this is outside of the loop.

ETA: establish the writer outside of the loop as well

Upvotes: 2

Related Questions