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"
.......
5
  • @cas Can you help?
    – Polucho
    Oct 30, 2015 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) Oct 30, 2015 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. Oct 30, 2015 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, 2015 at 8:36
  • @Costas yes it's sorted by first file.
    – Polucho
    Oct 30, 2015 at 9:21

1 Answer 1

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"

You must log in to answer this question.

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