How to use azure data factory to build json structure using data from excel sheets ?

Question

Hi,

I am new to azure data factory and have a requirement to build json structure using data from excel. An excel document has multiple sheets by names corresponding to entity/modules names like, Device modules, service items, manufacturers, service categories, service types etc. Each sheet has data present in column and row structure, where column names are not always same as that of keys from json structure, which I wanted to build.
So, agenda is to read data present from each sheet in specific order and build json structure.
Below is the json structure which I wanted to build/output and I am attaching images of excel file including multiple sheets as source-
{
"ServiceOffering": {
"Name": "Help Desk as a Service",
"Description": "Add high-quality help desk services to monitoring, managed network, and other service offerings. The team is based out of Louisville, KY, and Grand Rapids, MI, and they maintain an industry-leading Net Promoter Score, which is a key factor in customer satisfaction and retention.",
"OfferingSKU": null,
"ValueAddServices": [
{
"Name": "Network Assessment",
"Description": null,
"ValueAddServiceTemplateName": "Network Assessment",
"ValueAddServicecost": 0,
"ValueAddServiceAvgMargin": 0,
"ValueAddServicePrice": 0
}
],
"ServiceProviders": [
{
"Name": "Trace3",
"Description": null,
"MinimumMonthlySpend": null,
"ServiceLocations": [
{
"Name": "Domestic",
"Description": "Default as domestic- US",
"Location": "US",
"Country": "US",
"LocationbasedCost": 0,
"AgreementTerms": [
{
"Name": "1 Year",
"Description": null,
"Yearly": true,
"Monthly": false,
"Weekly": false,
"Daily": false,
"Term": 1,
"AgreementTermDiscount": 0,
"ServiceTypes": [
{
"Name": "BASIC",
"Description": null,
"ServiceItems": [
{
"Name": "Level 1 Remote End User Support Remote Monthly Ticket Package",
"Description": null,
"ServiceCategory": {
"Name": null,
"Description": null
},
"Manufacturer": {
"Name": null,
"AbbrName": null,
"Description": null
},
"Model": null,
"ServiceItemType": {
"Name": "Service",
"Description": null
},
"Mfgdate": null,
"Cpu": null,
"Memory": null,
"NetworkCards": 0,
"OS": null,
"Length": 0,
"Width": 0,
"Height": 0,
"Color": null,
"NoOfGasBurners": 0,
"ManufacturerSKU": null,
"Software": null,
"Price": 35,
"Cost": 0,
"Margin": 35,
"Quantity": 1
}
]
}
]
}
]
}
]
}
],
"MonthlySpendRanges": [
{
"SpendRangeName": null,
"RangeType": null,
"Min": 0,
"Max": 0,
"FeePercent": 0,
"FixedDollarAmount": 0
}
],
"OtherRecurringServiceCost": [
{
"Name": "Base Service Cost Fee",
"Description": null,
"BaseServicePrice": 0,
"BaseServiceCost": 0,
"BaseServiceCostMargin": 0
}
],
"Features": [
{
"Name": "Feature1",
"Description": "'Tech industry leading 84%+ Net Promoter Score which drives customer satisfaction and retention."
}
],
"MarketingContentURL": null,
"WebFlowOfferingImageFile": null
}
}

Can someone help me to know the direction ?
Thanks in advance.

Answer 1

Hi @@Vinayak J ,

Thanks for posting query in Microsoft Q&A Platform.

To loop through sheet names in excel you can check below link and follow steps.
https://learn.microsoft.com/en-us/answers/questions/986785/how-to-get-excel-sheet-names-dyanamically-in-azuur.html

In ADF you cannot directly read sheet name, You need consider writing code for that. Please check below link as example reference.
https://www.youtube.com/watch?v=giJhP6LiOgI

You consider using dataflows or copy activity to read from excel. Inside Copy activity, you can leverage mappings tab to map columns to Sink. If you want to rename any column in dataflows, then consider using derived column transformation. Either Copy activity or Dataflow, in both cases in Sink dataset you can consider using format as JSON, so that data can be loaded as json file.

Please let us know how it goes. Thank you.