UNIX for Dummies Questions & Answers

Thanks you danmero for the help.

Here is a shell script solution:

Code:

#!/usr/bin/ksh
typeset -i mI=0
IFS='|'
set -A mTag $(head -1 inp_file)
sed '1d' inp_file | while read mLine; do
  set -A mValue ${mLine}
  mI=1
  while [[ ${mI} -le ${#mValue[*]} ]]; do
    if [[ "${mValue[$mI]}" != "" ]]; then
      for mTemp in $(echo ${mValue[$mI]} | sed 's/,/|/g'); do
        echo "${mValue[0]}|${mTag[$mI]}|${mTemp}"
      done
    fi
    mI=${mI}+1
  done
done

Danmero,

Thank you very much, but the output is getting sorted, can you please help me so that the output is not sorted, and same as the orginal file.

Code:

awk '
BEGIN{
        FS=OFS="|";ORS = "\n";
        print "ID","Col_Name","Value","Count"
}
!(NR-2){
        split($0,l,FS);
        next
}
{
        for(o=1;++o<NF;)
        {
                split($o,_,",");
                for(O=0;O++<asort(_);)
                {
                       print toupper($1),toupper(l[o]),toupper(_[O]),O
                }
        }
}'

Thanks
Sunil

---------- Post updated at 05:01 PM ---------- Previous update was at 02:46 PM ----------

Danmero,

I have a few changes for the outfile, am not able to modify the awk , can you please help me in this.

I have highlighted the new change in brown color

Can anyone please help me with the below scenario.

I have a Flat file of the below format.

Code:
ID|Name|Level|Type|Zip|MAD|Risk|Band|Salesl|Dealer|CID|AType|CValue|LV|HV|DCode|TR|DU|NStartDate|Use rRole|WFlag|EOption|PName|NActivationDate|Os|Orig|Cus|OType|ORequired|DType
03| Prog|3||90001,90002,90003,90004-90006,90007,90008|881,866,862-839,828||99-99,88-88,11-19||||REG||||MOQA,MODD,9999,8229,8228,8227,8226||||Confffers||Leased|||FREE-TO-GO-2011,FREE-TO-GO-2012|NMS|new|Acquis,Acquis|mandat,mandat|


Rules

File Column delimiter is |
ID column will not have multipe values seperated by commas.
Columns which have values separated by commas, should be made into a New row.
Columns which do not have values , no need make them into New row.
Last column Orequired is to be concatenated with appropriate Os value in order as highlited below.


Output file format requested


Code:
ID|Col_Name|Value
03|Name|Prog
03|Level|3
03|Zip|90001
03|Zip|90002
03|Zip|90003
03|Zip|90004-90006
03|Zip|90007
03|Zip|90008
03|MAD|881
03|MAD|866
03|MAD|862-839
03|MAD|828
03|BAND|99-99
03|BAND|88-88
03|BAND|11-19
03|AType|REG
03|DCode|MOQA
03|DCode|MODD
03|DCode|9999
03|DCode|8229
03|DCode|8228
03|DCode|8227
03|DCode|8226
03|UserRole|Confffers
03|EOption|Leased
03|Os|FREE-TO-GO-2011
03|Os|FREE-TO-GO-2012
03|Orig|NMS
03|Cus|new
03|OType|Acquis
03|OType|Acquis
03|ORequired|FREE-TO-GO-2011/mandat
03|ORequired|FREE-TO-GO-2012/mandat

Code:

gawk 'BEGIN{FS=OFS="|";print "ID","Col_Name","Value"}!(NR-1){split($0,l,FS);for(i in l){if($i=="Os")x=i;if($i=="ORequired")y=i};next}{for(o=1;++o<NF;){if(o==x){X=$o};if(o==y)split(X,Y,",");split($o,_,",");n=0;for(i in _)n++;for(O=0;O++<n;){print $1,l[o],((Y[O])?Y[O]"/":Z)_[O]}}}' file