How to trim text based on conditions

[mgartner]

Hi there,

We are trying to trim data that is within an API call response and it looks something like this:

{“id”:4774905708865412,“rowNumber”:398,“siblingId”:271306081494916,“expanded”:true,“createdAt”:“2019-05-06T15:37:29Z”,“modifiedAt”:“2019-05-06T15:43:44Z”,“cells”:[{“columnId”:4141557109548932,“value”:398.0,“displayValue”:“398”},{“columnId”:3365712728221572},{“columnId”:3953178354968452,“value”:“Bob Vance”,“displayValue”:“Bob Vance”},{“columnId”:8456777982338948,“value”:“2019-05-06”},{“columnId”:392049171163012},{“columnId”:4895648798533508,“value”:true},{“columnId”:5925375797684100},{“columnId”:5362425844262788,“value”:150.0}]},

{“id”:6718386194933636,“rowNumber”:399,“siblingId”:4774905708865412,“expanded”:true,“createdAt”:“2019-05-06T20:08:50Z”,“modifiedAt”:“2019-05-06T20:57:46Z”,“cells”:[{“columnId”:4141557109548932,“value”:399.0,“displayValue”:“399”},{“columnId”:3365712728221572},{“columnId”:3953178354968452,“value”:“Bob Vance (Test)”,“displayValue”:“Bob Vance (Test)”},{“columnId”:8456777982338948,“value”:“2019-05-06”},{“columnId”:392049171163012,“value”:true},{“columnId”:4895648798533508,“value”:true},{“columnId”:5925375797684100},{“columnId”:5362425844262788},{“columnId”:3392101007288196,“value”:50.0,“displayValue”:“50”}]},

Would there be a solution to trim the text between {“id” and }]},where a specific columnID has a specific displayValue?

Any help would be greatly appreciated

[loginerror (Maciej Kuzmicz)]

Hi @mgartner

Could you give an example of the desired output?

[mgartner]

The desired output will be extracting the portion of row information such as the text below where columnId”:4141557109548932 has a “displayValue” value which is the same value in another variable that we are passing along the bot.

{“id”:6718386194933636,“rowNumber”:399,“siblingId”:4774905708865412,“expanded”:true,“createdAt”:“2019-05-06T20:08:50Z”,“modifiedAt”:“2019-05-06T20:57:46Z”,“cells”:[{“columnId”:4141557109548932,“value”:399.0,“displayValue”:“399”},{“columnId”:3365712728221572},{“columnId”:3953178354968452,“value”:“Bob Vance (Test)”,“displayValue”:“Bob Vance (Test)”},{“columnId”:8456777982338948,“value”:“2019-05-06”},{“columnId”:392049171163012,“value”:true},{“columnId”:4895648798533508,“value”:true},{“columnId”:5925375797684100},{“columnId”:5362425844262788},{“columnId”:3392101007288196,“value”:50.0,“displayValue”:“50”}]},

[apurvalost (Apurva Thakkar)]

Try playing something like this using assign activity
info= info.Split({":",“Client”},StringSplitOptions.RemoveEmptyEntries)

Client ID: DB83622
Client Name: Wilson Gau
Client Country: Italy

I used this for getting id , name and country from this type of data.

[Filip_C]

Hi @mgartner,

Since its basically JSON string it would be best to deserialize it with a Deserialize JSON activity from UiPath.Web.Activities package (available in package manager)
you’ll just need to replace all " with ’ before trying to process it

After that you’ll be able to access any object from within the JSON Object

and output of that sequence is:

Hope that helps
Best Regards,
Filip

[mgartner]

This is very helpful, thank you!