cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sshokri89
Regular Visitor

Extracting the last line of CSV and update a row using Power Automate

Hi all,

 

I am required to parse CSV in power automate so that I find the TOTAL value (coming as the last row) and use that to update a a row in another table.

 

I Know there are two methods so far: 1) split CSV   2)using Office Automate as a step

 

I have read a lot about first method but couldn't make it work. hence I went with option 2. The whole process that I'm taking is like this: 

 

 

sshokri89_4-1632281463939.png

 

 

  1. I read the csv from sharepoint where I save it
  2. I convert to string and trim it using this under COMPOSE step:   

 

concat(trim(base64ToString(outputs('Get_file_content')?['body']['$content'])),',')​

 

  • I convert the above output to JSON using a script in office AUTOMATE (it's in JavaScript language):sshokri89_5-1632281691828.png

     

function main(workbook: ExcelScript.Workbook,
csvData: string) {
var lines = csvData.split("\n");
var result = [];
var headers = lines[0].split(",");

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);
}
console.log(result.toString());
return JSON.stringify(result);
}

4. And finally intend to get the TOTAL value from the JSON to update a row in another file.

 

sample csv:

 

Customer Sales,
Custom Sales by Customer Summary,
1 September 2021 - 30 September 2021,
,
,
Region: Name (Grouped) ,Sales 
,"$12,161"
NSW / ACT,$280
SA,$801
NSW / ACT,-$35
VIC,$116
SA,$94
NSW / ACT,$298
WA,$55
SA,$903
VIC,$478
NSW / ACT,$222
Total,"$97,551"

 

 

Expected Output:

 

{total}={'$97,551'}

 

 

 

Issue with this method:

At the moment, everthing works find up to the step where the JAVASCRIPT code needs to convert it to JSON.

Thanks in advance for you help!

 

As you can see, the input is fine; my headers will always sit at line 6. but the OUTPUT is all wrong. it should output each line as a pair of two components.

 

sshokri89_6-1632282041896.png

 

 

Please help me if possible, with above method or any other method that you know it might work. thanks a lot in advance 🙂

0 REPLIES 0

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (7,853)