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?

  • (bash isn't a text editor) – Jeff Schaller May 24 '18 at 11:21
  • I meant a bash script that could use awk or perl or whatever – bachr May 24 '18 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 May 24 '18 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 May 24 '18 at 11:27
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
  • this is insanely beautiful! can you add some explanations? – bachr May 24 '18 at 11:32
  • @bachr, welcome. You have my explanation. Thanks for the question – RomanPerekhrest May 24 '18 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.