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.
public class Script : ScriptBase { public override async Task<HttpResponseMessage> ExecuteAsync() { if (this.Context.OperationId == "csv") { return await this.HandleCSV().ConfigureAwait(false); } HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.BadRequest); response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'"); return response; } private async Task<HttpResponseMessage> HandleCSV() { var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false); // (?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$)) // https://stackoverflow.com/a/48806378 var re = new Regex("(?!$)(\"((?:(?:\"\")*[^\"]*)*)\"|[^\",\r\n]*)(?:,|$)"); var lines = Regex.Split(contentAsString, "\r\n|\r|\n"); var result = new JArray(lines.Select(line=>{ var matches = re.Matches(line); return new JArray(matches.Cast<Match>().Select(match => { return match.Groups[2].Success ? match.Groups[2].Value : match.Groups[1].Value; } ).ToArray()); }).ToArray()); var response = new HttpResponseMessage(HttpStatusCode.OK); response.Content = CreateJsonContent(result.ToString()); return response; } }
explain the regex and match groups
Swagger
This is the custom connection swagger YAML file.
swagger: '2.0' info: {title: CustomCode, description: Custom Code, version: '1.0'} host: johnliu.net basePath: / schemes: [https] consumes: [] produces: [] paths: /Csv: post: responses: default: description: default schema: type: array items: {} description: Array title: Array summary: Parse CSV description: Parse CSV operationId: csv parameters: - name: value in: body required: true schema: {type: string, description: Text, title: value} x-ms-visibility: important definitions: {} parameters: {} responses: {} securityDefinitions: {} security: [] tags: []
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)
