0

I started to do this.. to swap fields in a csv file but I don't get to finish the script. What I want to do is to concatenate fields from 2 to 7 and group them by the first field. Fields 1, 8 and 9 must be respected for every line.

My work

BEGIN{
FS=";"
OFS="";
x="\"\"";
}
{
for(i=2;i<=7;i++){ if($i!= x)
  k=match(a[$1], $i);
if (k == 0)
{
  a[$1]=a[$1]";"$i;
}
  b[$1]=b[$1]"-"$8""FS""$9;
}
END {
  for (g in a)
    t=split(a[g], A, ";");
  if (t == 2)
  {
    a[g]=a[g]";"x";"x";"x";"x";"x";";
    }
  if (t == 3)
  {
    a[g]=a[g]";"x";"x";"x";"x";";
  }
  if (t == 4)
  {
    a[g]=a[g]";"x";"x";"x";";
  }
  if (t == 5)
  {
    a[g]=a[g]";"x";"x";";
  }
for (h in b)
    q=split(b[h], B, "-");
for (z=1; z <= q; z++)
    b[h]=B[z];                               
}
}

CSV File;

"1033reto";"V09B";"";"";"";"";"";"QVN";"V09B"
"1033reto";"V010";"";"";"";"";"";"QVN";"V010"
"1033reto";"V015";"";"";"";"";"";"QVN";"V015"
"1033reto";"V08C";"";"";"";"";"";"QVN";"V08C"

"1040reto";"V03D";"";"";"";"";"";"QVN";"V03D"
"1040reto";"V01C";"";"";"";"";"";"QVN";"V01C"

"1050reto";"V03D";"";"";"";"";"";"QVN";"V03D"
"1050reto";"V01F";"V07L";"";"";"";"";"QVN";"V01C"
.......

Desired output;

First group is the concatenation of the fields from 2 to 7, of the first four lines. Then repeat for each line with the same first field. Second group is the concatenation of the fields from 2 to 7, from the following two lines. Then repeat for each line with the same first field. Third group is the concatenation of the fields from 2 to 7, from the last two lines. Then repeat for each line with the same first field.

"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V09B"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V010"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V015"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V08C"

"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V03D"
"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V01C"

"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V03D"
"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V01C"
.......
  • @cas Can you help? – Polucho Oct 30 '15 at 6:11
  • Good job on including sample input and desired data, but looking at it hurts my eyes.  Can you please also describe what you want to do?  “swap fields in a csv file” is incomplete and misleading.  Please explain (in text) what your code is doing (e.g., what are arrays a and b?), and where you are getting stuck — a comment in the code is good for illustrating where you are stuck, but a clear English sentence (or two) would make a good addition.  … (Cont’d) – G-Man Says 'Reinstate Monica' Oct 30 '15 at 8:04
  • (Cont’d) …  Finally, is there any chance that you can make your script simpler/shorter?  For example, it seems to be able to handle up to six (consecutive) lines where the first field is the same.  You could cut it down from six to three, making it easier to read (ideally, short enough that it fits without scrolling), without impacting the logic.  Please do not respond in comments; edit your question to make it clearer and more complete. – G-Man Says 'Reinstate Monica' Oct 30 '15 at 8:04
  • 1
    Is the input file sorted by first field (is it possible to meet same value of the first field some where in the file else)? – Costas Oct 30 '15 at 8:36
  • @Costas yes it's sorted by first file. – Polucho Oct 30 '15 at 9:21
0

The awk script is produces desired output

awk '
BEGIN {
    FS = OFS = SUBSEP = ";"
}
$1 != f {
    for (i = 2; i < 8; i++) {
        line = line ";" A[i]
        A[i] = $i
    }
    if (NR > 1)
        for (j in B)
            print f line, j
    line = ""
    delete B
    count = 0
    f = $1
}
/^$/ {
    print $0
    next
}
{
    for (i = 2; i < 8; i++)
        if ($i !~ /""/)
            A[i + count] = $i
        else
            break
    count += (i-2)
    B[$8, $9] = 1
}
END {
    for (j in B) {
        printf "%s;", f
        for (i = 2; i < 8; i++)
            printf "%s;", A[i]
        print j
    }
}' "CSV 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.