How to Read Excel files in a folder until a condition is Met?

Help Community
, , ,
1

Hello All,

I have 6 Excel Files in a Folder as below :

DailyRunResults_25042022_130157.xlsx
DailyRunResults_25042022_130158.xlsx
DailyRunResults_25042022_130158.xlsx
RetryScans_25042022_161341.xlsx
DailyRunResults_25042022_130160.xlsx
DailyRunResults_25042022_130161.xlsx

I want the robot to read the three excel files before RetryScans_25042022_161341.xlsx then Stop.
And Merge the three files and again read the two files after RetryScans_25042022_161341.xlsx and merge the two files.

How to do this?
Please help.

2

Hello @Sanghamitra_Sahoo ,

Welcome to our UiPath Community!
Just need some clarifications. How are you determining which 3 files to merge and which 2 files to merge ? The logic of 3 files before RetryScans excel won’t work because what if the BOT picks up RetryScans file first.

Thanks!
Athira

3

Hi Athira,

I will first sort the files in ascending order.

and the first 3 DailyRunResult files will come first.
Then after some time a retry bot is run, so it will create the Retryscans file.
After the RetryScans process it will create may be 1 or 2 or may be 3 more DailyRun files.

So basically end of the day after sorting the files in Ascending order it looks like this :

DailyRunResults_25042022_130157.xlsx 25/04/2022 11:33 AM
DailyRunResults_25042022_130158.xlsx 25/04/2022 11:34 AM
DailyRunResults_25042022_130158.xlsx 25/04/2022 11:34 AM
RetryScans_25042022_161341.xlsx 25/04/2022 11:40 AM
DailyRunResults_25042022_130160.xlsx 25/04/2022 11:45 AM
DailyRunResults_25042022_130161.xlsx 25/04/2022 11:50 AM

4

Hello,

Please see the attached xaml file for your reference. Let me know if this works.
Sequence.xaml (9.4 KB)

Thanks!
Athira

5

Preformatted textHello @Sanghamitra_Sahoo ,

From the post what understood is, you are getting some files (Excel1,2,3) after some time Excel Retry will create and after that Excel4,5 will get create… plz correct if it’s wrong.

So if you need to use the same folder for the execution you ll have to move the processed files to some folder. Then in the next execution also you can use the same folder.

Plz try the flow which @athira.somasekharan has provided. If not working plz update here.

6

Hi Rahul,

Athiya’s script is fine but when I write the output of my fileList where I have saved all the files form the folder, its not showing in proper order.
Its showing in the following order :

DailyRunResults_25042022_130157.xlsx
DailyRunResults_25042022_130158.xlsx
DailyRunResults_25042022_130160.xlsx
DailyRunResults_25042022_130161.xlsx
RetryScans_25042022_161341.xlsx

How to store the files according to in ascending order?
I have already sorted it then also its not working.

7

It’s the correct sorted order. How you are getting the files?? Is there any possibility to rename the files while receiving??

Suppose if you are getting via email , when you download the attachment rename the files also.

Else after the downloading of all the files you will be having 6 files(after sorting)

Daily
Daily
Daily
Daily
Daily
Retry

Get all the files names to an array, loop through it and move it to separate folder. If count is 3 do a merge. Then move it to the backup folder.

Do the same for next 2 files

8

Is it changing the order after merging the data? If so, its sorting in the last modified date order. If you don’t want to change the order even after doing the required actions, you have to first copy all the files to a Copy folder and then perform the actions so that Copy Folder will be having the correct order.

Thanks!

9

Thanks Athira and Rahul for your help, I have done the same and it is being resolved :slight_smile: