How to convert JSON output to DataTable?

[Tana20]

Hi,

I am using Deserialize Json activity to parse the JSON received from an API,

JSON Format:

FileTest.json (1.7 KB)

‘Deserialize JSON’ activity output (jsonObject):

I’m trying to iterate each field by using “For Each”, assigning each field value to a variable…

… and finally, send each variable to the Data Table:

But the “For Each” activity fails,

Debugging, I found this in JsonObject…so I guess I’m using the wrong format to retrieve items in the “ForEach” activity…

Any idea to solve this?

[Yoichi]

Hi,

Perhaps, you need to create DataTable instance for OutPutData in advance using Build DataTable activity, for example. Can you try it?

Regards,

[Tana20]

Thanks, I already did it, I have updated the post with the screenshot… So that’s not the issue

[Yoichi]

Hi,

Can you share your json string as a file, if possible? It’s no problem even if dummy data.

Regards,

[Tana20]

FileTest.json (1.7 KB)

[Yoichi]

Hi,

Thank you for sharing your data. How about the following sample?

Sample20210126-1.zip (3.5 KB)

Regards,

[Tana20]

Thank you @Yoichi , It perfectly works with the file as Input,

I’m now testing with the API, the response is huge then the bot is taking a lot of time to process the records but at least it hasn’t failed…

I’m worried because in Production I need to process 160K records (29 fields each one), so maybe I will need to find other options to improve the performance

[Tana20]

@Yoichi it works! Thank you,

The bot was able to process 1000 records (29fields each record) in 15 minutes. I will investigate how to refactor your solution to process 160K, I just need it for the first Full Load, later I will use Deltas to update/process new records daily

[Yoichi]

Hi,

We might be able to achieve it using Regex, if there aren’t Name, Label and Value elsewhere and its order is always same. (Probably it’s faster than using deserialize json and loop).

Can you try the following?

Sequence.xaml (8.3 KB)

Regards,

[Tana20]

Thanks @Yoichi With Regex was almost the same, 14 minutes for 1000 records.

[Yoichi]

Hi,

Thank you for trying. For Each and AddDataRow might be slow.
I’ve rewrite the above with Invoke Code as the following. Can you try this?

Sequence.xaml (10.0 KB)

Note: Can you try just “run file” (not debug) If you ran debug mode, because it’s very slow.

Regards,

[Tana20]

Hi @Yoichi I have just tested it, and it was completed in 4 minutes! Thank you so much for all your help!