To Convert a csv file to JSON format and use it in Dataverse or PowerApps for further process
High level resolution steps
We use Power Automate to ease the process of converting a plain CSV file into JSON format
Detailed resolution steps
Step 1: In the Initial Step, use the trigger as you wish and try getting the file from OneDrive or any other format, such as “when a new email arrives” or something… Just make sure the CSV file is in place to be read. I have taken the file from OneDrive as shown below:
Step 2: Compose the file content and store it in Compose Action as shown below:
Step 3: Then we split them into new lines with a Power FX formula in another compose action as shown below:
Formula – split(outputs(‘Compose_CSV_Data’),decodeUriComponent(‘%0D%0A’))
Step 4: Then, get the output in such a way that we’re trying to split the line one after another using Power FX function: skip(outputs(‘splitNewLine’),1)
Step 5: Then, we split the lines or column headers by comma using another compose action with a Power Fx
Formula: @{split(item(), ‘,’)} as shown below:
Step 6: Then compose a JSON that looks like a JSON format and use the same column headers as in your CSV
file and for each header, try to get the output off of the CSV file with a formula:
outputs(‘splitByComma’)?[0]
outputs(‘splitByComma’)?[1]
…….……..Goes on with each column header as shown below:
When you peek the code, it’ll look like this:
Step 7: Then get the complete output into another compose action and store it there as shown below:
Formula – @{outputs(‘JSON’)}
Step 8: Parse the JSON in another step to make into a usable format in case you want to add the csv data into a
Dataverse table that will be available as a dynamic content as shown:
Generate the sample from the Complete JSON step, copy the output of the JSON, and paste it into generate from sample. Hereon, you can add the JSON dynamic content into further steps.
Output
Converted CSV into JSON Format as shown below:
