Reputation: 1849
I want to convert this Excel file which contains two tables in a single worksheet
{
parent:
{
"P1":"x1",
"P2":"y1",
"P3":"z1"
}
children: [
{"C1":"a1", "C2":"b1", "C3":"c1", "C4":"d1"},
{"C1":"a2", "C2":"b2", "C3":"c2", "C4":"d2"},
...
]
}
And then post the JSON to a REST endpoint.
How to perform the mapping and posting to REST service?
Also, it appears that I need to sink the JSON to a physical JSON file before I can post as a payload to REST service - is this physical sink step necessary or can it be held in memory?
I cannot use Lookup activity to read in the Excel file because it is limited to 5,000 rows and 4MB.
Upvotes: 1
Views: 1308
Reputation: 479
I managed to do it in ADF, the solution is a bit long, but you can use azure functions to do it programmatically.
Here is a quick demo that i built:
the main idea is to split data, add headers as requested and then re-join data and add relevant keys like parents and children.
ADF:
![enter image description here
Activities:
AddSurrogateKey:
(it's the same for parents data flow just change the name of incoming stream as shown in dataflow above)
FilterFirstRow:
PartentsJson:
Here i added subcolumn in Expression Builder and sent column name as value,this will build the parents json.
ChildrenArray: Again in a derived column, added column with a name "children" and in Expression Builder i added relevant columns.
Aggregate:
the purpose of this activity is to aggregate children Json's and build the array, without it you will not get an array. the aggregation function is collect().
Join Activity:
Here i added an outer join to join the parents json and the children array.
Upvotes: 1