Converting excel data into Required json

Help Activities
, ,
1

Hi Folks,

I am facing issue in converting a excel file in the form of Json. Attached below is my excel file . In the excel the columns marked in yellow should be my json header . The one marked in green . I need to have a distinct value of that in json (like in the form of array and rest all should be the line item under that). Till Now, when i am using datatable to to json activity i am getting a different output.InputFile.xlsx (15.4 KB)

Can anyone let me know how to do this with a sample solution? the expected json should look something like this
Response": [
{
“RowID”: “some value”,
“PayerID”: “some value”,
“BillingNPI”: “some value”,
“LastNameInput”: “some value”,
“FirstNameInput”: “some value”,
“LastNamePayer”: “some value”,
“FirstNamePayer”: “some value”,
“PatientControlNum”: “some value”,
“InsuranceOrMemberID”: “some value”,
“StatusDetails”: “Claim 1: The Claim for $0.00”,
“Claim”: [
{
“ClaimNo”: 1,
“AdjudicationDate”: “”,
“StatusEffDate”: “03-19-1998”,
“EFTorCheckDate”: “”,
“EFTorCheckNumber”: “”,
“ClaimNumber”: “some value”,
“ClaimPayment”: “”,
“ClaimCategoryCodes”: “some value”,
“ClaimStatusCodes”: “some value”
}
]
},

2

Hi @anshuman.pathak

under “Claim”:, there should data for more than one claim if there are same claim numbers?

Is it right?

3

under claim , i will be having my line item for eg: in the excel i am having CL01309177 as claim number with many rows. so under claim , there should be one more tree for CL01309177 in which all the line item prsent for this claim number should be present. and similary for others as well

4

Let me know if any suggestions as i am stuck in this. i am able to extract the json but everything is coming in single json object