Extraction of Data from a JSON

Help
1

Hi Guys,

I use UiPath since around two weeks. With the integrated functions it works for me as user fine.
Now i have a new requirement and actually i have not a clue to solve it. May be somebody can give me some tips:

Situation:
I actually use UI Path to extract json Information from a Partner API and write it to a csv/text file (around 5mb). Works fine. I got now a request if it is possible to filter the Information since colleagues want to put that informations in a SQL database… It means to get the complete json Feedback and filterering for several 100 entrys (dynamic size) with around an dozen different informations (like Name, prize, Duration, and so one)

Idea:
I thought that i can build an loop to search the file for different key words / informations. But i did’t found an option for that.
Any tips?

Regards Timo

1 Like
2

Not really an answer but I have created an automation challenge here so stay tuned. I guess that is what you’re looking for.

3

hey @TimoC

Regards…!!

2 Likes
4

Thanks for the Input.
I will try to adapt that for my case,

Thanks!

regards
Timo

1 Like
5

Hi Guys,

I still have some Problems with the orignal Operation. The JSON part is now okay. But i have still Problems to write the extracted JSON informations to csv file.
The Export to a csv presume that the data Comes from a datatable. How can i Transfer data from the json Export to a datatable?

Regards
Timo

6

hi @TimoC

if you are able to extract json data if i will suppose kind of array you are getting then you can use loops with it according to datatype suitable and by using “add data row” datatable activity you can add data into a datatable and then you can write into an excel or csv.

Regards…!!

7

Hi Aksh1

Thanks for your Feedback.
My sequence actually Looks like:

  1. HTTP Request (get a id)
  2. HTTP Request with the ID ( get the json as string)
  3. Deserialize Json (string) as Json object
  4. For each Loop
  5. Write it to line as string

That is working so far (if i filter that everything will be exported). If i Change the filtering to dífferent types i got an error that there are different datatypes in the Array (string, int32 and so on). Think here is the first understanding Problem from me.

I tried to Change the step 3 to a json Array deserialization but than i receive an exeption that the json string is not an array (that isn’t true, that is an JasonArray). So i changed it deserialize json and deserialze it. Is that the reason why i can not Transfer the data to a datatable?

Regards
TimoMainJsonArray.xaml (6.6 KB)
MainJsonObject.xaml (6.5 KB)

2 Likes
8

I exported a part of my test xaml files. May be that is simpler for the understanding…

Regards
Timo

9

HI,
I deserialized my JSON file.But I’m getting the following error. I don’ know why am I getting this…?

“Error reading JObject from JsonReader. Current JsonReader item is not an object: StartArray. Path ‘’, line 1, position 1.” Can anyone help me to solve this.

1 Like
10

download the web plugin
image

11

image
image1573×564 146 KB

2 Likes
12

@Senthilkumar55
JSON array must start with square brackets"[" and should end with “]”, your JSON array might be wrapped up in another JSON object which is not an array, for eg.

This is correct:
[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]

This is incorrect:
{“result”:[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]}

13

Hi @sarthakj,

This also valid JSON format only.

Regards,
Arivu

14

Yes, I know but this is not a JSON array, that’s what i wanted to say.

15

Don’t use deserialise,
Create new variable and parse string
JObject works when we use variable name “jobject” and variable type jobject with Default value to “new jobject”

1 Like
16

Hi
Pls follow below json parsing tutorial

3 Likes
17

Here is a detailed article on that :slight_smile:

5 Likes
18

Can you please tell me how to convert
{“result”:[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]}

to
[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]

I am also facing similar issue, I want to remove {“result”: part. Can someone please guide me on this.

19

Hi @anto.santhosh ,

Firstly the valid Json format should not contain (“,”) left double and right double quotations.

  • Replace those left and right double quotations with (") normal double quotation

  • Then use deserialise json activity and pass the correct Json text as input and argument as Newtonsoft.Json.Linq.JObject

  • Now you are having output in JArray format and you can get “result” value

Refer the attached workflow reference.
Handling_JsonText.zip (1.7 KB)

Thanks & Regards,
Likith Rajesh.