How to create json body to pass in HTTP Request(API) using each row data from my excel file

[Mandava_Naresh]

Hi all,

Can someone help me creating json body from excel data and put in the HTTP Request(API) Body.

…

I have excel with 2000 rows, I have to read each row values and put in json body like below …

below example is for 2 rows

[

{

“Part”: “1”,
“Direction”: “N”,
“DEODE”: “31101687”,
“DEFERENCE”: “NS_0001”,
“OPERATDE”: 10,
“POSITION”: “1”,
“TARI_ODE”: “0202”,
“GOODS_NAME”: “Goods Name1”,

},

{
“Part”: “1”,
“Direction”: “N”,
“DEODE”: “5676888”,
“DEFERENCE”: “N666001”,
“OPERATDE”: 190,
“POSITION”: “1445”,
“TARI_ODE”: “034302”,
“GOODS_NAME”: “GoHName1”,
}
]

→ My goal is to achieve reading data from excel and generate json body for each row where “Part” and “Direction” field is always same and not from excel …

→ Data that we need to read from excel is “DEODE”,“DEFERENCE”,“OPERATDE”, “POSITION”,“TARI_ODE”,“GOODS_NAME”,

Thank you,
Naresh

[sppal.c]

@Mandava_Naresh

I have excel with 2000 rows, I have to read each row values and put in json body like below …

below example is for 2 rows

[

{

“Part”: “1”,
“Direction”: “N”,
“DEODE”: “31101687”,
“DEFERENCE”: “NS_0001”,
“OPERATDE”: 10,
“POSITION”: “1”,
“TARI_ODE”: “0202”,
“GOODS_NAME”: “Goods Name1”,

},

{
“Part”: “1”,
“Direction”: “N”,
“DEODE”: “5676888”,
“DEFERENCE”: “N666001”,
“OPERATDE”: 190,
“POSITION”: “1445”,
“TARI_ODE”: “034302”,
“GOODS_NAME”: “GoHName1”,
}
]

→ My goal is to achieve reading data from excel and generate json body for each row where “Part” and “Direction” field is always same and not from excel …

→ Data that we need to read from excel is “DEODE”,“DEFERENCE”,“OPERATDE”, “POSITION”,“TARI_ODE”,“GOODS_NAME”,

1. Read Datatable from excel
2. Build jsonArray
   jsonArray = (From row In dtData.AsEnumerable()Select New Dictionary(Of String, Object) From {{"Part", "1"},{"Direction", "N"}, {"DEODE", row("DEODE")}, {"DEFERENCE", row("DEFERENCE")},{"OPERATDE", row("OPERATDE")}, {"POSITION", row("POSITION")}, {"TARI_ODE", row("TARI_ODE")}, {"GOODS_NAME", row("GOODS_NAME")}}).ToList()
3. Convert jsonArray to jsonString
   jsonString = JsonConvert.SerializeObject(jsonArray, Formatting.Indented)

[Parvathy (PS Parvathy)]

Hi @Mandava_Naresh

=> Use Read Range Workbook to read the excel to data table
Output → dt

=> Use the below syntax in Assign activity:

jsonArray = New JArray(
    From row In dt.AsEnumerable()
    Select New JObject(
        New JProperty("Part", "1"),
        New JProperty("Direction", "N"),
        New JProperty("DEODE", row("DEODE").ToString()),
        New JProperty("DEFERENCE", row("DEFERENCE").ToString()),
        New JProperty("OPERATDE",(row("OPERATDE"))),
        New JProperty("POSITION", row("POSITION").ToString()),
        New JProperty("TARI_ODE", row("TARI_ODE").ToString()),
        New JProperty("GOODS_NAME", row("GOODS_NAME").ToString())
    )
)

Note: jsonArray is of DataType Newtonsoft.Json.Linq.JArray

=> Use the below syntax to convert to jsonString
jsonString = jsonArray.ToString()

Hope it helps!!

[Anil_G (Anil Gorthi)]

@Mandava_Naresh

You can directly convert full datatable to json string using Newtonsoft.Json.JsonConvert.SerializeObject(dt)

Hope this helps

cheers