Azure Logic App calculate Total Sum of a column from a CSV file

Question

I'm using an Azure Logic App to get the blob contents from my storage account. The Blob-file is in .CSV file. The CSV file consists of a few columns and multiple rows. The file is basically an Invoice file which consists of a column named "PreTaxCost". I need to calculate the Total Sum of the all the entries within the "PreTaxCost" column in the CSV file to get the TOTAL BILLING COST.

Any idea on how I can achieve this using Azure Logic Apps? Thanks!

Hi, do you think it takes too much time and too complex if do the add number in for each loop ? — Hury Shen
Maybe I can provide another solution for add number, I just thought it. I'm not sure if it can work, need to do some research and then provide the solution. — Hury Shen
Hi @HuryShen the thing is I cant use a third-party connector. Thats the problem. Your solution of the for_each loop for adding number each loop was perfect! But I cannot use a paid connector to convert CSV to JSON :/ — SD4
If you want another solution to add number, maybe I can provide it. But I'm afraid I can do nothing to help you parse csv to json. If you do not want to use third-party connector, you can just write code by yourself to convert the csv to json. You can create a azure function and write convert code in it and invoke the function in you logic app. As far as I know, there isn't a connector which can help us parse csv in logic app(except third-party connector). — Hury Shen
I'm trying to write code to parse csv for you. If success, I will post the solution below. Maybe I will provide solution tomorrow. — Hury Shen

user20200509 user20200509 · Accepted Answer · 2020-11-04T13:12:41

If you want to convert csv to json in Azure logic app, you can use Execute JavaScript Code action to run js code to implement it. The JS code is as below

function csvToJson(csv){

  var lines=csv.split("\r\n");

  var result = [];

  var headers=lines[0].split(",");
  for(var i=0; i<headers.length; i++){
    //remove space
    headers[i]=headers[i].split(" ").join("")
   
  }
  for(var i=1;i<lines.length;i++){

      var obj = {};
      var currentline=lines[i].split(",");

      for(var j=0;j<headers.length;j++){
          obj[headers[j]] = currentline[j];
      }

      result.push(obj);

  }

  return JSON.stringify(result);;
}
var content = "<csv content>";

var json = JSON.parse(csvToJson(content));
var sum = 0;

json.forEach(item=>{
    sum = sum + parseInt(item.PreTaxCost);
});
return sum;

For more details, please refer to here and here

Azure Logic App calculate Total Sum of a column from a CSV file

2 Answers