Copy data from multiple excel files

Hi, All.

I need to copy data from multiple files in 1 folder (the file names are changed everyday) and compile the data in 1 excel file.

Example: The input folder is where all employees submit excel file (file name: their employee ID), each file contains employee ID & list of individual training program. I need to summary the data from all of these files in 1 excel file.

I know how to copy the data from 1 excel file to another, but I don’t know how to do it from multiple excel files.

Thank you in advance for your help.

Regards,
Rose

Hi Rose,
I’ve had this use case pretty often in my automations, this is my solution:
-Create a list variable and two table variables
-Use the Get Folder Contents action to read the names of all files in the folder to the list variable. This will capture the FULL file name including path and extension for each.
-Perform a For Each loop on the list of file names
-For each file name, set your first table to a blank table, then use the Open Spreadsheet action to open the spreadsheet name that is the current list element (${element}), and the Get Range Start:End action to extract the contents of the spreadsheet into that blank table.
-Now add a Nested For Each loop inside this one, that is For Each row in your first table, and use the Expression action to append that row onto the end of your second table.

This process will open each spreadsheet in the folder, then from that spreadsheet append each row onto the second table, so once it’s run every row from every file will be listed on the second table. You can then output that table to an excel document as you normally would. You may need to make some tweaks depending on your specific use case, for instance if your files have headers you will want the nested For Each loop to be filtered to only look at rows 2-*, and have your table two set such that the default first row will always be the headers, but the general principal is always the same.

-Ethan Morris
RPath RPA Manager

1 Like