Parsing Excel File to Create records in Dynamics 365

Hello,

I am trying to parse an excel file in SharePoint which has the first row of headers and the following rows with data. I was thinking i should construct a JSON object out of the data so that it is easier to create the required records in Dynamics.

I followed a blog and was able to extract Excel data and save it to two array variables. However i am not able to loop through the data array and the then within that loop the header array to construct the JSON object.

Below is the Excel data:

This is the JSON object i am trying to build based on the data in the excel:

[
 {
 "Image Name": "GLOBUS_000556_000115_0001.tif",
 "Survey_Reference": "ALS0219A",
 "StreetAdress": "167 Carmichael Road",
 "City": "TAURAMGA",
 "StateProvinceCounty": "",
 "Postcode": "3110",
 "E_mail": "fred.chard@nu-abe.co.nz",
 "PhoneNumber": "",
 "CountryofResidence": "New Zealand",
 "CountryofResidence_OtherComments": "",
 "SEX": "",
 "MaritalStatus_Married": "",
 "DOB": "20/01/1983",
 "CheckHereIfYouAreMemberOfUniOrAssociationTravelGroup": "",
 "CheckHereIfYouAreBookedThisCruiseWithDeltaOrContinentalVacations": "",
 "UK RESIDENTS ONLY": "",
 "ItineraryCode": "WGG",
 "DepartureCode": "0428",
 "Invoice Number": "",
 "Country": "",
 "Title": "Mr",
 "FirstName": "Chard",
 "LastName": "Alfred",
 "Q1": "9",
 "Q5": "",
 "Q11": "Nile/Egypt,Rhine,Danube,Volga/Russia",
 "Q12": "Fewer than 6 months",
 "Q13": "Central Europe/Eastern Europe,Germany/Switzerland/Austria",
 "Q14": "River & Small Ship Cruising,Small Group Tours"
 },
 {
 "Image Name": "GLOBUS_000556_000115_0001.tif",
 "Survey_Reference": "ALS0219A",
 "StreetAdress": "167 Carmichael Road",
 "City": "TAURAMGA",
 "StateProvinceCounty": "",
 "Postcode": "3110",
 "E_mail": "fred.chard@nu-abe.co.nz",
 "PhoneNumber": "",
 "CountryofResidence": "New Zealand",
 "CountryofResidence_OtherComments": "",
 "SEX": "",
 "MaritalStatus_Married": "",
 "DOB": "20/01/1943",
 "CheckHereIfYouAreMemberOfUniOrAssociationTravelGroup": "",
 "CheckHereIfYouAreBookedThisCruiseWithDeltaOrContinentalVacations": "",
 "UK RESIDENTS ONLY": "",
 "ItineraryCode": "WGG",
 "DepartureCode": "0428",
 "Invoice Number": "",
 "Country": "",
 "Title": "Mr",
 "FirstName": "Bard",
 "LastName": "Alfred",
 "Q1": "9",
 "Q5": "",
 "Q11": "Nile/Egypt,Rhine,Danube,Volga/Russia",
 "Q12": "Fewer than 6 months",
 "Q13": "Central Europe/Eastern Europe,Germany/Switzerland/Austria",
 "Q14": "River & Small Ship Cruising,Small Group Tours"
 }
]

Below is the Flow i have build so far:

Excel_Import_Data_Flow 1.PNG

Excel_Import_Data_Flow 2.PNG

Excel_Import_Data_Flow 3.PNG

I was trying to loop through the Data Array initially and within that loop, loop through the header data array to build the JSON object. However i am unable to build the single item within the second loop. Any suggestions how i could achieve this? Or is there a simple way to loop through the excel data so that based on the header column, i can create a record in Dynamics and set the corresponding value. Thanks in advance.

Regards,

Nithin

Quick Links

Leaderboard > Power Automate