Introduction

In the olden days, legacy systems used XML as the main data transfer format but with the passage of time, JSON has become the norm as the preferred data interchange format. It has also become the standardized default way to send and receive data over Web API and REST API calls. It is also quite common to extract the data from the source as a JSON file and use this file for uploading the data to the destination. In this article, we will see how we can use Power Automate to read such a JSON file that contains the extracted source data, read the data and upload it to the SharePoint list as individual records.

JSON

JSON stands for JavaScript Object Notation which encapsulates data that is organized as key-value pairs. It always starts with { and ends with a }. A valid example of JSON is :

Implementation

We have extracted the data from SQL Server as a JSON file and now we will be using Power Automate to read through the JSON file to loop through the Objects and create individual items in the SharePoint List.

Within the Schema field, we need to provide the JSON schema that the Parse JSON activity is expecting within the JSON file so that it can map the Key-Value pairs within the file. In case you already have a sample JSON file that you would be using for the implementation, click on Generate from Sample which will let you add the JSON data which gets converted into a schema structure.

Clicking on Done will generate the schema as below:

Now that we have the data from the JSON file captured as Key-Value Pair collection within the Power Automate, the next step is to loop over the collection and get each item that will be created as an item in SharePoint List.

As the input to the Apply to each loop, we provide the body output of the Parse JSON action so that we can iterate through each object in the JSON. For each of the iterated objects, we will then use the create item action to add a new item in SharePoint by mapping the JSON value to the corresponding SharePoint List Column. So the

Caveat

However, if we test the flow right now, we will get the below error :

The reason is that when the JSON file is uploaded and the Power Automate is triggered, the file content read by Power Automate is in an encoded state

As we can see, the content type for the file content is application/octet-stream which is a content type used for unknown binary files. We will have to follow a 2-step process to convert the data back into JSON for which we will add 2 more Compose Steps between the Trigger and the Parse JSON action to format the data into a proper JSON String.

Let’s add a Compose Action and we will take the File content and pass it on to the base64 function which converts the octet-stream into base 64 encoded data

Let’s convert the base 64 data to JSON string using another compose action. Here we will take the output of the “Convert to Base 64 Data” action and pass it on to the Base64toString function for converting it into JSON string which can later be used by the parse action.

We will now pass on the output of Convert Base 64 to String(JSON) to Parse JSON action for further processing :

So now the entire flow would look like below:

Testing the implementation.

We will now upload the JSON file to the Document Library which should ideally kick start the Power Automate flow which will read through the JSON and create an item in the Destination List.

Output

The outcome of the flow run will result in the creation of the list items as below:

Summary

Thus, we saw how to utilize the JSON data dumped from a source like SQL and take leverage of Power Automate to import the data to SharePoint List as the destination.