Creating a flow to remove first 10 rows of incoming CSV from email attachment

[Pawan_Fin]

Hi Community,

First time using Power Automate, trying to learn as I go. 

I'm trying to create a flow in which I take a CSV from an email, then remove the first 10 rows of the csv, and push it back to a connection.

(Continued below)

I used @abm 's super helpful YouTube video, and the following link to get to where I am

: @Power Automate: How to parse a CSV File to create a JSON array (tachytelic.net)

Please help! 

Getting stuck at the Enter a Valid JSON error above. 

Thanks in advance!

[Pawan_Fin]

The code used in the screenshots

;

{
"Account": "@{outputs('Spit_by_Comma')?[0]}",
"Subsidiary": "@{outputs('Spit_by_Comma')?[1]}",
"Subsidiary Internal ID": "@{outputs('Spit_by_Comma')?[2]}",
"Account Number": "@{outputs('Spit_by_Comma')?[3]}",
"Local Balance(Starting)":"@{outputs('Spit_by_Comma')?[4]}",
"Consolidated Balance(Starting)": "@{outputs('Spit_by_Comma')?[5]}",
"Local (Net Posting)": "@{outputs('Spit_by_Comma')?[6]}",
"Flow Rate": "@{outputs('Spit_by_Comma')?[7]}",
"General Rate": "@{outputs('Spit_by_Comma')?[8]}",
"Consolidated Balance (Net Posting)": "@{outputs('Spit_by_Comma')?[9]}",
"Local Balance (Ending)": "@{outputs('Spit_by_Comma')?[10]}",
"Consolidated Balance (Ending)": "@{outputs('Spit_by_Comma')?[11]}",
"Beginning Balance Rate Difference Contribution": "@{outputs('Spit_by_Comma')?[12]}",
"Net Posting Rate Difference Contribution": "@{outputs('Spit_by_Comma')?[13]}",
"Total CTA Contribution": "@{outputs('Spit_by_Comma')?[14]}",
}

[wskinnermctc]

I don't know if this will fix your flow, but take out that last comma , after "Total CTA Contribution" since the last item will not have a comma at the end.

{
"Account": "@{outputs('Spit_by_Comma')?[0]}",
"Subsidiary": "@{outputs('Spit_by_Comma')?[1]}",
"Subsidiary Internal ID": "@{outputs('Spit_by_Comma')?[2]}",
"Account Number": "@{outputs('Spit_by_Comma')?[3]}",
"Local Balance(Starting)":"@{outputs('Spit_by_Comma')?[4]}",
"Consolidated Balance(Starting)": "@{outputs('Spit_by_Comma')?[5]}",
"Local (Net Posting)": "@{outputs('Spit_by_Comma')?[6]}",
"Flow Rate": "@{outputs('Spit_by_Comma')?[7]}",
"General Rate": "@{outputs('Spit_by_Comma')?[8]}",
"Consolidated Balance (Net Posting)": "@{outputs('Spit_by_Comma')?[9]}",
"Local Balance (Ending)": "@{outputs('Spit_by_Comma')?[10]}",
"Consolidated Balance (Ending)": "@{outputs('Spit_by_Comma')?[11]}",
"Beginning Balance Rate Difference Contribution": "@{outputs('Spit_by_Comma')?[12]}",
"Net Posting Rate Difference Contribution": "@{outputs('Spit_by_Comma')?[13]}",
"Total CTA Contribution": "@{outputs('Spit_by_Comma')?[14]}"
}

[Pawan_Fin]

