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

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.

Sample.csv

Workflow

Accepted answers

DataNath

@RVDL here's what I came up with and as you can see, it's independent of column positions:

Output CSV with 2 x UK amounts.yxmd

Felipe_Ribeir0

Hi @RVDL

You are right, i missed it. Here it is, now with this new correction:

Read csv file with header.yxzp

All comments

Felipe_Ribeir0

Hi @RVDL

One way of doing this

Read csv file with header.yxzp

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:

Output CSV with 2 x UK amounts.yxmd

gautiergodard

Hello @RVDL

Here is one way you could do this:

Hop this helps!

New Workflow100.yxmd

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

Read csv file with header.yxzp

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: