Blue Prism Product

 View Only
last person joined: 18 hours ago 

This community covers the core Blue Prism RPA product.

  • 1.  Different sheets of excel

    Posted 04-20-2023 12:29

    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
    ------------------------------


  • 2.  RE: Different sheets of excel
    Best Answer

    Posted 04-20-2023 12:48

    @A S - 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
    ------------------------------



  • 3.  RE: Different sheets of excel

    Posted 04-20-2023 13:49

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



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



  • 4.  RE: Different sheets of excel

    Posted 04-20-2023 15:01

    Hi @A S : 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 . 



    ------------------------------
    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
    ------------------------------



  • 5.  RE: Different sheets of excel

    Posted 04-20-2023 16:31

    @A S 

    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.

    1. Get the worksheet names into the "Worksheet Names" collection.
    2. Loop through the "Worksheet Names" collection.
    3. Add a row to the "Worksheet Data" collection
    4. Assign the worksheet name to the current row in the "Worksheet Data" collection.
    5. 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
    ------------------------------



  • 6.  RE: Different sheets of excel

    Posted 04-20-2023 21:07

    Hi @A S 

    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
    ------------------------------



  • 7.  RE: Different sheets of excel

    Posted 04-20-2023 21:28

    @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
    ------------------------------



  • 8.  RE: Different sheets of excel

    Posted 04-20-2023 21:48

    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
    ------------------------------



  • 9.  RE: Different sheets of excel

    Posted 04-21-2023 02:45

    Understood Michael, thank you so much



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



Welcome to the Blue Prism RPA Product Community!

Whether you’re looking to manage a complex infrastructure, maintain security and compliance, bring new products to market faster, or gain operational speed and agility in an uncertain economy, Blue Prism delivers — with the flexibility you need to create the business you want. From deployment on-premise, through a cloud service provider or as SaaS, to a skillful and adaptable digital workforce that continually expands to meet your enterprise needs, you can gain enhanced operational insight and control while your people reclaim the time they need to focus on great work.

Product PageKnowledge BaseBlue Prism Training Offering
Product Research Programv6 End of Life

FAQs

Blue Prism is intelligent automation — business-developed, no-code automation that pushes the boundaries of robotic process automation (RPA) to deliver value across any business process in a connected enterprise.

A combination of RPA with expanded cognitive and AI capabilities, Blue Prism is different than other automation technology on the market. With one Blue Prism license, you gain instant access to an already AI equipped digital workforce, along with the tools you need to build and delegate automations. Click here for more information on Blue Prism and Intelligent Automation.
To learn more about how Blue Prism RPA can help your organization and how much it will cost to get started, please Contact our Sales department.
Blue Prism RPA can be downloaded from our customer portal. If you would like to consume or download any material it is necessary to create an account on the Portal. Once you have registered, you can access the download options for Blue Prism here.
Yes! Installed on your own machine and supported by our training materials and product documentation, you can use all the features of the full enterprise product for free with our Blue Prism Trial – giving you the opportunity to learn the basics before moving to a full production implementation. Click here for more information and to download the trial.
Yes! You can access our known issue list for Blue Prism from our Support Portal.
Regardless of your industry, Blue Prism’s Digital Workforce can adhere to strict governance and compliance standards without limiting productivity. Click here for more information on how your industry can benefit from Blue Prism.