Python: how to make a loop to copy data from different Excel files into a new one in an iterative wa-CodePudding

I need to copy data from different Excel files into a new one. I would like to just tell the program to take all the files into a specific folder and copy two columns from each of them into a new Excel file. I tried a for loop but it overwrites data coming from different files and I get a new Excel file with just one sheet with data copied from the last file read by the program. Could you help me, please? Here is my code:

import os.path
import pandas as pd
folder=r'C:\\Users\\PycharmProjects\\excelfile\\'

for fn in os.listdir(folder):
    fx = pd.read_excel(os.path.join(folder, fn), usecols='H,E')
    with pd.ExcelWriter('Output.xlsx') as writer:
        ws = os.path.splitext(fn)[0]
        fx.to_excel(writer, sheet_name=ws)

CodePudding user response：

You should open the output file in append mode like so:

with pd.ExcelWriter("Output.xlsx", engine='openpyxl', mode='a') as writer:
    ws = os.path.splitext(fn)[0]
    fx.to_excel(writer, sheet_name=ws)