Get Table

[khalidyoo]

Hi everyone, I need a trick if possible, I use the action get table, I want to copy 7 tables a same collection, the problem is every time it crushes the previous table, I want copy all the tables in the same collection.

 

[VJR]

Hi khalidyoo,

Here is a trick that you can make use of.
- After you get the data in Collection 1 from the Get Table
- Use 'Append Rows to Collection' action to place the same rows into another Collection 2.
- Next time when you do a Get Table again the new data will be overwritten into Collection 1 and appended to the bottom of Collection 2.
At the end of 7 tables, Collection 2 will be your final collection.

 

[Sukesh Kumaru]

Hi khalidyoo,

Here is a trick that you can make use of.
- After you get the data in Collection 1 from the Get Table
- Use 'Append Rows to Collection' action to place the same rows into another Collection 2.
- Next time when you do a Get Table again the new data will be overwritten into Collection 1 and appended to the bottom of Collection 2.
At the end of 7 tables, Collection 2 will be your final collection.

Hi
khalidyoo,

VJR came up with the Best Approach you can go ahead and implement this logic you will get the results as per your requirement.

 

[khalidyoo]

Hi khalidyoo,

Here is a trick that you can make use of.
- After you get the data in Collection 1 from the Get Table
- Use 'Append Rows to Collection' action to place the same rows into another Collection 2.
- Next time when you do a Get Table again the new data will be overwritten into Collection 1 and appended to the bottom of Collection 2.
At the end of 7 tables, Collection 2 will be your final collection.

thank you it works x)

 

[khalidyoo]

Hi
khalidyoo,

VJR came up with the Best Approach you can go ahead and implement this logic you will get the results as per your requirement.

thanks

 

[KBP]

Hi everyone,

May I ask about the action "Get table".
Is this an action under BO MS Excel VBO, as I am currently not able to see this action?
I am using Blue Prism version 5.

Thanks for any answers.

 

[Sukesh Kumaru]

Hi everyone,

May I ask about the action "Get table".
Is this an action under BO MS Excel VBO, as I am currently not able to see this action?
I am using Blue Prism version 5.

Thanks for any answers.

Hi KBP,
Hi,
Its very simple and easy to get data from a table. All you need to do is spy just one element from the entire table. Then use the reader stage in blue prism and in data drop down select "Get Table".
I am attaching screenshots for your help.

 

[KBP]

Hi KBP,
Hi,
Its very simple and easy to get data from a table. All you need to do is spy just one element from the entire table. Then use the reader stage in blue prism and in data drop down select "Get Table".
I am attaching screenshots for your help.

Would you still recommend this approach even though the table is in excel?

Thank you so much for your reply.

 

[Sukesh Kumaru]

Would you still recommend this approach even though the table is in excel?

Thank you so much for your reply.

Hi KBP,
If that table is in excel then use action as "Get worksheet as Collection".

 

[KBP]

Hi Sukesh

Thank you so much for your replies.
I am new to the forum, so my apologises for the unclear question I have asked.
I have attached my current process. What I would like is to change the static cell reference (stored in the data item) and replace those with the table name "Grades" to make the process more dynamic. So when the table is moved around within the worksheet, I will still be able to get it into a collection calling only the table name.

Do you know if this is possible in Blue Prism?

Thank you!

 

[Sukesh Kumaru]

Hi,
If you want the table name "Grades" as dynamically use Region mode to spy it use Read text with OCR and place that value in required data item.
If this suits your requirement then go ahead.
otherwise reach me out.

 

[KBP]

I am not sure if I understand this correctly....but the table name will be constant and I would like to use this name to call the underlying table and write it into the collection.

Basically I would like to do it in the same way as I showed in my previous attachment, but only replace the "Start Cell" and "End Cell" with a new data item containing the table name "Grades".

Is there any easy fix to this?

Thank you very much for taking the time to answer my questions.

Regards
KBP

 

[VJR]

Hi KBP,

Below is the change you will need to do to get the Table into collection wherever it is moved.

- Make a duplicate copy of the 'Get Worksheet as Collection' tab and rename it as 'Get Worksheet Table as Collection'
- Comment or Delete these 2 lines from the Code stage
'ws.UsedRange.Select()
'ws.UsedRange.Copy()

- Replace them with the below lines
ws.Range(TableNameWithHeaders).Select()
ws.Range(TableNameWithHeaders).Copy()

- Save this new Object, Reset and Refresh.

- From the Process (instead of start and end cell) you will now be passing a parameter as "MyTable[#All]"
where MyTable is the name of the table and #All is to copy along with Headers of that table so that the collection column names will be the same as the Headers

- Accept this new parameter in the Start stage of the new Object that you just created ('Get Worksheet Table as Collection'). Just like other parameters pass this parameter to the Code stage by the name TableNameWithHeaders as Text datatype because we are using this name in the code stage.

This change is now a customised one and will work for any table name when you pass it from the process in the format as "TableName[#All]".

Input 1:


Output 1:


After changing the location of the table and also after deleting some rows of that table-
Input 2: Table shifted to Column I and rows deleted


Output2:


The instructions above are long but the change is actually a small one. Let me know if you face any issues.

 

[KBP]

Thanks for the reply VJ

I think this should solve my problem, but unfortunately I am not that familiar with coding, so I got some error message when I tried your code, are you able to understand what I am doing wrong?

Thank you very much for your reply.

 

[VJR]

Thanks for the reply VJ

I think this should solve my problem, but unfortunately I am not that familiar with coding, so I got some error message when I tried your code, are you able to understand what I am doing wrong?

Thank you very much for your reply.

It appears that you copy pasted the code without implementing the below instructions.

- Accept this new parameter in the Start stage of the new Object that you just created ('Get Worksheet Table as Collection'). Just like other parameters pass this parameter to the Code stage by the name TableNameWithHeaders as Text datatype because we are using this name in the code stage.

Adding a parameter is unrelated to Coding and you can just take a look at other parameters passed to the Start stage and the Code stage and likewise pass TableNameWithHeaders.

 

[KBP]

I am sorry for my late reply VJR, but yes I did forget to implement all the needed steps.
Thank you so much for your help and patience!
I works perfectly now.

BR
KBP

 

[yeturirpa]

Hi All,
Can someone help.. We need to read this table data into collection(Screenshot 1). while trying to get the data with Read stage. its throwing an exception "Internal : Invalid collection data returned from action - are field names duplicated?"(Screenshot 2). the fields names are not the same.

The same has code has worked in win7.

We tried with all spy modes but still i didn't find a way to achieve this function.