How to Convert CSV to JSON in Power Automate
Converting CSV data sets into JavaScript Object Notation (JSON) format makes that data much easier to use in Power Automate.
The same logic applies to this conversion as it does to an Excel to JSON conversion; once we’ve converted our CSV dataset to JSON, we can parse that JSON into a schema and utilize each row of our data as an array of dynamic content in our Power Automate flow.
Walkthrough: Convert CSV to JSON in Power Automate
In this article, we’ll walkthrough a quick example flow that converts a CSV file to JSON (using the Cloudmersive Document Conversion connector) and parses that JSON into a custom schema. We’ll then loop the resulting data through a subsequent action in our flow.
We’ll build a manually triggered, instant cloud flow in this context so we can have full control over our data.
We’ll begin by retrieving a CSV file from our system using a Get file content action. In my example, I’ll be using a Microsoft Excel CSV file which contains a few rows of data detailing a (fake) company’s expenses.
In our next step, we’ll add a new action and search for Cloudmersive connectors. We’re looking for the Cloudmersive Document Conversion connector with the green logo.
We’ll click “See more” to reach the actions list, and from there, we’ll CTRL+F search for an action called Convert CSV to JSON.
After we select this action, our first order of business is to create our connection, and to do that, we’ll need a Cloudmersive API key. I’m using a free API key in this walkthrough (which allows up to 800 API calls per month with zero commitments), and you can get one of those by visiting the Cloudmersive website and creating a free account. It’s important to note we’ll also need a premium Power Automate license to access Cloudmersive connectors in the first place.
To structure our CSV to JSON conversion request, we’ll simply add our CSV file bytes and file name (this can be any name) in each of our two request parameters.
Next, we’ll add the Parse JSON action to our flow. We’ll find this action in the built-in Data Operation connector.
After we open this action, we’ll add response content from our Convert CSV to JSON action labeled Body into the Parse JSON Content parameter.
Next, we’ll create a Schema for our response content. One easy way to generate a Schema is by using a sample payload — and to get one, we can simply save & run our flow as it is.
To make sure the Parse JSON action doesn’t fail our initial flow validation check, we can simply add {} in the sample payload window. This will generate a basic object schema which allows us to run our flow, and it won’t parse our data correctly — which we’re fine with for now.
After our flow finishes running, our placeholder Schema validation will fail, but we’ll now be able to 1) copy our Convert CSV to JSON response content, 2) begin editing our flow once again, and 3) paste our payload in the sample payload parameter.
Now, when we make our CSV to JSON conversion, we’ll have an array of dynamic content available in our flow.
In my example flow, I’ll use the Send an email (V2) action from the Office 365 Outlook connector to send one email to myself about each expense recorded on my original CSV file. Because my Parse JSON response content is an array, Power Automate will automatically wrap my action in a For each control to loop through each array item.
To see this in action, I’ll now save & test my flow, and then I’ll check my inbox.
After looping through my JSON array, the Send an email (V2) action shared each line of relevant expense information with me via email.
Just like that, we’ve turned CSV data into actionable dynamic content in our Power Automate flow.
We can automate all kinds of useful data-related actions using the Cloudmersive Document Conversion connector in Power Automate!
