Power Automate: How to parse a CSV file

Today I was answering a question in the Power Automate Community, and one of the members posted an interesting question. How to parse a CSV file and get its elements? I see this question asked a lot, but the problem is always to use the external component X or Y, and you can do it.

Although some of the components offer free tiers, being dependent on an external connection to parse information is not the best solution. You may not be able to share it because you have confidential information or have the need to parse a lot of CSV files, and the free tiers are not enough. Finally, we depend on an external service, and if something changes, our Power Automates will break.

TL;DR

You can find the template to parse a CSV file in my template archive with other Power Automates that can be useful to you. Download it directly here.

The motivation

I want to answer this question with a complete answer, and I think this will benefit the overall community, so I decided to build a CSV parser using only Power Automate’s actions.

The overall idea is to parse a CSV file, transform it into a JSON, and collect the information from the JSON by reference.

For example, if we have the file:

name,date
Manuel, 12-12-2020
Gomes, 13-12-2020

Teixeira, 12-1-2020

I inserted the space on purpose, but we’ll get to that.

We’ll get the following JSON:

[
  {
    "name": "Manuel",
    "date": " 12-12-2020"
  },
  {
    "name": "Gomes",
    "date": " 13-12-2020"
  },
  {
    "name": "Teixeira",
    "date": " 12-1-2020"
  }
]

And then, we can do a simple “Apply to each” to get the items we want by reference. Looks nice, doesn’t it?

The template

The template may look complicated, but it isn’t. I’ll explain step by step, but here’s the overview.

You can trigger it inside a solution by calling the “Run Child Flow” and get the string with the JSON. If you don’t know how to do it, here’s a step-by-step tutorial.

The trigger

The trigger is quite simple. We need to provide two parameters:

  1. The path of the file in OneDrive. With this we make the Power Automate generic
  2. It’s important to know if the first row has the name of the columns. With this information, we’ll be able to reference the data directly.

Fetching the file

With the parameter in the trigger, we can easily fetch the information from the path.

The variables

The variables serve multiple purposes, so let’s go one by one.

Each Row

In this one, we break down the file into rows and get an array with the information.

The formula is quite simple:

