Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Python: how to make a loop to copy data from different Excel files into a new one in an iterative way with pandas

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)

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading