Count contents from different Excel files

[juergenh1975]

Hi,

has anyone ever done something like this:

I have an Explorer folder with many Excel files. My desired process would have to output the number of rows with a certain value (e.g. “done”) from certain files with the same name range (e.g. Booking).
Example:
Booking001 with 10x done
Booking002 with 24x done
Bokking003 with 14x done
…004 8x done
…005 17x done
results after execution = 73
Is that possible?
As the icing on the cake: only count in files from the month “October” (variable)

Is anyone bored designing something like this?
I’ll buy you a coffee

sincerely
Joe

[pikorpa (Piotr Kołakowski)]

Hey @juergenh1975
Yes, it should be possible using UiPath Studio.

1. Create a DirectoryInfo object to access the folder that contains the Excel files:
   DirectoryInfo = New System.IO.DirectoryInfo("C:\Your\Folder\Path\")
2. Filter Excel files by Month:
   filteredFiles = directoryInfo.GetFiles("*.xlsx").Where(Function(file) file.CreationTime.Month = 10).ToArray()
   
   
3. Use the For Each activity to iterate over the elements in filteredFiles
4. Use Excel Application Scope (workbook path = currentFile.FullName)
5. Use Read Range activity
   Sheet Name - “Booking”
   Output: eg. bookingData
6. Use for each activity for bookingData:
   → use if activity inside to check if the value in the relevant column equals “done”
   CurrentRow("ColumnName").ToString.ToLower.Equals("done")
   Increment counter
counter = counter + 1

Counter is your result.

I hope this helps

[juergenh1975]

Hi, many thanks ! I will test it tomorrow and then let you know