Power Automate: Parse a CSV – New Template

My first article about how to parse a CSV file was on August 3rd, 2020, and this is by far the one that gets the most questions, suggestions, and requests for improvement. Parsing CSV files is not intuitive in Power Automate, and people often give, so I want to help. My objective with this second version of the template is to do all the work for you. You only need to import it, and you’re good to go.

I built the template to parse a CSV file with no dependencies so that anyone could use it, and I’ll keep it the “older” version in case it fits your needs. You can find it in my Cookbook section, where you can download it and give it a shot.

But as always, we need to improve, so let’s improve the template:

Parse a CSV file with no premium connectors

The template now supports quotes, the configuration of separators, select the file that has headers, and more. It’s a huge upgrade from the other template, and I think you will like it. 

What’s new?

There are a few areas that are new so let’s explore them.

Pick the options you want

I’ll detail in the next sections, but I’ve added the possibility for you to select some of the options so that your Flow fits more different types of files.

This way, there’s more customization, and we can parse different types of CSV files using the same template.

Headers or not

You can now parse a CSV file that doesn’t have headers. I provide customization so that you can indicate if there is one or not. This is important because I’ll either consider the first line as the headers or data. If the file doesn’t have headers, I’ll generate sequential headers for you since JSON needs a key for each element.

If you want to merge files, for example, then you can provide multiple files to the template, generate all JSON files and then read one by one and merge them into a new file.

Any character will do

You can add the character you want as a separator. Although it’s called “Comma Separated Values,” you can have files with other characters that separate the data. If you have a lot of text with commas in it, I would advise using another character.

Pro Tip: It doesn’t need to be only one character. You can have the separator as “<separator>” for example.

I’m going a bit away from the “defined” CSV structure with this feature, but I want to provide flexibility for the most demanding cases.

Any file will do

The file doesn’t need to have the extension CSV. You can provide any text file, and the Flow will parse it. Be aware that we’re interested in the content of the file and not the type itself. The opposite also is true. If the file has the extension CSV, but it doesn’t contain the “expected” format, the Flow will fail.

With or without quotes

Previously if the file had quotes separating the text fields, the Flow would consider the quote as a character and not separation.

By adding this feature, we can better parse description fields, for example, that may have commas in them. If this is the case, the field will be parsed and will not break in another column when it finds the comma.

One limitation that is that you need all fields to have quotes. The Flow was complex enough, so I didn’t include “mixed” files with and without quotes. I’ll improve this in a future version

What’s better?

I need to do what I preach, so I started from scratch building this template with all the best practices that I publish here. In my opinion, this new template serves two purposes:

  1. It gives you a more robust template that can do the job for you without you necessarily need to understand how it works.
  2. If you want to explore and learn, I’ve added a lot of information to teach you the concepts. Please take a look at my Power Automate Action Reference to learn more detail about an action that you’re not familiar with.

Scoped

I created scopes everywhere to break things into smaller chunks that are easier to debug and understand. I do this by using the Scope Action that serves as a big box to put other actions.

I strongly recommend that you use this strategy in your Flows. Breaking things into areas that “make sense” will make your Flow a lot more readable and hide complexity if you are want to have a global overview of it.

Comments

I’ve added comments everywhere in the code so that you can understand why I’m using the action. It’s also important to learn how something works or fix something that isn’t correct.

Nothing is perfect, so if you see a typo or some comment that doesn’t make sense, please let me know. Also, if there’s an action that is doing something different from its comment, please let me know. Let’s improve this template together.

No Premium Connectors

This was an important requirement to me. There are many templates out there that work fine, but not many people can use them because they don’t have access to premium connectors. There’s no premium connector, and that was a challenge by itself, but at least everyone can use it.

Option to save JSON

JSON files are easier to parse, so I’ve added the possibility to save the JSON file with all the data converted.

This will save the file in the same folder as the CSV file. In the future, I can add an option to pick another folder or, you can change the “Create the JSON file” action by adding your preferred folder.

I didn’t add the option to replace the file on purpose. If, for some reason, the file was not generated correctly it could replace a file that was correct so please be aware of this.

What can still be better?

There’s always room for improvement, so let’s put already some work on my to-do list for the next version.

Mixed quotes

You can have files with fields with quotes, like strings, and without quotes, like numeric values. This template is a little bit all or nothing. Either you have quotes or not. “Detecting” quotes is quite hard and something I need more time to work with.

Faster

I still need to work on performance. Although I consider that it’s not slow, it could be a lot faster, especially if you have huge CSV files. If you have a file that takes too long to parse and can provide me to test without any legal or privacy issues for both you and me, please send me an email. I’ll delete the file and the email after testing and won’t use the information in it for anything. This way, we’re both sure that the information disappears.

You tell me

You can always let me know if it’s failing or if there’s a specific case where it’s not working as it should. Suggestions are all welcome, and I’m happy to keep on improving this template.

What can you do with it?

Here are some different ways to use it.

Convert a CSV to a JSON

This template is not only to parse a CSV file. You can use it to convert CSV into JSON files. Since you’re providing a CSV and getting a JSON file in return, this feature comes for “free.” JSON files are a lot simpler to parse, so you can use this as a quick way to convert files.

Integrate it (or part of it) in your Flows

If you have a Flow that can benefit from it, you can copy the actions to your Flow. Although I always recommend separating responsibilities, sometimes it’s not possible to do so. If there’s a part of the Flow that could be useful to you, don’t forget that you can “copy and paste” actions.

Another advantage of using scopes is that you can copy a great number of actions in one “go.” Be sure to copy the dependencies, for example, any variable used inside the “block” you’re copying.

Separated Responsibility

With this Flow, you can separate the responsibility of parsing CSV files away from all your other Flows. You can call it from other Flows (if it’s in a solution, you can call it a child Flow)

If you have premium connectors.

You can easily replace the “Manual trigger a Flow” trigger for a “When an HTTP request is received” so that you can call it from anywhere (including outside Power Automate).

Once you’re done, you can send the JSON file back as a “Response.”

Improve it and send it back

If you have a better version of this template, please send it my way. I’ll give you full attribution, and we’ll all learn something new. Awesome right? But please check the rules to submit a Flow in my cookbook.

Final thoughts

I want to thank all the feedback that arrived, both on email and in the comments. I’m very fortunate that the comments are usually super helpful so that we can all learn together.

I hope this template is useful to you, but I’m already working on the next version, so please let me know what features you would like are.

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 Power Automate-related articles here.

Photo by Mike Petrucci on Unsplash

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 →

2 thoughts on “Power Automate: Parse a CSV – New Template

    1. Hi Mark,

      Sure. Two quick questions:
      1. Why are you not able to import with OneDrive connections? Is it because you don’t use it?
      2. Where do you keep your files to edit the template with the service you use.

      Cheers
      Manuel

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: