cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
juresti
Level 8

Create JSON object from csv or tab delimited for dynamic content

This post shows how to create your own JSON object from csv or tab delimited.

 

This is an extended solution to my other solutions on handling csv and tab delimited files.

It will allow you to access the column names from the dynamic content.

 

You can insert this solution into my other solutions at these steps:

At the end of step 7 tab delimited file https://powerusers.microsoft.com/t5/Flow-Cookbook/Read-tab-delimited-and-write-records/m-p/246459

At the end of step 13 csv file https://powerusers.microsoft.com/t5/Flow-Cookbook/read-csv-file-and-write-to-destination/m-p/258524

 

It may appear unnecessary, however it will be nice to select the column names when processing your data.

On top of that it will provide some data validation in regard to data type.

Other uses for this are that you can use this output from another flow by easily accessing the columns or you can send it to another system that requires this format.

 

1. Set the string where we build the json object

json1.PNG

Notice I set an initial value which is the beginning of the json object. Since we are appending the body, it needs to have the "heading" set.

 

The json object is just like an excel sheet or sql table, except it looks like javascript.

It's confusing at first but a basic json like this just contains rows, and within the rows are the items list, which are the records in the rows composed as array of column name - value pairs.

 

2. Append the records to the json object as column value pairs

For this example I use the csv import flow

append json string items step uses an append to string variable action

json2.PNG

 

The items in this JSON object array follow this format. (The items are our rows or records)

The column name is on the left and the value is on the right.

These are the column names that we will be able to select from the field list once JSON parses it.

 

As we loop through each row, we access each column and append it to the JSON string.

In this example I'm only using 3 fields / columns. 

You will access the column values just like in the linked examples above by using their index number.

 

We append the JSON item like so:

{ "column_name1" : "variables('eachDataRow')[0]",

  "column_name2" : ".etc.. .... ",

  "column_name3" : ".etc......" } ,

Note: if you have a value that needs to be in number format, remove the quotes from the value side.

my record id is string format for this example.

 

Notice there is a comma at the end of each column value pair and at the end of the record item.

 

Note: The variable funciton needs to be entered in the expression input.

 

json3.PNG

 

 

3. Set the ending of the JSON object string

Now that we have the header and the body of the JSON string, we need to "end and close" the object.

json4.PNG

 

 

 

 

 

 

 

At this step I use the compose action to create the completed JSON object string.

The inputs are the jsonStringCompose variable where we appended the header and the items.

Then we close the array and the header. The closing characters are as follow:

] } }

 

We now have a complete valid json object ready to be parsed. Once parsed we get access to select the column names from the dynamic content.

 

4. Parse the JSON object

In this step we will create our JSON schema for our json object and parse our json.

Note: Disregard the names of the following actions Parse JSON 2 and Parse JSON, there should only be one except I created a blank one to show how it looks empty.

json5.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on the highlighted link to open the schema input box.

 

This is the window where you will type / copy your schema into.

json6.PNG

My schema looks like this, and if you are working with a cvs or tab delimited file you should only need this structure.

Of course you would have your own columns which will be more than this example.

json7.PNG

Note: This does not match my actual example, I wanted to show how a number column value pair looks.

If it was money type you would include decimals. Also notice the quotes are gone from the number type.

The rows, items, and column names should always have quotes, and they are actually properties of the object.

 

When you click done, the Parse JSON generates the schema and it is ready to parse our data.

5. Set the content data to parse

json8.PNG

Here we handle one detail which is removing a comma from our compose step.

Since the append json string item step was adding a comma at the end of each object we end up with a comma at the very end of the array items and it must be removed.

The content expression would look like so:

replace(outputs('compose_json_parts'),'},]','}]')

 

Finally you can access the column names from the dynamic content

json9.PNG

1 REPLY 1
Administrator
Administrator

Re: Create JSON object from csv or tab delimited

Hey, @juresti!

Thanks so much for sharing your Flow recipe! 😁

 

Happy Flowing!

-Gabriel
Microsoft Flow Community Manager

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 144 members 4,415 guests
Recent signins:
Please welcome our newest community members: