Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

File manipulation with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting File manipulation with awk
# 1  
Old 08-21-2007
File manipulation with awk
Could you please help me to achieve the below:
In a file I need to convert the multiple lines whose filed 1 and field 5 values are same into a single line but with the field 4 values comma separed as mentioned below. Fileds after 5 shall be discarded.

Also here by default all other remaining fields will be same.

I/P:
WOR|573|10024|2853369|P|Sto|1|
WOR|573|10024|11358|P|Bir|27|
WOR|573|10024|11101|R|Fta|14|
WOR|573|10024|11067|R|Col|11|
SAS|573|10024|2853369|P|Sto|1|
SAS|573|10024|11358|P|Bir|27|
SAS|573|10024|11270|P|Bat|11|
SAS|573|10024|10968|R|PanA FDMx|10|
SAS|573|10024|10950|R|Mac|2|

O/P
WOR|573|10024|2853369,11358|P|
WOR|573|10024|11101,11067|R|
SAS|573|10024|2853369,11358,11270|P|
SAS|573|10024|11101,11067|R|

Thanks,
Dhams
# 2  
Old 08-21-2007
nawk -f dhams.awk myFile

dhams.awk:
Code:
BEGIN {
   FS=OFS="|"
   SEPcomma=","
   }

{
        NF=5
        idx= $1 SUBSEP $5
        fld4[idx] = ( idx in fld4 ) ? fld4[idx] SEPcomma $4 : $4
        $4=fld4[idx]
        arr[idx]=$0
}
END {
   for(i in arr)
           print arr[i] OFS
}

# 3  
Old 08-22-2007
Thanks much vgersh99 for your help.
# 4  
Old 08-22-2007
one more question
Hi Vgersh99,

What i have to do to accomplish the below?

If suppose field 2 has different values in some of the lines and has to be considered for grouping the field 4 (comma separated) along with the field 1 and field 5?

Sample I/P:
WOR|573|10024|2853369|P|Sto|1|
WOR|573|10024|11358|P|Bir|27|
WOR|570|10024|2853369|P|Sto|1|
WOR|570|10024|11358|P|Bir|27|
WOR|573|10024|11101|R|Fta|14|
WOR|573|10024|11067|R|Col|11|

O/P:
WOR|573|10024|2853369,11358|P|
WOR|570|10024|2853369,11358|P|
WOR|573|10024|11101,11067|P|
Last edited by dhams; 08-22-2007 at 06:25 PM..
# 5  
Old 08-23-2007
try
Hi, hope this one can give you some help. Actually, this code will not fullfill your requirements. But i think it will be helpful.


Code:
awk 'BEGIN{FS="|"}
{
        if (arr[$1$5]=="")
        {
                arr[$1$5]=sprintf("%s|%s|%s|%s|%s",$1,$2,$3,$4,$5)
        }
        else
        {
                split(arr[$1$5],temp,"|")
                t_num=1
                str=""
                for (j=1;j<=5;j++)
                {
                        if (j==4)
                        str=sprintf("%s,%s|",str,temp[j])
                        else
                        str=sprintf("%s|%s",str,temp[j])
                }
                arr[$1$5]=sprintf("%s",str)
        }

}
END{
for (i in arr)
print arr[i]
}' filename

# 6  
Old 08-24-2007
if you want to group by 'field1, field5 and field2' instead of the old 'field1 and field5' - it should be pretty easy to do given the original code. It's a one-line change for indexing.
Give it a try.....
# 7  
Old 08-24-2007
try this code ;
sort -t '|' -k1,1 -k5,5 -k2,2 inputfile| awk -F'|' -v lastline="`wc -l inputfile |awk '{print $1}'`" -v f1="" -v f2="" -v f3="" -v f4="" -v f5="" '{
if ( ( f1 == $1 ) && ( f2 == $2 ) && ( f5 == $5 ) ) { f4=f4","$4 ;
if ( NR == lastline ) { msg=f1"|"f2"|"f3"|"f4"|"f5"|";print msg; } ;
}
else
{
if ( f1 != "" )
{ msg=f1"|"f2"|"f3"|"f4"|"f5"|";print msg;}
f1=$1;f2=$2;f3=$3;f4=$4;f5=$5;
}
} ' >lastout

