transform csv file using awk script

Question

I have csv file as below:

C1, C2,   C3,Cv1,Cv2,Cv3,Cv4 ...  this one can be have longer column
x1, x2 ,x3.1, 1.1, 1.2, 1.3, 1.4
x1, x2, x3.2, 2.1, 2.2, 2.3, 2.4
x1, x2, x3.3, 3.1, 3.2, 3.3, 3.4

i would like to transform this csv file to as below:

C1,C2,   C3,CTEXT,XVALUE
x1, x2, x3.1, Cv1 , 1.1
x1, x2, x3.1, Cv2 , 1.2
x1, x2, x3.1, Cv3 , 1.3
x1, x2, x3.1, Cv4 , 1.4
x1, x2, x3.2, Cv1 , 2.1
x1, x2, x3.2, Cv2 , 2.2
x1, x2, x3.2, Cv3 , 2.3
x1, x2, x3.2, Cv4 , 2.4
x1, x2, x3.3, Cv1 , 3.1
x1,x2,x3.3, Cv2 , 3.2
x1,x2,x3.3, Cv3 , 3.3
x1,x2,x3.3, Cv4 , 3.4

Below is my code:

#!/bin/bash
awk -F, -v OFS=, '{ if (NR==1)
{ print $1,$2,$3, "CTEXT","XVALUE"
  i=4; while (i < NF) {
   a[i]=$i; i=i+1
  }
  am=NF; next
}
i=4 ; while (i < am) {
  if (i > NF) {print "record "NR" insufficient value" >/dev/stderr
  break}
  print $1,$2,$3,a[i],$i
  i=i+1
  }
if (am <NF) print "record "NR" too many values for text" >/dev/stderr
}' input.csv

When i run the script, it shows error :

awk: syntax error near line 2 awk: bailing out near line 2

Edit by Ed Morton - I just ran the script through a beautifier (gawk -o- '...') so it's much easier to read/understand:

{
    if (NR == 1) {
        print $1, $2, $3, "CTEXT", "XVALUE"
        i = 4
        while (i < NF) {
            a[i] = $i
            i = i + 1
        }
        am = NF
        next
    }
    i = 4
    while (i < am) {
        if (i > NF) {
            print("record " NR " insufficient value") > (/dev/) stderr
            break
        }
        print $1, $2, $3, a[i], $i
        i = i + 1
    }
    if (am < NF) {
        print("record " NR " too many values for text") > (/dev/) stderr
    }
}

@Barmar: True. My bad. I didn't notice the data, taking the title as correct. — Amadan
– Amadan, Commented Oct 8, 2019 at 1:24
that looks like an old-awk error message. try nawk, or gawk or /usr/xpg4/bin/awk. (Edit your Q to show output of uname -srv). Good luck. — shellter
– shellter, Commented Oct 8, 2019 at 1:43
Please pick a common style of formatting code and use that for your scripts. What you have right now with multiple statements on some lines and haphazard indenting makes your script unnecessarily difficult to understand. Also - awk supports for loops so you can write statements like for (i=4; i<NF; i++) { foo } instead of i=4; while (i<NF) { foo; i = i + 1 } — Ed Morton
– Ed Morton, Commented Oct 8, 2019 at 2:35

tshiono · Accepted Answer · 2019-10-08 02:07:55Z

2

Even if you switch your Solaris awk to gawk or nawk, there still remain some problems. Would you please try the following:

awk -F, -v OFS=, '
NR==1 {
    print $1,$2,$3, "CTEXT","XVALUE"
    for (i = 4; i <= NF; i++) a[i]=$i
    am=NF; next
}
{
    if (am < NF) {
        print "record "NR" too many values for text" > "/dev/stderr"
        next
    }
    for (i = 4; i <= am; i++) {
        if (i > NF) {
            print "record "NR" insufficient value" > "/dev/stderr"
            break
        }
        print $1,$2,$3,a[i],$i
    }
}' input.csv

You need to increment i up to NR or am (not < but <=).
Enclose /dev/stderr with quotes.
Better to use for loop rather than while.

Hope this helps.

edited Oct 8, 2019 at 2:07

answered Oct 8, 2019 at 1:59

tshiono

22.3k2 gold badges18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tshiono Over a year ago

@wina If you mean the message awk: syntax error near line 2 awk: bailing out near line 2 by the same error, you at first need to change to other awk as @shellter comments. My code above does not solve the error itself.

tshiono Over a year ago

@wina Good to know its working. BTW it is amazing you were almost close to the right answer despite you could not run it. Cheers!

karakfa · Accepted Answer · 2019-10-08 16:10:25Z

0

something like this

$ awk -F, 'BEGIN {OFS=FS} 
           NR==1 {n=split($0,h); 
                  print $1,$2,$3,"CTEXT","XVALUE"; 
                  next} 
           n!=NF {print n<NF?"too many":"not enough"; 
                  exit} 
                 {for(i=4;i<=NF;i++) print $1,$2,$3,h[i],$i}' file

C1,C2,C3,CTEXT,XVALUE
x1,x2,x3.1,Cv1,1.1
x1,x2,x3.1,Cv2,1.2
x1,x2,x3.1,Cv3,1.3
x1,x2,x3.1,Cv4,1.4
x1,x2,x3.2,Cv1,2.1
x1,x2,x3.2,Cv2,2.2
x1,x2,x3.2,Cv3,2.3
x1,x2,x3.2,Cv4,2.4
x1,x2,x3.3,Cv1,3.1
x1,x2,x3.3,Cv2,3.2
x1,x2,x3.3,Cv3,3.3
x1,x2,x3.3,Cv4,3.4

answered Oct 8, 2019 at 16:10

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

Collectives™ on Stack Overflow

transform csv file using awk script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related