Export Json data to datatable/csv/excel file (Thomson Reuters - Transaction Report)

Help Activities
, , ,
1

Hello Guys,

I need help converting json data in the attached file to data table. I can achieve this using a For loop which will be time consuming because there will be 15K+ rows of bulk data for each transaction. In the attached file array of “items” are to be consider as the data which I want to add in the data table and later export it as csv/excel file.

TestJSON.json (1.4 KB)

I did try below custom activity but unable to get desired result.

Using custom activity I am getting this result. I am providing result in excel file as I cannot attach csv file.
TEST_Result.xls (28 KB)

Please let me know how I can achieve below expected result.
Expected_Result.xls (27 KB)

Thanks,
Navneet
Happy Automation :slight_smile:

2

Hi,

How about the following? I think the above expected result is not from TestJson.json, right?

image
image1225×817 40.2 KB 

strTSV = String.Join(vbcrlf,jitem.Select(Function(j) String.Join(chr(9),j.Select(Function(j2) j2.Value(Of String)))))

Sample20230322-2L.zip (3.2 KB)

Regards,

3

As a long oneliner after deserializing the JSON with

grafik

we can do:
grafik

JArray.FromObject(myJObject("items").Select(Function (a) new JObject(a.Select(Function (x,i) new JProperty("Col"&i.toString, x))))).toObject(Of DataTable)

And Decompose it to Query Syntax
or

Hybrid Approach with a for each

grafik
grafik668×696 40.3 KB

For each:
grafik

tmpJO | DataType: JObject:

new JObject(item.Select(Function (x,i) new JProperty("Col"&i.toString, x)))

Merge DataTable
grafik

1 Like
4

@ppr Peter

Your one liner solution worked fantastic :+1:
Would you provide me the query which should get column names from json itself?
Also, I want to know what does “Decompose it to query syntax” means and why to do that?
Attached updated json file where array of Fields are consider as column names.
TestJSON - Copy.json (2.6 KB)

Thanks,
Navneet
Happy Automation :slight_smile:

5

@NavneetPanpaliya
could work with this:

grafik
grafik947×232 12 KB

JArray.FromObject(myJObject("items").Select(Function (a) new JObject(a.Select(Function (x,i) new JProperty(myJObject("_meta")("fields")(i).toString, x))))).toObject(Of DataTable)

However for learning purpose we would recommend to explore both approaches.

2 Likes
6

@ppr

I implement workaround to get column names but thanks a lot for your quick help and the new query will be useful too.

Regards,
Navneet
Happy Automation :slight_smile:

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.