2

I'm trying to organise a CSV file with 30 columns and 1000 rows. I want to concatenate the rows column by column if the 1st column is the same, using a vertical line (|).

Input:

3,XX,YY,123,1234,MM,-,,DD,1235,XC
3,XM,YM,123,1234,MD,-,A,FD,1233,XC
3,XN,YN,123,1234,MM,-,,ED,1235,XC

Output:

3,XX|XM|XN,YY|YM|YN,123,1234,MM|MD,-,A,DD|FD|ED,1235|1233,XC

So I only want to concatenate the different values.

I had a look at this thread but wasn't helpful for my problem?

1

Why wasn't the thread helpful? After slight modifications, try

    awk -F, '
    function p(n,A) {s = n
                     for (i=2; i<=NF; i++)  {s = s FS  A[i]
                                             A[i] = $i
                                            }
                     if (n) print s
                    }


    $1==n           {for (i=2; i<=NF; i++) if (A[i] !~ "[|]*" $i "[|]*") A[i] = A[i] "|" $i
                     next
                    }

                    {p(n,A)
                     n = $1
                    }

    END             {p(n,A)
                    }
    ' file
3,XX|XM|XN,YY|YM|YN,123,1234,MM|MD,-,|A,DD|FD|ED,1235|1233,XC
  • It returns an error. awk: illegal primary in regular expression |*COSM4955034|* at COSM4955034| input record number 2, file test.csv source line number 10 – ness Aug 6 '18 at 6:57
  • @ness: Try modified version – RudiC Aug 6 '18 at 8:19

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.