topic RE: Different sheets of excel in Product Forum

Different sheets of excel

aanchalm — Thu, 20 Apr 2023 11:29:00 GMT

Hi team,

I have an excel with multiple sheets, I want to get data from all worksheets into different collections, the number of sheets can vary.

------------------------------
aanchal mathur
------------------------------

RE: Different sheets of excel

Mukeshh_k — Thu, 20 Apr 2023 11:48:00 GMT

@aanchalm - One of way of doing it is to get all the worksheets in the excel and set counter to 0 and set worksheet position - Follow below approach > Get worksheet names as Collection > Loop and Set Positions with Counter (my requirement was to get worksheet as collection for 3 worksheet in the start but you can customise as per your own requirements) > Get worksheet as Collection :

Example:

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

RE: Different sheets of excel

aanchalm — Thu, 20 Apr 2023 12:49:00 GMT

But in case we do not know how many sheets are there then how can we set the choice criterion?

------------------------------
aanchal mathur
------------------------------

RE: Different sheets of excel

Mukeshh_k — Thu, 20 Apr 2023 14:01:00 GMT

Hi @aanchalm : If you could explain the scenario how exactly are you planning to use these collection data or to be feeding as input - I will be able to suggest you the approach accordingly - but Ideally Input files if its meant to be fed as Data, it should have some sort of certainty around it - It can go around to have "N" number of sheets having the input data - that would loose its feasibility at some point in time, either it needs fixing or some sort of knowledge pattern for possible sheets occurrence to design a solution around it .

RE: Different sheets of excel

MichealCharron — Thu, 20 Apr 2023 15:31:00 GMT

@aanchalm

You could always use a collection to house other collections. As long as you are not working with 100s of thousands of rows in each worksheet, Blue Prism should be able to handle it okay. Take the following stages:

Note: I did not include the Excel opening and closing of the workbook in the Image.

Get the worksheet names into the "Worksheet Names" collection.
Loop through the "Worksheet Names" collection.
Add a row to the "Worksheet Data" collection
Assign the worksheet name to the current row in the "Worksheet Data" collection.
Get the worksheet data and assign directly into the current row in the "Worksheet Data" collection.

What you end up with is a collection of collections that you can loop through and process individually:

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Different sheets of excel

michaeloneil — Thu, 20 Apr 2023 20:07:00 GMT

Hi @aanchalm

I have a solution for this but it will require a little bit of coding hopefully you're familiar enough with how to do this but I'll try to break it down as much as possible. Firstly you will need to create an action in the Utility - Collection manipulation vbo (or you could create a a separate vbo but to keep it simple i created it here). Add a code stage with the following code:

Try

Output_Collection = Input_Collection.Copy

Dim Newcol As DataColumn = New DataColumn(Column_Name, GetType(DataTable))
Output_Collection.Columns.Add(Newcol)
Output_Collection.Columns(Newcol.ColumnName.ToString).SetOrdinal(Column_Index)

Success = True

Catch ex As Exception

Message = ex.Message.ToString
Success = False

End Try

The inputs are - Input Collection, Column Index, Column Name , and the outputs are - Output Collection, Message, Success
Once you have created the action then you need to create a flow that will get all the worksheet names, add an embedded collection to a collection, then for each worksheet you will add a row to the desired collection out then add the worksheet data to it. My flow is shown below and when you are done you will have a collection with a single column and multiple rows of embedded collections. For the embedded collecitons action it will create the first column with no row data but you need to add the column index as 0 since its adding the column to a collection that doesnt already contain defined columns or any data. Hopefully this is clear and see the screenshots below for reference.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Different sheets of excel

MichealCharron — Thu, 20 Apr 2023 20:28:00 GMT

@Michael O'Neill

Actually, I am not the originator of the question.

I'm not too sure what you are answering here. By defining my collection of "Worksheet Data", I can already define a column as a collection and by not defining the fields of that collection column I can assign any collection to it.

I just did not include the image of this definition because it was implied from the image of the results.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Different sheets of excel

michaeloneil — Thu, 20 Apr 2023 20:48:00 GMT

hi Michael I realized after posting it wasnt you that had asked the question. I might have misunderstood what the issue was on their collection (not reading it properly), sorry for any confusion.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Different sheets of excel

aanchalm — Fri, 21 Apr 2023 01:45:00 GMT

Understood Michael, thank you so much

------------------------------
aanchal mathur
------------------------------