split(outputs('Get_file_content_using_path')?['body'],'
')

It’s not an error in the return between ‘. There would be the temptation to split by “,” but, for some reason, this doesn’t work. If you apply the formula above you’ll get:

[
  "name,date",
  "Manuel, 12-12-2020",
  "Gomes, 13-12-2020",
  "",
  "Teixeira, 12-1-2020"
]

Looks good.

Helper variables

I use the other variables to control the flow of information and the result. You’ll see them in action in a bit.

The parsing

OK, let’s start with the fun stuff. Here we want to:

  1. Fetch the first row with the names of the columns.
  2. Check if we have at least two lines (1 for the column names and one with data)
  3. Get an array for each row separated by ‘,’
  4. Check if the array is not empty and has the same number of columns has the first one. If you have more or less, then we cannot do the mapping, for example:
[
  "name,date",
  "Manuel, 12-12-2020", this one doesn't have a column name
  "Gomes, 13-12-2020",
  "",
  "Teixeira, 12-1-2020"
]
  1. Get the row elements.
  2. Build the JSON element, for example:
{
    "name": "Manuel",
    "date": " 12-12-2020"
  }
  1. Add that to a JSON string (variable created above)

Looks complex? Well, a bit, but at least makes sense, right?

Check the column names and min requirements.

The first two steps we can do quickly and in the same expression. To check the number of elements of the array, you can use:

length(variables('EACH_ROW'))

Parsing the headers and checking the information

Now that we know that we have the headers in the first row and more than two rows, we can fetch the headers. To do so:

split(variables('EACH_ROW')[0],',')

We get the first element and split it by our separator to get an array of headers. Then we start parsing the rows. If it’s the beginning, then we don’t do anything because it contains the headers, and we already have them.

To check if the row has the same number of elements as the headers (second clause of the if) we have the following formulas:

Several elements in the headers:

length(variables('Headers'))

Several elements in the current item:

length(split(items('Apply_to_each'),','))

Now let’s parse the information.

Parsing the remaining rows

First, we get the array with the elements of the row split by ‘,’

split(items('Apply_to_each'),',')

Then we upgrade the iterator since we’re already parsing another row.

The application to each is a little bit more complicated, so let’s zoom in.

Generating the element

Since each row has multiple elements, we need to go through all of them.

First, let’s ad the start of the value with an if statement. This will check if we’re in the beginning and add an “{” or nothing.

if(equals(variables('CSV_ITERATOR'),0),'{','')

The formula is a concatenation:

concat('"',variables('Headers')[variables('CSV_ITERATOR')],'":"',items('Apply_to_each_2'),'"')

Let’s break it down:

  1. Add a “
  2. Go to the position X of the headers and get the name and the current item. Generates
{"name": "Manuel"

We need to increase the element by one. The next column to parse and corresponding value. So that we can generate the second column and the second record:

"date": " 12-12-2020"

Now let’s bring them together:

if(equals(length(outputs('Array_with_each_element_in_the_CSV_line')),variables('CSV_ITERATOR')),'}',',')

Here we’re checking if we’re at the end of the columns. If we are, we close the element with }. Otherwise, we add a “,” and add the next value.

In the end, we get:

{
    "name": "Manuel",
    "date": " 12-12-2020"
  }

Closing the loop

Since it’s so complicated, we added a compose with the formula so that, in run time, we can check each value and see if something went wrong and what it was.

Finally, we reset the column counter for the next run and add what we get to the array:

if(equals(length(variables('EACH_ROW')),variables('ITERATION')),']',',')

If it’s the last line we don’t add a “,” but close the JSON array “]”

Return the JSON

All we need to do now is return the value, and that’s it.

Final thoughts

Please note that you can, instead of a button trigger, have an “HTTP trigger.” With this, you can call this Power Automate from anywhere. Both the “HTTP trigger” and “Response” are Premium connectors, so be sure that you have the correct account.

If you want to call this all you need to do is the following:

Call the Power Automate and convert the string into a JSON:

json(triggerBody()['text'])

Then all you have to do is go through all values and get the information that you need.

Again, you can find all of this already done in a handy template archive so that you can parse a CSV file in no time. There are other Power Automates that can be useful to you, so check them out. Download this template directly here. If you don’t know how to import a template, I have a step-by-step here.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Microsoft Power Automate-related articles here.

Photo by AbsolutVision on Unsplash

Update: 2020-11-17: Many thanks to dyl for spotting a missing formula in the template.

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

19 thoughts on “Power Automate: How to parse a CSV file

    1. Hey Lee,

      You can use a “Parse JSON” that gets the values and creates an array and use a “For Each” to get each value.

      Makes sense?

      Cheers
      Manuel

  1. I am attempting to apply your solution in conjunction with Outlook at Excel:
    1) Trigger from an email in Outlook -> to be saved in OneDrive – > then using your steps for a JSON.
    2) After the steps used here, is it possible to create one JSON that continues to be updated. The end goal here is to use the JSON to update content in Excel (through Power Query).

    What sort of editions would be required to make this work? Do you have any other advice that I might be able to refer to?

    Thank you.

    1. Hey!

      If you want to persist, the JSON is quite simple. You can add all of that into a variable and then use the created file. Tick the “replace if exists,” so the new version will replace the old one.

      Makes sense?

      Cheers
      Manuel

    2. Hey!

      If you want to persist the JSON is quite simple. You can add all of that into a variable and then use the created file to save it in a location. Tick the “replace if exists,” so the new version will replace the old one.

      Makes sense?

      Manuel

      Cheers
      Manuel

  2. I keep getting the same error message

    InvalidTemplate. Unable to process template language expressions in action ‘Each_Row’ inputs at line ‘1’ and column ‘6184’: ‘The template language function ‘split’ expects its first parameter to be of type string. The provided value is of type ‘Object’. Please see https://aka.ms/logicexpressions#split for usage details.’.

    I downloaded your flow file and still get the same problem. Any Ideas?

    1. Hey Caleb,

      Are you getting this issue right after you upload the template?

      That’s really strange. If there’s sensitive information, just email me, and we’ll build it together.

      CHeers
      Manuel

        1. Hi Pieter,

          I simulated the upload of the template and tested it again. I exported another template just to be sure that it wasn’t an export problem.

          You can find the new template here

          Can you please try?
          Thanks
          Manuel

      1. Hi Anna,

        I re-imported the template and did a bunch of testing and I think it’s all working:
        Flow Result

        To be extra-sure I’ve uploaded that exactly Flow again. You can find it here.

        Can you please give it a try and let me know if you have issues.

        Thanks

  3. Hello,
    I am currently in a tricky spot at the moment. I would like to convert a json i got (from your tutorial) and put it into an online excel worksheet using power automate. So i am trying to parse the Json file to create an array, and iterating through that array and adding every row into the excel document. Currently what i have is a really simple Parse Json example ( as shown below) but i am unable to convert the output data from your tutorial into an object so that i can parse the Json and read each line. Any Tips?

    {
    “type”: “object”,
    “proprerties”: {
    “Title”: {
    “type”: “String”
    },
    “Employee Name”: {
    “type”: “String”
    }
    }
    }

    Or am i looking at things the wrong way?
    Hopefully that makes sense

    Thank you

    1. Hey Lin,

      Your definition doesn’t contain an array; that’s why you can’t parse it.

      Can you please send me the Power Automate print-screens to my email, and we’ll build it together :).

      Cheers
      Manuel

  4. I was following your “How to parse a CSV file” tutorial and am having some difficulties. In my flow every time I receive an email with an attachment (the attachment will always be a .csv table) I have to put that attachment in a list on the sharepoint. But in the variable “Each_row” I can’t split the file because it is coming to me as a base64 file. Any idea how to solve? Thank you!

    1. HI Anna,

      Have you imported the template or build it yourself? I’m finding it strange that you’re getting that file and not a JSON to parse.

      If you’re not comfortable posting details here,, please feel free to email me with your Flow to try to help you further.

      Manuel

Leave a Reply

%d bloggers like this: