Reading all files in a folder and consolidating data into one file

Help
,
1

Hi!

The goal is to read all files in a specified folder and merge the data from these files into one structured table in one consolidated Excel file.

Things to note:

  1. All excel files do not have the same Sheet Names. I am not sure how to navigate this as it seems in order to read a range, you have to specify a sheet name. However, the data is always on the first tab of the excel sheet.
  2. I will need to read specific row/column names for each file, which will then be transferred to the consolidated file, structured into one table. These column/rows are not necessarily on the same rows, however there are key words i.e. “Tester” that is standardized among all files. Will I be able to use a key word function to locate the data?

I have little to no experience with UIPath but can learn quickly if the steps explained to me. I can give more details as well but this is the basic idea.

Thanks in advance!

Best,

Kalvin

2

Hi @kluo123

Try this:
Assign Var1 = Directory.Getfiles(“HereYourPath”)
or if you ony want the xlsx files try
Var1 = Directory.Getfiles(“HereYourPath”, “*.xlsx”)

After this assign each item to a FileVar (for example)
For Each Item in Var1
assign FileVar = item.ToString

Then write FileVar to your excel.

Edit: I missed half your question.
To get each first sheet of the excel, Use the Get Workbook Sheet activity inside an Excel scope.

Hope this helps

3

Hey! Thanks for the response.

Unfortunately I can only understand a portion of your solution (as I am just beginning to learn this tool). When you say write FileVar to your excel how do I do that?

Additionally, how do I use a the Get Workbook Sheet to then, read the data within the first sheet of each excel file?

4

Hi,
I’ve misread your question a little as well.
The solution I post is part of getting all the filenames within a directory and write it to an excel. But I don’t Think you mean this.
If you don’t know the filenames within that folder, you can still use the first part to identify the filenames.

Once you have a file name you can use that filename to open the excel and “Get workbook sheet” activity to get the sheet within that Excel file.
Extract the data from the excel and append them to a new destination excel.

All this will be part of a loop for each file in the designated directory.

I’m not sure this is the best way to do it, but that’s how I would build it. I don’t have much time at the moment or I would write you an example file. Maybe somebody else might beat me to it or ill take a look for you tomorrow.

5

BlankProcess.zip (29.1 KB)

hello @kluo123 ,
i believe this will help you to understand.

before you run my workflow, please check this :

  • all excel files are different file name.xlsx in folder ‘data’.
  • i read data number from all excel files.
  • the data number is from different worksheet of each excel files. (this one is kind of hardcoded, because system won’t know which worksheet the human wants to retrieve the needed data right? so we tell the system which worksheet to retriving the needed data.)
  • i write the retrived data number from different worksheet of each excel files into 1 new excel file named ‘result.xlsx’ in Column B.

Hope this is your issue that you facing :slight_smile: