How to extract the data from API convert to datatable

Help Studio
, ,
1

image
image476×514 14.6 KB

how to extract the data from API convert to datatable …

this is for log message output…
{
“result”: [
{
“headrow”: {
“rowno”: {
“hide”: “false”
},
“axrowtype”: {
“hide”: “true”,
“width”: “80”,
“dec”: “0”,
“type”: “c”
},
“axp__font”: {
“axp__font”: “axp__fontdetails”,
“hide”: “true”,
“width”: “80”,
“dec”: “0”,
“type”: “c”
},
“new_empno”: {
“new_empno”: “new_empno”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “1”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“first_name”: {
“first_name”: “first_name”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “2”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“branch_shrt”: {
“branch_shrt”: “branch_shrt”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “3”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“department_name”: {
“department_name”: “department_name”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “4”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“division_name”: {
“division_name”: “division_name”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “5”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“cost_center”: {
“cost_center”: “cost_center”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “6”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“bcc”: {
“bcc”: “bcc”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “7”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“createdon”: {
“createdon”: “createdon”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “8”,
“runningtotal”: “False”,
“type”: “d”,
“hide”: “false”
},
“modifiedon”: {
“modifiedon”: “modifiedon”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “9”,
“runningtotal”: “False”,
“type”: “d”,
“hide”: “false”
},
“last_name”: {
“last_name”: “last_name”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “10”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“email”: {
“email”: “email”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “11”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“mobile_no”: {
“mobile_no”: “mobile_no”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “12”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“doj”: {
“doj”: “doj”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “13”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“reporting_to”: {
“reporting_to”: “reporting_to”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “14”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“designation_name”: {
“designation_name”: “designation_name”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “15”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“nurse_regno1”: {
“nurse_regno1”: “nurse_regno1”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “16”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“nurse_valid_date”: {
“nurse_valid_date”: “nurse_valid_date”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “17”,
“runningtotal”: “False”,
“type”: “d”,
“hide”: “false”
},
“subdivision”: {
“subdivision”: “subdivision”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “18”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“status”: {
“status”: “status”,
“width”: “80”,
“dec”: “0”,
“align”: “Left”,
“ordno”: “19”,
“runningtotal”: “False”,
“type”: “c”,
“hide”: “false”
},
“reccount”: “10208”,
“totalrows”: “10208”,
“datarows”: “10208”
},
“row”: [
{
“rowno”: “1”,
“new_empno”: “100001”,
“first_name”: “Samyiyya”,
“branch_shrt”: “KCN”,
“department_name”: “Facility Services”,
“division_name”: “Engineering”,
“cost_center”: “Unit”,
},
{
“rowno”: “2”,
“new_empno”: “100002”,
“first_name”: “Abdulsalam”,
“branch_shrt”: “KCN”,
“department_name”: “Facility Services”,
“division_name”: “Facility Services”,
“cost_center”: “Unit”,
},
{
“rowno”: “3”,
“new_empno”: “100005”,
“first_name”: “Chitra”,
“branch_shrt”: “KCN”,
“department_name”: “Clinical Nursing”,
“division_name”: “OPD”,
“cost_center”: “Unit”,
},
{
“rowno”: “4”,
“new_empno”: “100006”,
“first_name”: “Gunaseeli”,
“branch_shrt”: “KTN”,
“department_name”: “Operations”,
“division_name”: “OPD”,
“cost_center”: “Unit”,
},
{
“rowno”: “5”,
“new_empno”: “100007”,
“first_name”: “Dharmarajan”,
“branch_shrt”: “KTN”,
“department_name”: “Facility Services”,
“division_name”: “Facility Services”,
“cost_center”: “Unit”,
},
{
“rowno”: “6”,
“new_empno”: “100008”,
“first_name”: “Sagaya Selvi”,
“branch_shrt”: “KTN”,
“department_name”: “Clinical Nursing”,
“division_name”: “Nursing”,
“cost_center”: “Unit”,…

2

Hi @domsmgtmeet22

Check the below steps for better understanding,
→ After HTTP Request activity use the Deserialize JSON activity.
Input: Output from HTTP Request
Output: jsonObject
→ Use the Assign activity and use the below expression,

- Assign -> jsonArrayString = jsonObject("result")(0)("row").ToString()

→ Then use the Deserialize JSON Array activity
Input: jsonArrayString
Output: jsonArray
→ Use the Build Data Table activity to build the datatable structure by adding columns, create a variable in the output of Build datatable, let’s say the variable name as DT.
→ Define columns as per your JSON structure, e.g., “rowno”, “new_empno”, “first_name”, etc.
→ For Each activity to iterate the each item in jsonArray
→ Inside for each insert the Add Data Row activity to add the rows to the datatatable variable DT.

ArrayRow: {item("rowno").ToString(), item("new_empno").ToString(), item("first_name").ToString(), item("branch_shrt").ToString(), item("department_name").ToString(), item("division_name").ToString(), item("cost_center").ToString()}

→ After for each activity insert the Write range workbook activity to write the DT to the excel file.

Hope it helps!!

3

Hi @domsmgtmeet22

Can you provide the whole data in a text file and required output dt in excel. So,that we will help you out

Regards

4

i will share my API and parameters you will work then share output

1 Like
5

@domsmgtmeet22
Okay , Share it we will help you out

6

Hi @domsmgtmeet22

You can follow the above steps of process, or you can use the process in the below thread,

Hope you understand!!

7

@domsmgtmeet22

You want to extract only row as the datatable ?

If yes

Then after deserialize json use deserialize json array and select type argument as datatable as pass jo("result")(0)("row").ToString

Cheers