How to Convert Excel XLSX Data to JSON in Power Automate

In this quick Power Automate walkthrough, we’ll learn how to convert Excel data to JSON using the Cloudmersive Convert API (available as the Cloudmersive Document Conversion connector in Power Automate).

Original Excel data (generic expense report spreadsheet)

Converted Excel data available as dynamic content via Parse JSON action

By converting our Excel data to JSON, we’ll end up with an array of JSON objects which we can parse into a custom schema. This schema will allow us to use data from our Excel spreadsheet as dynamic content downstream in our flow.

We’ll demonstrate this process in the context of a manually triggered, instant cloud flow.

We’ll begin our flow diagram by adding a Get File Content (SharePoint or OneDrive) action to retrieve a spreadsheet from our file system.

Setting up a Get File Content action to retrieve an Excel spreadsheet

This demonstration will use a generic “expense report” spreadsheet (pictured at the beginning of this article) that contains several lines of employee expense information.

Next, we’ll incorporate a Cloudmersive Document Conversion connector action titled Convert Excel XLSX to JSON. We’ll find this action by typing “Cloudmersive ” into the action search bar and selecting the Cloudmersive Document Conversion connector (with the green logo) from the resulting list.

Searching for Cloudmersive API connectors

Searching for the Convert Excel XLSX to JSON conversion action

To configure our conversion action, we’ll first need to create our Cloudmersive connection by naming our connection and providing a free Cloudmersive API key. With a free Cloudmersive API key, we’ll get up to 800 API calls per month with zero commitments. Bear in mind, however, that we’ll need a premium Power Automate license to access Cloudmersive connectors.

Once our connection is created, we’ll add file content from our Get File Content step to the first field, and then we’ll add a file name in the second field. We do not need to use our actual file name for this action to work correctly.

Configuring the Convert Excel XLSX to JSON conversion action

In our next step, we’ll parse our JSON response into a custom schema using Power Automate’s Parse JSON action. Before we do that, however, we’ll first run the flow as it is to obtain the sample JSON payload we need to generate our custom schema.

When our flow finishes running, we will find our sample payload by opening the Cloudmersive conversion action and copying the JSON array from the output body.

Copying a sample payload from the initial flow run

With this data copied to our clipboard, we’ll now add a new action and search for the Parse JSON action. We’ll find this action on the Data Operation connector actions list.

In our opened Parse JSON action, we’ll first click on the “content” field and add dynamic content labeled “body” from our Excel to JSON conversion action. After that, we’ll select the option to generate our schema from a sample, and we’ll paste our sample JSON response into the subsequent field.

Using the sample payload to generate a custom schema in the Parse JSON action

After we click done, Power Automate will quickly generate a custom schema for our data that describes each name-value pair in our array.

Reviewing the custom generated schema in the Parse JSON action

We can now use our converted Excel data in any subsequent action. With the expense report information used in this flow, we could, for example, create a series of Power Automate Approvals and share those with a relevant stakeholder.

Utilizing dynamic content from the Parse JSON schema for a practical purpose

We’ll notice that Power Automate automatically wraps the Approval action in a For each control once we add dynamic content to our approval message. This ensures individual approvals are sent for each expense report object in our JSON array.

And that’s all there is to it!

Now we can effortlessly utilize data from our Excel spreadsheets as dynamic content in our Power Automate flows.