Opening Excel File Depending on the name with in loop

Help
1

Hello Everyone,

I have 2 folders with Excel files 1. Downloaded_RKIV_Reports 2. ModifiedReports
SSC

For each file in the 1st Folder I have to read the data and paste into the file in the 2nd Folder.

The condition is the Robot has to pick the correct file in the 2nd folder.
For example : If the file name from the 1st folder is SEP2016_BPO_RK, the robot has to pick the Modified_SEP2016_BPO

The matching criteria for this scenario is BPO.

Can it be achievable, if yes please guide me through.

Much appreciated for your kind help in advance.

Regards
Vishnu

2

Hey @winningvish,

Please find the workflow in attachment.Please have a look and do let me know if it helps.

Test.xaml (11.4 KB)

1 Like
3

This is a more complete solution. I’ve created two sample folders to test with based on your logic. A dictionary of key value pairs is gathered of files in each and their file names. The key is the full filename and the value is the key word you need to isolate on each to apply your matching logic (e.g. the substring BPO).

This uses regex to isolate the BPO substring from each file name in both folders. It assumes it will always have the substring of SEP2016 and file extension .xlsx. So if this changes, it will need to be expanded in terms of how the match works.

ListFiles.zip (19.5 KB)

1 Like
4

Hello @ronanpeter,

Firstly very much appreciated for your time and effort.

A bit confused to understand this List and Dictionary. But I have used Array of string with split option, which solved my issue.

Would you please help me to understand the difference between List and Array?

Regards
Vishnu

5

Essentially a Dictionary is a list of Key Value Pairs. So we can map one Key to a single Value. In this case it us useful as we can map the filepath of each excel workbook to the keyword of the filename we need (in the example I created it was ‘BPO’) to signify a file that needs to be changed.

So for the FileNamesListA dictionary it will have something like this in the format of [ Key : Value ]:
[ C:\ListFiles\FolderA\SEP2016_BPO_RK.xlsx : BPO,
C:\ListFiles\FolderA\SEP2016_DG_RK.xlsx : DG ]

You end up with a dictionary for each of the two folders. As FolderB contains the files you want to act on, you use a ForEach loop on FileNamesListB dictionary, seeing if the Value of each Key Value Pair is also in the Values contained in FileNamesListA. If so, you can then grab the Key of that Pair (the file path) and begin to act on the workbook as you need.

An Array is simply a list of values, or objects, without any mappings. It seemed the most efficient solution here.

1 Like
6

Very well explained Peter and clearly understood the concept now.

Thank you very much.

Regards
Vishnu

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.