Saitama ftw again! 
That did immediately get rid of my invalid JSON error (thank you! can't believe I missed that)
but now it has popped up the following error: 
It's strange, because my flow was testing and saving fine earlier, till the Skip first 10 rows Compose..

The error:
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Apply_to_each' at line '1 and column '3779' is invalid. Action 'Edit_Attachment_-_Convert_to_String_and_Parse' must be a parent 'foreach' scope of action 'Apply_to_each' to be referenced by 'repeatItems' or 'items' functions.'.'.

Thanks again!

[Pawan_Fin]

Am I overcomplicating this? All I want to do is take the incoming CSV, delete the first 10 rows, and push the CSV forward..

[wskinnermctc]

It seems complicated to me, because I don't know how your flow works.

I think the issue will be for the "Apply to Each" that is using Current Item as the source. It is probably trying to use the Current Item from the previous Apply to Each which is "Edit Attachment - Convert to String and Parse" and I don't know how that would work correctly. You can use Current Item inside of an apply to each, or a nested apply to each, but not across flow steps to a different apply to each.

Are you trying to use the steps "Split by Comma" and "JSON" on the previous output of "Create CSV Table"? Can you just put those steps "Split by Comma" and "JSON" up in the previous apply to each after "Create CSV Table" using the table outputs as the source of "Split by Comma"? 

[abm]

Hi @Pawan_Fin 

Great to hear that video was useful. For the invalid JSON remove the comma from the last line.

[Pawan_Fin]

Thank you both,

I used the compose inside the Apply to each and was able to successfully save, but now I'm getting the following error when I run my flow on the Create CSV step:

"The property 'columns' must be specified unless the 'from' property value is an array of objects."

I thought that the specification in my JSON input, which I fixed by removing the last comma was defining the columns for the CSV, but the flow isn't moving beyond the Create CSV.

I get the same error if I get rid of the Apply to Each and just attach the 'Split by Comma' and 'JSON' compose to the flow directly

I'm sure all of this is very basic for you all, thanks for helping all the same. After I deliver on the current requirements I'm actually going to do a small course and learn PA a bit properly instead of my YouTube + trial and error approach right now

[abm]

Hi @Pawan_Fin 

Create CSV action step expect an array values here.

See below

[Pawan_Fin]

I don't think I follow @abm 😞 What is No:1 and Product:100 supposed to be for my inputs? my inputs are rows, and I'm defining the columns in my JSON step i.e. Account, Subsidiary, Subsidiary Internal ID and so on

The data coming in from the input step previously looks like this: I've tried to label Rows and Columns 

[abm]

Hi @Pawan_Fin 

In my JSON No & Product is my name of the column. So once it executed the CSV step the output will be

No,Product

1,100

and if you have more than one records JSON format must be something like this.

[

{

"No":1,

"Product":100

},

{

"No":2,

"Product":200

}

]

The CSV output will be

No,Product

1,100

2,200

Not sure your data is in this format or not?

[Pawan_Fin]

The data from my file will change on every automated push from NetSuite (it is generated and pushed daily), it won't be the same data. The columns ofcourse will be the same, but the data itself will change.
All I'm trying to do is take the incoming CSV and remove the first 10 rows and push it back out. I don't know why it's taking this much work to figure this out. Surely there must be a simpler way?

[abm]

Hi @Pawan_Fin 

Could you post a sample data of your CSV file ideally more than 10 rows.

Thanks

[Pawan_Fin]

hi @abm 

Here's the raw CSV file, with the rows im skipping highlighted.

The output from the previous step in my flow looks like this

Thanks

[abm]

Hi @Pawan_Fin 

Could you upload that CSV file for me? I could try using that from my end.

Thanks

[Pawan_Fin]

Hi @abm ,

File is attached below. Had to convert from CSV to XLSX so that I could upload here.

Thank you for all your help! This community is great!

[abm]

Hi @Pawan_Fin 

Thanks for sending the files.

Please see below steps:

Above Compose 2 Press the Enter Key

Above expression I used are:

Compose 3

split(outputs('Compose'),outputs('Compose_2'))

Compose 4

skip(outputs('Compose_3'),10)

Above append to string variable action step expressions: item()

Finally outside the loop create file action step.

Let me know how it goes.

[Pawan_Fin]

Thanks for the response @abm !

I think we're super close.

I'm getting the following error now - image of existing process to pull attachment and converting to string based on your YT video.

What do you think:

Thanks you! 

[abm]

Hi @Pawan_Fin 

Which step its failing. Could you please post a run history of the step where it fails and another screenshot of your flow and mappings.

Thanks

[Pawan_Fin]

Hi @abm ,

It is failing in the last step, which is push to an external application, but the error is in Compose 2 - "The execution of template action 'Push_to_External' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose_2')' is of type 'String'. The result must be a valid array.  "

Error:

Here is the flow: 

1. 

Part 1

(Continued)

Part 2

(Continued) 

Part 3 (Apply to each continued till Append to String Variable(