I have a folder with 200 excel files. I have the respective path and sheet names for each file in the folder. Is it possible to merge all of these files into one or a couple large excel file via python? If so, what libraries would be good for me to start reading up on for this type of script?

I am trying to condense the files into 1-8 excel files in total not 200 excel files.

Thank you!

CodePudding user response：

For example, suppose there are a.xlsx, b.xlsx, c.xlsx.

With using os(by import os) and endswith method, you can take all xlsx files.(You would easily find how to do it)

Then, read xlsx files in the loop(for or while statement) and with pandas and add it into a new excelwriter like below

e.g.

import pandas as pd

# Create a Pandas Excel writer using XlsxWriter as the engine.
   writer = pd.ExcelWriter('goal.xlsx', engine='xlsxwriter')

while True:
   df = pd.read_excel('excel_file_path')

# Write each dataframe to a different worksheet.
   df.to_excel(writer, sheet_name='Sheet{}'.format(sheet_number))

writer.save()

CodePudding user response：

1. Go to directory where all csv are located (eg cd C:\Temp)
2. Click on the file directory and type "cmd"; this will open a command prompt at the file location.
3. Type "copy *.csv combine.csv"

(Replace "csv" with the type of excel file you have, however this will probably work best with csv files)