Convert CSV files to JSON in Power Automate

How do you convert CSV files to JSON? When you have data in CSV format and you want to use this within Power Automate, there used to be a lot of splitting by end of lines, commas and various other challenges. Nowadays, converting CSV files to JSON can be done in an easier way.

Read a CSV file

Quite a few years back I wrote a post about reading CSV files in Power Automate. But that can be done so much better now. An imagine having to deal with commas, you would end up with ugly expressions.

The first step will be to just read the content before we can process it.

To read a CSV (Comma-Separated Values) file from SharePoint we can use the Get file content action.

All we have to do is select our sites and select the file that we want to read. This will give us the file content in base64 format.

Using the following expression we can make this content appear as plain text

base64ToString(body('Get_file_content')?['$content'])

When I use the following content in my csv file:

I will get the following content once my base64 content is read.

Column 1,Column 2,Column 3\r\n123,Some Text,21/01/2026\r\n

Convert CSV files to JSON

So far we got the CSV file content from SharePoint and we are ready to convert the CSV to JSON. We can do this with the Run a prompt action as we have done before.

In the input text we just have to include our Base64ToString expression mentioned earlier.

Get the result from the Run a prompt

The Run a prompt will give us

Certainly! Here is the provided CSV data converted into JSON format:

```json
[
  {
    "Column 1": 123,
    "Column 2": "Some Text",
    "Column 3": "21/01/2026"
  }
]
```

If you need further assistance with this or any other data format conversion, please let me know.

To clean this up a bit we will need to add to Compose actions to our flow. You could of course just use one but I’m going to simplify the expression in the second Compose action a bit by using two actions here:

The Compose-ResponseText action we can set to

outputs('Run_a_prompt')?['body/responsev2/predictionOutput/text']

Or you can select Text from the Dynamic content of course.

Now comes the most complicated part of the solution. Do you remember my post about substring and slice? Well we will need to use the slice function now.

The slice function in the below expression will look at the response that we got earlier. Then look for a ‘[‘ and a ‘]’ to identify where the json content is within the response text.

json(
  slice(
        outputs('Compose-ResponseText'), 
        indexOf(outputs('Compose-ResponseText'), '['),       
        Add(IndexOf(outputs('Compose-ResponseText'), ']'),1)
   )
)

And we got the JSON code that we wanted once we converted the json text to a json object with the json function.

Download the example

If you want to download the example then please click on the link below