Converting 2 column data file to row format

Question

i am Beginner Unix user,trying to convert a file having 2 columns to a row format data file.

Sample data file : HEADER Count: 6 , EMPID, EMPNAME,SALARY,DEPT,AGE,JOD

col1;col2

empid;1001
empname;ABC
salary;3000
dept;ABC
age;24
JOD;20170101
empid;2001
salary;5000
dept;XYZ
age;27
JOD;20170303
empid;1002
empname;MAN
salary;11000
dept;SCI
age;30
JOD;20180607
empid;1005
empname;NAME
salary;10200
dept;XYZ
JOD;20161212

Below is the code i have tried, but i am not getting desired output.

awk '
BEGIN {FS=';'
       OFS=';'
       RS="\n"
Print An = "empid", Bn = "empname", Cn = "salary", Dn = "Dept", En = "age", Fn = "DOJ"
     }
     {
     A=B=C=D=E=F=" "
     for ( i = 1; i<=NF; i++)
          {
           if($i == An)
                A = $(i+1)
           if($i == Bn)
                B = $(i+1)
           if($i == Cn)
                C = $(i+1)
           if($i == Dn)
                D = $(i+1)
           if($i == En)
                E = $(i+1)
           if($i == Fn)
                F = $(i+1)
             }
            print A, B, C, D, E, F
             }' FILE.txt >New_file.txt

let me know if anything is required from my side. i am using PUTTY for unix, 64 bit intel core i5-5300U CPU, 2.30GHz.

Could you please help me out with this.I would be very helpful.

Regards, Naresh

Answer 1

Since you don't have a consistent number of fields in each input record, a 2-pass approach is required to avoid having to hard-code column names in your code:

$ cat tst.awk
BEGIN { FS=OFS=";" }
NR==FNR {
    if ( !($1 in colNrs) ) {
        colNrs[$1] = ++numCols
        hdr = (numCols>1 ? hdr OFS : "") $1
    }
    next
}
FNR == 1 { print hdr }
{ colNr = colNrs[$1] }
colNr in vals { prt() }
{ vals[colNr] = $2 }
END { prt() }

function prt(   colNr) {
    for (colNr=1; colNr<=numCols; colNr++) {
        printf "%s%s", vals[colNr], (colNr<numCols ? OFS : ORS)
    }
    delete vals
}

.

$ awk -f tst.awk file file
empid;empname;salary;dept;age;JOD
1001;ABC;3000;ABC;24;20170101
2001;;5000;XYZ;27;20170303
1002;MAN;11000;SCI;30;20180607
1005;NAME;10200;XYZ;;20161212

To use the above awk script in a shell script:

#!/bin/env bash

... other shell stuff ...

awk '
BEGIN { FS=OFS=";" }
NR==FNR {
    if ( !($1 in colNrs) ) {
        colNrs[$1] = ++numCols
        hdr = (numCols>1 ? hdr OFS : "") $1
    }
    next
}
FNR == 1 { print hdr }
{ colNr = colNrs[$1] }
colNr in vals { prt() }
{ vals[colNr] = $2 }
END { prt() }

function prt(   colNr) {
    for (colNr=1; colNr<=numCols; colNr++) {
        printf "%s%s", vals[colNr], (colNr<numCols ? OFS : ORS)
    }
    delete vals
}
' file file

... other shell stuff ...

Answer 2

First of all there are syntax errors in your code.

The single quotes in your BEGINblock should be double

BEGIN {FS=";";
       OFS=";";
       RS="\n";

Secondly, you cannot assign variables and print in the way that you have done and this needs correction.

Thirdly Dn = "Date" and the field in your records is "date" and you have the same issue with "DOJ"/"JOD"

        An = "empid"; Bn = "empname"; Cn = "salary"; Dn = "dept"; En = "age"; Fn = "JOD";
        print An, Bn, Cn, Dn, En, Fn
     }

These corrections to the BEGIN block get you to ...

empid;empname;salary;dept;age;JOD
1001; ; ; ; ; 
 ;ABC; ; ; ; 
 ; ;3000; ; ; 
 ; ; ;ABC; ; 
 ; ; ; ;24; 
 ; ; ; ; ;20170101
2001; ; ; ; ; 
 ; ;5000; ; ; 
 ; ; ;XYZ; ; 
 ; ; ; ;27; 
 ; ; ; ; ;20170303
1002; ; ; ; ; 
 ;MAN; ; ; ; 
 ; ;11000; ; ; 
 ; ; ;SCI; ; 
 ; ; ; ;30; 
 ; ; ; ; ;20180607
1005; ; ; ; ; 
 ;NAME; ; ; ; 
 ; ;10200; ; ; 
 ; ; ;XYZ; ; 
 ; ; ; ; ;20161212

Which is because of logic errors.

As @pLumo points out there are missing data fields in your data sets and your code does not allow for this, but more, awk is printing at every record (every line) and not every record set (just before the occurrence of empid).

There are many ways to skin this particular cat but since I am in a good mood today here's a simple one to follow for a beginner to learn arrays....

In the BEGIN block load an array with the field numbers you want using the field names as the indices and print the titles

awk -F";" 'BEGIN{
    fields["empid"]=1;
    fields["empname"]=2;
    fields["salary"]=3;
    fields["dept"]=4;
    fields["age"]=5;
    fields["JOD"]=6;
    print "empid;empname;salary;dept;age;JOD"
    }

if the first field is "empid" and && it is not the first record NR>1 then iterate over the array that will contain your field values (the array is empty when NR==1 this is why we skip it) and after you have printed the array then empty it for reuse by delete

    $1=="empid" && NR>1 {
         for (f=1; f<6; f++) printf field[f]";"; print field[6]; delete field}

For every line in the file, load the value $2 into your array of values field using the field number recovered from the fields array we built in the BEGIN block as an index, based upon the field name in $1

   {field[fields[$1]]=$2

The array will still be loaded with unprinted values when you get to the end of the file and so you have to print the array one last time

    }END{for (f=1; f<6; f++) printf field[f]";"; print field[6]}' file1

Edit

With this script copy/pasted into the terminal

awk -F";" 'BEGIN{
    fields["empid"]=1;
    fields["empname"]=2;
    fields["salary"]=3;
    fields["dept"]=4;
    fields["age"]=5;
    fields["JOD"]=6;
    print "empid;empname;salary;dept;age;JOD"
    }$1=="empid" && NR>1 {
         for (f=1; f<6; f++) printf field[f]";"; print field[6]; delete field}{field[fields[$1]]=$2 
         }END{for (f=1; f<6; f++) printf field[f]";"; print field[6]}' file1

With this input

empid;1001
empname;ABC
salary;3000
dept;ABC
age;24
JOD;20170101
empid;2001
salary;5000
dept;XYZ
age;27
JOD;20170303
empid;1002
empname;MAN
salary;11000
dept;SCI
age;30
JOD;20180607
empid;1005
empname;NAME
salary;10200
dept;XYZ
JOD;20161212

Output is

empid;empname;salary;dept;age;JOD
1001;ABC;3000;ABC;24;20170101
2001;;5000;XYZ;27;20170303
1002;MAN;11000;SCI;30;20180607
1005;NAME;10200;XYZ;;20161212