How to get all Excel files data (from a folder) into different collections ?

[Sukesh Kumaru]

I am trying to develop a process which firstly gets all excel (in this case there are 4 excel files in a folder) filenames into a collection (until here everything works as expected) and looping this collection and trying to get excel data of each file in a folder but the destination collection is single and gets overwritten in each loop,

My Question is how to get each excel file data in a separate collection i.e excel1 in colln1, excel2 in colln2 and so on..............
PFA for your clear understanding.

Thanks in advance.

 

[VJR]

Hi Sukesh Kumaru,

Your way of explaining is good. Your diagrams are also good with all the Note stages, blocks and the comments written in the screenshot for clarification.
May I know what are you trying to achieve by having multiple collections. The reason I am asking is this is by design from Blue Prism for 'Get Worksheet as Collection' which returns one collection at a time. There may be another way to do the same thing if you could explain what you are trying to do.

 

[Sukesh Kumaru]

Hi Sukesh Kumaru,

Your way of explaining is good. Your diagrams are also good with all the Note stages, blocks and the comments written in the screenshot for clarification.
May I know what are you trying to achieve by having multiple collections. The reason I am asking is this is by design from Blue Prism for 'Get Worksheet as Collection' which returns one collection at a time. There may be another way to do the same thing if you could explain what you are trying to do.

Hi VJR,
This is a simple practice test which am trying it on my own thought, that fetching all excel files data into different collections.
Thanks in advance.

 

[VJR]

Okay, usually a general practice would be to open the excel, get it into collection, finish the processing on the collection and close the file and the same sequence will continue for every single file inside the loop.
If you are aware of how many excel files are already there you can design those many collections in the diagram, but that won't be the case since the file count is not known.
Hence you cannot do File 1 in Collection 1, File 2 in Collection 2, but you can get File 1 in Collection 1, then in the 2nd iteration of the loop copy collection 1 to collection 2 (collection 2 should already exist in the diagram) and so on but you can only interchange data between these two collections in this case which I'm sure is not what you are looking for.
Dynamic generation of one collection into another directly from the process diagram is not yet available in BP at least not in version 5. But you can try making use of the Code stage. Just like how the code is written to dynamically generate the collection returned from 'Get worksheet as collection' see if you can dynamically create a collection based on the nth Excel file passed (where n = 1, 2,3...).

 

[Sukesh Kumaru]

Th

Okay, usually a general practice would be to open the excel, get it into collection, finish the processing on the collection and close the file and the same sequence will continue for every single file inside the loop.
If you are aware of how many excel files are already there you can design those many collections in the diagram, but that won't be the case since the file count is not known.
Hence you cannot do File 1 in Collection 1, File 2 in Collection 2, but you can get File 1 in Collection 1, then in the 2nd iteration of the loop copy collection 1 to collection 2 (collection 2 should already exist in the diagram) and so on but you can only interchange data between these two collections in this case which I'm sure is not what you are looking for.
Dynamic generation of one collection into another directly from the process diagram is not yet available in BP at least not in version 5. But you can try making use of the Code stage. Just like how the code is written to dynamically generate the collection returned from 'Get worksheet as collection' see if you can dynamically create a collection based on the nth Excel file passed (where n = 1, 2,3...).

Hi VJR,
Thanks for your valuable information and lets close this thread here.

 

[VJR]

You're welcome. Since you are the OP (original poster) of this thread you will have the option to close it.