How to merge a bunch of excel files into one?

Question

Python

Hello, dear Toaster users.
Before me there was a task - it is necessary to automate routine work. There are a bunch of Excel files and you need to collect them into one. You will have to do this repeatedly, so the question arose in writing a program.
The question is: would it be fair to write a given program in Python? If not, what is the easiest way to solve this problem?
Thanks in advance to anyone who tries to help.

Answer 1

Let's say you have a bunch of Excel workbooks, all of which need to be merged into one file. Copying by hand is long and painful, so it makes sense to use a simple macro.
We open the book where we want to collect sheets from other files, enter the Visual Basic editor (ALT + F11), add a new empty module (in the menu Insert - Module) and copy the text of the following macro there:
Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
Application.ScreenUpdating = False 'disable screen updating for speed
'call dialog for selecting files to import
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="All files (*.*), *.* ", _
MultiSelect:=True, Title:="Files to Merge")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No files selected!"
Exit Sub
End If
'go through all selected files
x = 1
While x <= UBound(FilesToOpen)
Set importWB = Workbooks.Open(Filename:=FilesToOpen(x))
Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
importWB.Close savechanges:=False
x = x + 1
Wend
Application.ScreenUpdating = True
End Sub
After that, you can return to Excel and run the created macro through the menu Tools - Macro - Macros (Tools - Macro - Macros) or by pressing ALT + F8. A dialog box for opening a file will be displayed, where you need to specify one or more (by holding down CTRL or SHIFT) files, sheets from which you want to add to the current book.
PE

Answer 2

How about pandas?
Let's say all the files are in the same folder, and the necessary data is on the first sheet, then this can be done like this:

import pandas as pd
import os
folder = r'D:\Files\excel' #папка с файлами
files = [os.path.join(folder,f) for f in folder] #формируем список путей к файлам
all_file_frames = [] #сюда будем добавлять прочитанную таблицу 
for f in files:
    print('Reading %s'%f)
    tab = pd.read_excel(f)
    all_file_frames.append(tab)
all_frame = pd.concat(all_file_frames,axis=0) #  axis=0 если нужно добавить таблицу снизу и axis=1 если нужно слева
all_frame.to_excel('final_file.xlsx') #получим файл final_file.xlsx в os.getcwd()

Answer 3

I will also suggest looking at Apache POI poi.apache.org/spreadsheet/index.html , which is used to read and generate excel. Written in java, there are a bunch of examples on reading and conceiving xsl files - poi.apache.org/spreadsheet/examples.html
The project is mature, actively developed and used in a bunch of software -
poi.apache.org/spreadsheet/how-to. html - questions/answers with code and links to examples.
At one time, 8 years ago, they used it to generate invoices / forms.
PS. And here is an example of how to do it - www.coderanch.com/t/420958/open-source/Copying-she...
Read the entire thread.