Excel to JSON using Flow

I was reading through Scott’s recent post -> https://www.hanselman.com/blog/ConvertingAnExcelWorksheetIntoAJSONDocumentWithCAndNETCoreAndExcelDataReader.aspx and I was thinking that this could be done in a codeless way using Microsoft Flow. So, I tried to do this in Flow, and it was really easy to do this – under 15mins, as all the plumbing is already there. This is my Flow.

Excel to JSON Flow.png

The Flow is triggered by a HTTP GET and reads from an Excel file stored in OneDrive.

Trigger and Source.png

The output is already a JSON, but not a valid one probably one, as it has spaces and special characters in the key names, which map to the column names of the table.

Excel JSON.png

So, we can simply re-shape the data using Select, and dump the JSON response.

Select and Response.png

Now, when I call my trigger URL, the Flow executes and returns me the JSON.

Response JSON.png

With Flow add-on for Excel, you could run the same Flow from Excel itself, if you want to.

Hope you find this useful.

 

One thought on “Excel to JSON using Flow

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s