/* I couldn't find EOF "END OF FİLE" in awk and I fused last line. if there is
you can use for example if ( NR == EOF ) {.... or if ( EOF ) { .... */
it run correctly in korn shell
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk manipulation

hello I have example file AA 11 BB 22 CC 33 And what I expect to have -a AA=11 -a BB=22 -a CC=33 can anyone help how I have this using awk? (1 Reply)
Discussion started by: vikus
1 Replies

2. Shell Programming and Scripting

awk manipulation

Hallo Family, I have csv file which has over a million records in it. All i want to do is to change field 2 to have the same value as field 10. sample file:Now 0860093239,Anonymous,unconditional,+27381230283,Anonymous,unconditional,y,public,,2965511477:0A Desired output: ... (2 Replies)
Discussion started by: kekanap
2 Replies

3. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

4. UNIX for Dummies Questions & Answers

File manipulation via awk

Hello, I am having issues generating the output file below from this input file: Basically, what I want is if the ID= matches with the line below to print the first value in column 3 and the last value of column 4 for the matching ID's. The ID's can repeat more than twice, however, they... (2 Replies)
Discussion started by: verse123
2 Replies

5. Shell Programming and Scripting

File manipulation in awk

I have got a sample file below(colon(:) is the field separator) . The data is like col1:col2:col3:col4:col5:col6:col7:col8:col9:col10 11:12:012:aa:a a a:10::111:12: 311:321:320:caad::321:31:3333:: 2:22:222::bbb::cads::2222:20 :::::12:1234::12: :5:55::555:5555::::55550 Now I want to find... (9 Replies)
Discussion started by: rinku11
9 Replies

6. Shell Programming and Scripting

SED/AWK file read & manipulation

I have large number of data files, close to 300 files, lets say all files are same kind and have extension .dat , each file have mulitple lines in it. There is a unique line in each file containing string 'SERVER'. Right after this line there is another line which contain a string 'DIGIT=0',... (4 Replies)
Discussion started by: sal_tx
4 Replies

7. Shell Programming and Scripting

File manipulation with AWK and SED

Hello How do i check that correct input files are used while using AWk and SED for file manipulation? e.g awk '/bin/ {print $0 }' shell.txt sed 's/hp/samsung/' printers.txt how do i ensure that the correct input files I am working with are used? (5 Replies)
Discussion started by: Pauline mugisha
5 Replies

8. Shell Programming and Scripting

$0 manipulation in awk

OK, so if $0 represent the entire record... can I change $2 and will that be reflected back in $0? I think the following answers that YES, it does work. But is there anything I should be thinking about prior to doing this? What I am actually doing is part of 5 pages of scripting and awk... (1 Reply)
Discussion started by: joeyg
1 Replies

9. Shell Programming and Scripting

File manipulation using AWK

Hi All, I have a file having content, $ cat data1.txt 20060620 142 62310 959400 A 5.00 20060620 142 62310 959400 B 3.00 20060620 143 62310 959401 A 7.00 20060620 143 62310 959401 B 4.00 20060620 144 62310 959402 A 8.00 20060620 144 62310... (6 Replies)
Discussion started by: rinku11
6 Replies

10. Shell Programming and Scripting

awk manipulation

Hi , what a wonderful command but so hard to maintain ! i have a file like that : 03/07/2006 05:58:45 03/07/2006 06:58:45 03/07/2006 07:58:50 03/07/2006 08:58:50 and i want to read it and keep only the lines with 3rd field less than 07:00:00 writing it in a second file ! ... (2 Replies)
Discussion started by: Nicol
2 Replies
Login or Register to Ask a Question