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

1 Answer 1

Reset to default
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"
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.