3

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?

4
  • (bash isn't a text editor)
    – Jeff Schaller
    Commented May 24, 2018 at 11:21
  • I meant a bash script that could use awk or perl or whatever
    – bachr
    Commented May 24, 2018 at 11:22
  • indeed, any shell (csh, sh, bash, etc) can call awk or perl or whatever, so the question isn't specific to bash
    – Jeff Schaller
    Commented May 24, 2018 at 11:26
  • 1
    With CSV', you have to beware that fields can contain delimiters (semicolon here) if the field is quoted. Does your file have any such fields?
    – Jeff Schaller
    Commented May 24, 2018 at 11:27

3 Answers 3

3

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
2
  • this is insanely beautiful! can you add some explanations?
    – bachr
    Commented May 24, 2018 at 11:32
  • @bachr, welcome. You have my explanation. Thanks for the question Commented May 24, 2018 at 11:38
3

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 ;

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .