How to merge multiple EXCEL files into one table?

Question

Python

How to merge several EXCEL files into one table (namely, into a table, in one Excel workbook, but into several books in one file)?
Those. there are 1600+ questionnaires received from a survey in a telegram bot. The bot automatically creates a separate file on the server based on the results of the survey. Those. ivanovivanivanovich.xlsx , federovfedrfedrovich.xlsx etc. They all answer the same questions. How can I combine them all into one table?

Answer 1

Get a list of files and enumerate it:
open a file, get the answer values ​​from the required cells, form a dictionary in memory - for each file, and add it to the general list.
Create a new excel file
Enumerate the list and write in rows in the table - each dictionary field in the desired column.
Save file.

Answer 2

I am doing this with pandas. I import the first questionnaire into pandas, then in a cycle into another dataframe the second, I attach it to the first second and the next questionnaire in the second df, then at the end of the pack of tables I normalize the data and upload it to excel.

Answer 3

The easiest way would be to save not in . xslx , and in . csv , then a couple of lines of code could have been dispensed with and a very fast merging would have happened.
And so:
1) Get a list of files, the first thing that comes to mind is FileSystemObject
2) Open each as a Workbook
3) Get a list of Sheets
4) Get data from the desired Cells
5) Save to memory. A good option for this is Dictonary
6) Save everything to a file in a convenient format