Parse CSV through Code in Power Automate Custom Connection
I was inspired reading Alex Shlega and Hiroaki Nagao ’s posts on using code with custom connections. So I set out to give it a go and work on another common problem I have: Parse CSV
First a picture showing you how it works.
Give it text, the action returns array of arrays.
Microsoft’s docs are here.
Write code in a custom connector | Microsoft Docs
And particularly, I need to parse CSV without using additional libraries, and using only the existing libraries available here. I noted that we do have access to System.Text.RegularExpressions, so I started my planning there.
Because parsing CSV correctly is a problem best sorted via use of a tokenizer, I went looking for a regular expression pattern that treats each line as a series of tokens. There are many patterns, but I like this one that I found on stackoverflow the best for my needs. https://stackoverflow.com/a/48806378
Code
So the code takes all the content of the body and splits by line breaks, then the regular expression is run over every line using Matches (this method returns multiple matches giving us a MatchCollection of tokens). In each match, I look for Group[2] which is the value without quotes “ and “. But if failing that match, we take Group[1] value.
We do not take the Match.Value because that would include the comma.
/end of regular expression explanation.
We cast the matches back to array via Linq and then back to JArray and return that back to Flow.
explain the regex and match groups
Swagger
This is the custom connection swagger YAML file.
I want to add more parameters over time, and that will involve a tweak to the input parameters on the Swagger definition. But that’s probably a task for another day.
Links:
Write code in a custom connector | Microsoft Docs
C# code in Power Automate: let’s sort a string array? | It Ain't Boring (itaintboring.com)