Read csv file with header on the second line but keeping the data of first line

[RVDL]

Hi All,

Suppose I have a csv file as per attachment. It contains the following data:

"Account Overview","20221108",,
"Account Date","Country","City","Amount"
"20220930","UK","London","100.25"
"20220930","UK","Birmingham","16.5"
"20220930","France","Paris","50.125"
"20220930","Germany","Frankfurt","123.45"
"20220930","France","Nice","5432.987"
"Total Accounts","5",,

The header starts on line 2. I now need a workflow that does the following:
- Read the csv file.
- For those lines where "Country" = UK, multiply "Amount" by 2.

- Output a csv file as per below:
"Account Overview","20221108",,
"Account Date","Country","City","Amount"
"20220930","UK","London","200.5"
"20220930","UK","Birmingham","33.0"
"20220930","France","Paris","50.125"
"20220930","Germany","Frankfurt","123.45"
"20220930","France","Nice","5432.987"

Thus the output file should include the first line ("Account Overview","20221108",,), and should drop the last line.

Thanks.

[Felipe_Ribeir0]

Hi @RVDL 

One way of doing this

[DataNath]

Hey @RVDL, how does something like this look? The first part of the workflow assigns a RecordID to rows, finds the maximum and drops the final line based on this. After that, we then just multiply the UK values by 2, leaving the others as is:

Final output:

[gautiergodard]

Hello @RVDL 

Here is one way you could do this:

Hop this helps!

[RVDL]

Thanks All. I probably should have been clearer that column "Country" isn't necessarily always Field_2, and "Amount" isn't always Field_4. I really would like to work with the actual field names [Country] and [Amount].

[Felipe_Ribeir0]

Hi @RVDL 

Try this version, it will do the job despite the column changing order

[RVDL]

Thanks Felipe. This solution seems to drop the header line as it isn't in the output file.

[RVDL]

Thanks. But looking for a solution where I use the columns [Country] and [Amount] instead of referring to [Field_2] and [Field_4].

[Felipe_Ribeir0]

Hi @RVDL 

The last one that i attached is not dependent on the [Field_x] to apply the formulas (try a different column order, it should work), and the output is like this:

I could not understand exactly what is not being done, could you detail more what is it?

[RVDL]

Hi @Felipe_Ribeir0 

In the output, the second line should contain the header:

"Account Date","Country","City","Amount"

This is currently missing.

Thanks.