How to convert api json into excel i.e datatable

Help
#1

Sample :

{
“FileName”: {
“0”: “10302-S100085_Invoice (1).pdf”,
“1”: “10302-S100085_Invoice (1).pdf”,
“2”: “10302-S100085_Invoice (1).pdf”,
“3”: “10302-S100085_Invoice (1).pdf”
},
“Invoice Date”: {
“0”: “11/3/2019”,
“1”: “11/3/2019”,
“2”: “11/3/2019”,
“3”: “11/3/2019”
},
“Invoice Number”: {
“0”: 10302,
“1”: 10302,
“2”: 10302,
“3”: 10302
},
“Line Item Details”: {
“0”: “1 04-128368-E2-3099”,
“1”: “10mm Round Earring Post W/7.6mm Round Rose Gold Color Agate Druzy W/ Bronze W/ IP Rose”,
“2”: “Gold Plated”,
“3”: “1 04-128368-E2-3099”
},
“Total amount”: {
“0”: "$5,697.73 ",
“1”: NaN,
“2”: NaN,
“3”: NaN
},
“Vendor Name”: {
“0”: "Aluma USA lnc ",
“1”: "Aluma USA lnc ",
“2”: "Aluma USA lnc ",
“3”: "Aluma USA lnc "
}
}

#2

You can use the Deserialize Json activity to parse the json received from the api. You can then add the data to a DataTable in whichever format you require and then use the Excel Write Range activity to write the data to Excel.

#3

image
image1366×768 65.4 KB

I am using HTTP request to read API Json then i used Deserialize JSON to parse the Json recieved from API then i used for each activity to obtain the data but this error pops up. What should i do ?

#4

Hi @Adhu_4,

The Type argument shoulb be of jobject but in the above Screenshot you have shared its Jproperty. Change the argument type to Jobject and it will work fine.

image
image1366×768 62.2 KB

#6

image
image1366×768 71.4 KB
How do i solve this error? I want to write the Jobject data into a excel sheet.

#7

Hi @Adhu_4,

You are DE serializing the json and storing in j_obj and then again why are you deserializing the j_Obj(“FileName”), that is the thing which is causing the issue. Have a look into it.

Regards,
Shiva Karthik

#8

can you tell me how to convert the jobject to datatable pls

#9

Hi @Adhu_4,

Can you share your json with me once. The json which you have mentioned above has some issue with I tried to validate it but it saying incorrect json.

Kindly share the json file I will work on it and share it with you.

Regards,
Shiva Karthik

#10

Api_txt.json (1.0 KB)

#11

Hi Shiva , can we read the Json by passing a number,

for eg: if my json is {
“Workstation_Printer”: {
“Drive”: {
“Status”: 1,
“Value”: "10.188.32.28:Canon_Secure_Blr_01 ",
“StatusDetails”: “Printer is in the same network”
}
},
“Workstation_ScreenLock”: {
“ScreenSaveTimeOut”: {
“Value”: “300”,
“Status”: 1
},
“ScreenSaverIsSecure”: {
“Value”: “1”,
“Status”: 1
},
“ScreenSaveActive”: {
“Value”: “1”,
“Status”: 1
}
},
“Workstation_PasswordPolicy”: {
“Computer role”: {
“Value”: “WORKSTATION”,
“Status”: 1
},
“Length of password history”: {
“Value”: “14”,
“Status”: 1
},
“Minimum password length”: {
“Value”: “8”,
“Status”: 1
},
“Lockout threshold”: {
“Value”: “6”,
“Status”: 1
},
“Lockout duration”: {
“Value”: “720”,
“Status”: 1
},
“Maximum password age”: {
“Value”: “90”,
“Status”: 1
},
“Force user logoff”: {
“Value”: “0”,
“Status”: 1
},
“Minimum password age”: {
“Value”: “1”,
“Status”: 1
},
“Lockout observation window”: {
“Value”: “30”,
“Status”: 1
}
},
“Workstation_Wifi”: {
“Drive”: {
“Value”: “Up”,
“Status”: 0
}
},

I want the bot to fetch Workstation printer when i pass the number 1. My json can be dynamic, i want to retrieve the values one by one by passing number