Converting excel data into Required json

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”
}
]
},

Hi @anshuman.pathak

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

Is it right?

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

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