how to parse a csv file with a specific pattern to am array in Logic App Service

Question

i would like to convert a csv file and to put the outputs into dedicated arrays which will be sent to an azure function. there are 3 challenges in this process :

1. what is the method to read csv file from an email attachment

2. this is an example of how the format of the file looks like :

so the headers are stored at every row in column A and the value is stored at Column B. i need to extract the values to an array based on the header that is specified in Column A

3.on each value there is a need to remove the next strings []

what is the best practice to implement this operation

Answer 1

Yep, DO NOT do this in LogicApps alone, it'll just make your brain melt when you look at it afterwards. Much easier to do the heavy lifting part of your requirement in an Azure Function.

In the portal, create a new .NET HttpTrigger Function with the following code and call it CsvDataToObjectArray ...

#r "Newtonsoft.Json"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
    string csvData = await new StreamReader(req.Body).ReadToEndAsync();

    var result = new JObject();
    var csvList = csvData.Split("\n");

    var csvGroups = csvList.GroupBy(x => x.Split(",")[0]);

    foreach (var csvGroup in csvGroups)
        result.Add(csvGroup.Key, JArray.FromObject(csvGroup.Select(x => x.Split(",")[1]).ToList().ToArray()));

    return new ContentResult()
    {
        ContentType = "application/json",
        Content = result.ToString()
    };
}

You can then call that from your LogicApp. Much easier than stuffing around with 100 actions to make it work for you.

Note: Typically, you'd use the Azure Functions action to make the call but in this case, it's easier to use HTTP so you don't have to format the body as JSON, you just throw the CSV data in directly.

Action

Result

JSON

{
  "IP Address": [
    "Value 1",
    "Value 2",
    "Value 3",
    "Value 4",
    "Value 5"
  ],
  "Domain": [
    "Value 6",
    "Value 7",
    "Value 8",
    "Value 9",
    "Value 10",
    "Value 11"
  ]
}

Answer 2

There's a good answer with screenshots here: Convert CSV to JSON using Azure Logic App

For removing the brackets you can use the replace expression.

If I was implementing this, I'd probably put this into an Azure function too. Logic apps tend to get very hard to read when you try to do stuff like this, and compiled code has better performance too.