Json from a cell in Excel to DataTable

[kmaddikatla]

Hi There,
Hope all are doing well!

I do have the below data in a cell of an Excel sheet in the Json Format,I need to add the below data to a DT
Please find the below input:
{“DynamicProperties”:{“Code”:“KKR”,“AccountNumber”:“123456789”,“Name”:“kmaddikatla”,“DocumentPath”:“Kmaddikatla\pdf\.pdf”,“PostingDate”:“03/30/2021”,“Amount”:“100”,“CompanyUserId”:“1000”}}

Expected output::

Thanks in Advance,
Happy Automation ,
@Anil_G
@ppr
@Yoichi
@ushu
@vishal.kp
@supermanPunch
@postwick
@Nithya1

Regards ,
@kmaddikatla

[Anil_G (Anil Gorthi)]

@kmaddikatla

Use deserialize json activity and deserialize the string and get the output say Jobj

now use Jobj("DynamicProperties")("Code").ToString to get the code…replace code with other values to get others

you can use add data row with all these outputs and use in arrayrow {Jobj("DynamicProperties")("Code").ToString,Jobj("DynamicProperties")("AccountNumber").ToString and so on}

Another way use two deserialize one after the other like below that will give dt directly

Code:
First

Second

First - select type argument as Jobject
Second type argument as Datatable

output:

cheers

[postwick (Paul)]

Replace \ with \\ (to escape it) then use Deserialize JSON to get it into a JSON object. From there you can reference the individual values to use in an Add Data Row activity.

[ppr (Peter)]

when JSON String properly escaped or passed we can deserialize and transform:

[kmaddikatla]

Thank you For the response @Anil_G ,
I have tried this and It is working fine,
Actually I’m Iterating this in for each row data table as the input is from the DT,
while I was writing in a Excel I’m able to write only one row in it .

so, should I have to use a build DT and use Add data row ?

Thank you In Advance

[Anil_G (Anil Gorthi)]

@kmaddikatla

Use build datatable outside the loop and in the loop use add data row

Cheers

[Shiva_Nikhil]

Newtonsoft.json.jsonconvert.Deseralizeobject(of Datatable)(your variable of json.tostring) To write into excel the above function create a data into datatable

[postwick (Paul)]

They don’t have all the rows in one JSON. It’s a row-by-row loop so they need to add each JSON value to a datatable each iteration through the loop, then write the datatable to Excel at the end.

[Shiva_Nikhil]

Ok no problem i thought input has multiple json array
.

[kmaddikatla]

I used merge DT in the loop and created a DT before the loop. It is working as expected…

Thanks @Anil_G
Happy automation