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?

0

1 Answer 1

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
2
  • 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
    Commented Aug 6, 2018 at 6:57
  • @ness: Try modified version
    – RudiC
    Commented Aug 6, 2018 at 8:19

You must log in to answer this question.

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