Append a column from a previous match in CSV file

Question

I've a CSV file with the following format (; is the delimiter):

KEY;..;..;..;..;id1;..;..
SUBKEY;..;..
SUBKEY;..;..
SUBKEY;..;..

KEY;..;..;..;..;id2;..;..
SUBKEY;..;..
SUBKEY;..;..
SUBKEY;..;..

I need to take the id (6th column) from each line starting with KEY then append it to the following rows starting with SUBKEY. So the result should be something like this:

SUBKEY;..;..;id1
SUBKEY;..;..;id1
SUBKEY;..;..;id1

SUBKEY;..;..;id2
SUBKEY;..;..;id2
SUBKEY;..;..;id2

Any idea how this can be done in bash script?

Answer 1

Awk solution:

awk 'BEGIN{ FS = OFS = ";" }
     $1 == "KEY"{ id = $6; next }
     { print $0 (NF? OFS id : "") }' file.csv

• FS = OFS = ";" - treat ; as field separator (FS) and output field separator (OFS)
• $1 == "KEY"{ id = $6; next } - if the 1st field $1 is equal to "KEY" string - set id variable with the 6th field $6.
  next will force awk to jump to the next record (skipping the current one)
• print $0 (NF? OFS id : "") - print/output the whole record $0 with condition:
  if there are any fields within a record (ensured by NF built-in variable) i.e. the record is not empty - append ; (as OFS) and current id value to the end of the record

---

The output:

SUBKEY;..;..;id1
SUBKEY;..;..;id1
SUBKEY;..;..;id1

SUBKEY;..;..;id2
SUBKEY;..;..;id2
SUBKEY;..;..;id2

Answer 2

Sed solution:

sed -E '/^KEY/{s/([^;]*;){5}([^;]*).*/\2/;h;d};/;/{G;s/\n/;/}' file.csv

• /^KEY/{s/([^;];){5}([^;]).*/\2/;h;d}

If the line start with KEY, keep 6th field in the hold space and delete the line

• /;/{G;s/\n/;/}

If the line contain field, get the hold space at the end and substitute \n by ;

Answer 3

I managed to fix it by doing this sed -r 's|^(\S+)(\s+\S+)$|s/^\1.*/\&\2/p|' file2 | sed -nf - file1

Now I'm facing issue where I need to append on a CSV file a column with the name of the ESX where the server lies Append field to csv based on conditional search from another file