2

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.

Screenshot of the csv file (opened with notepad++) Any idea on how I can achieve this using Azure Logic Apps? Thanks!

SD4
  • 439
  • 10
  • 27
  • Hi, do you think it takes too much time and too complex if do the add number in for each loop ? – Hury Shen Nov 04 '20 at 07:26
  • 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 Nov 04 '20 at 07:29
  • 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 Nov 04 '20 at 07:46
  • 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 Nov 04 '20 at 07:50
  • 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 Nov 04 '20 at 08:54
  • May I know the column name is `PreTaxCost` or `Pre tax cost`, I need to do some test in my code. The space in the column name may have impact. – Hury Shen Nov 04 '20 at 09:14
  • And is there any space between the headers ? The header of csv is `InstanceID,MeterID,UsageQuantity,ResourceLocation,PreTaxCost` or `InstanceID, MeterID, UsageQuantity, ResourceLocation, PreTaxCost` ? – Hury Shen Nov 04 '20 at 09:21
  • Hi @HuryShen It is is PreTaxCost. There's a space between each column name like InstanceID, MeterID, UsageQuantity, ResourceLocation, PreTaxCost. I'll post a screenshot of the CSV file in the post – SD4 Nov 04 '20 at 09:24
  • Could you please provide a screen of the csv header(open the csv with notepad++ but not with excel) ? – Hury Shen Nov 04 '20 at 09:31
  • I will do some further test and provide the solution tomorrow. – Hury Shen Nov 04 '20 at 09:38
  • Yes @HuryShen sure I'll do that. I'll send the new screenshot in sometime. Thanks so much! – SD4 Nov 04 '20 at 09:45
  • Hey @HuryShen sorry there's no space between the column names. I've added the screenshot of the csv file opened with notepadd++ – SD4 Nov 04 '20 at 10:04
  • The solution provided by user20200509 is almost same with mine. You can refer to it to solve your problem. – Hury Shen Nov 05 '20 at 01:27
  • Hey @HuryShen so I added the code below to the inline code in the logic app. In "" I'm calling the "FILE_CONTENT" of the blob from "get blob contents" trigger. But the output I'm getting is 0 – SD4 Nov 05 '20 at 04:27
  • The line in code should be `var content = workflowContext.actions.Get_blob_content.outputs.body;`. If your "Get blob content" is named "Get blob content 2", the line should be `var content = workflowContext.actions.Get_blob_content_2.outputs.body;` – Hury Shen Nov 05 '20 at 05:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/224130/discussion-between-sd4-and-hury-shen). – SD4 Nov 05 '20 at 05:19

2 Answers2

2

According to our conversation and your requirements. Since you can't solve a problem when doing add number in JS inline code, so provide the add number steps below for your reference:

1. Delete the last few lines of the code in JS inline code action, return json directly.

2. Initialize two variables sum and tempItem.

enter image description here

3. Use "For each" to loop the Result from JS inline code action, and do set variable action in the "For each" loop.

enter image description here

4. The expression of fx add(...) is add(variables('tempItem'), float(items('For_each')?['PreTaxCost'])). If your "For each" loop named For each 2, the expression should be add(variables('tempItem'), float(items('For_each_2')?['PreTaxCost'])).

5. Please do not forget enable Concurrency Control, and set Degree of Parallelism as 1. Then run the logic app, you can get the sum result.

Hury Shen
  • 14,948
  • 1
  • 9
  • 18
1

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

user20200509
  • 27
  • 5
  • Thanks for your response! I have a "get blob contents" trigger. Do I need to specify the path of the blob(csv file) in the above code ? – SD4 Nov 04 '20 at 13:39