awk help

Shell Programming and Scripting
1

i have a file like

 
col1, col2, col3, col4, col5, col6,col7,col8
a,b,c,d,e,1,t,11
b,e,,w,,1,,
c,r,c,d,e,1,t,11
d,f,c,g,w,1,e,22
e,f,c,d,e,1,w,33
f,e,f,r,t,1,t,22
g,h,t,d,e,2,s,22
h,t,,,e,2,f,
a,r,,,w,2,,
f,r,r,d,e,2,f,44
e,b,h,d,t,2,d,55
a,r,h,d,e,2,g,66
a,b,c,d,e,2,44
a,b,c,d,e,2,,
a,b,c,d,e,2,
a,b,c,d,e,2,55

I am concenered with specific cols -- (col6, col8) -- (1,11)

what i need to do is make out a new file out of this containing unique records of (col6,col8) excluding spaces in col8.

output of the above sample is:

 
1,11
1,22
1,33
2,44
2,55
2,66

this removes the duplicates, but how to ignore the blank of col8,

i tried this

 
nawk -F, '!a[$6,$8]++ {print $6 "," $8}' sample.dat

but it gives this output

 col6,col8
,
1,11
1,
1,22
1,33
2,22
2,
2,44
2,55
2,66

what is that extra , after first line and how can i ignore blanks of col8.
pls help

2

Could this help you?

awk -F"," '$6!="" && $8!="" && !a[$6FS$8] {a[$6FS$8]++;print $6FS$8}' inputfile

OR

#!/usr/bin/perl

use strict;
my (@fields,%hash);

while (<DATA>) {
chomp;
@fields=split(/,/);
if ($fields[5] !="" && $fields[7]!="") {$hash{$fields[5].",".$fields[7]}++;}
}

print $_,"\n" foreach (sort(keys(%hash)));

__DATA__
a,b,c,d,e,1,t,11
b,e,,w,,1,,
c,r,c,d,e,1,t,11
d,f,c,g,w,1,e,22
e,f,c,d,e,1,w,33
f,e,f,r,t,1,t,22
g,h,t,d,e,2,s,22
h,t,,,e,2,f,
a,r,,,w,2,,
f,r,r,d,e,2,f,44
e,b,h,d,t,2,d,55
a,r,h,d,e,2,g,66
a,b,c,d,e,2,44
a,b,c,d,e,2,,
a,b,c,d,e,2,
a,b,c,d,e,2,55
3

Hi mukeshguliao,

Excluding blank in Col6 and Col8

awk -F"," '$6 && $8~/./{print $6","$8}'

If it is needed to exclude blank only in Col 8:

awk -F"," '$8~/./{print $6","$8}'

Regards

4

yes this works for the sample i provided...
but cant figure out it is failing to skip the last col's blank record in my original file..

here is the chunk of file

ASSETCLASS|ASSETTYPE|SETTLEMENTDATE|HOLIDAYCENTERNAMES|VALUATIONBASIS|RIGHTVALUATIONBASIS|CONTRACTUALREUSERIGHTS|APPLICABLEPARTY|RATENAME|BPSPREAD|BPSPREADEFFECTIVE|MATURITYDATE|CURRENCY|COUNTRY|IDENTIFIERS|SECUCREDITRATING|ISSUERS|FROMTIME|TOTIME|VALUATIONPERC|DESCRIPTION|REHYPOTYPE|REHYPORIGHTS|EXTID|ORGID|ICI_ID
Government Bonds|Govt-United States|T1|New York City|98|0|1|P||||||||||0|1|98|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Government Bonds|Govt-United States|T1|New York City|98|0|1|P||||||||||1|10|95|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Government Bonds|Govt-United States|T1|New York City|98|0|1|P||||||||||5|10|90|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Mortgage Backed Securities|MBS-US FNMA (FANNIE MAE)|T1|New York City|98|0|1|C||||||||||0|99|98|TMC MTGE FD C2047|Bilateral|Y|2047|6110230|2530084990
Government Bonds|Govt-United States|T1|New York City|98|0|1|C||||||||||0|1|98|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Government Bonds|Govt-United States|T1|New York City|98|0|1|C||||||||||5|10|90|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Government Bonds|Govt-United States|T1|New York City|98|0|1|C||||||||||1|10|95|BLACK DIAMOND DBL C88|Bilateral|Y|88|1449041|2530016690
Mortgage Backed Securities|MBS-US FNMA (FANNIE MAE)|T1|New York City|98|0|1|C||||||||||0|0|98|TMC MTGE FD C2047|Bilateral|Y|2047|6110230|2530084990
Mortgage Backed Securities|MBS-US FHLMC (FREDDIE MAC)|T1|New York City|98|0|1|C||||||||||0|99|98|TMC MTGE FD C2047|Bilateral|Y|2047|6110230|2530084990
Government Bonds|Govt-France|T2|Target|98|0|1|C||||||||||1|10|98|NORDEA BK DENMARK C2044|Bilateral|Y|2044|640|ENORBKNRX
Agency Bonds|Agency-US FHLB (FED HOME LOAN BK)|T1|New York City|98|0|1|C||||||||||1|5|97|BR (NRRIT) C16240|Bilateral|Y|16240|6591472|2530028790
Agency Bonds|Agency-US FHLMC (FREDDIE MAC)|T1|New York City|98|0|1|P||||||||||0|1|95|SOWOOD ALPHA LP C5326|Bilateral|Y|5326|6599026|2530081290
Government Bonds|Govt-France|T2|Target|98|0|1|C||||||||||0|1|98|PENSFORMAGOGPSYK C5374|Bilateral|Y|5374|6541495|C265909
Government Bonds|Govt-France|T2|Target|98|0|1|C||||||||||1|10|95|PENSFORMAGOGPSYK C5374|Bilateral|Y|5374|6541495|C265909
Government Bonds|Govt-France|T2|Target|98|0|1|C||||||||||10|0|90|PENSFORMAGOGPSYK C5374|Bilateral|Y|5374|6541495|C265909
Government Bonds|Govt-Great Britain|T2|London|98|0|1|P||||||||||10|0|95|WELL37N9 C45423|Bilateral|Y|45423|7100736|
Government Bonds|Govt-Great Britain|T2|London|98|0|1|P||||||||||5|10|97|WELL37N9 C45423|Bilateral|Y|45423|7100736|
Government Bonds|Govt-France|T2|Target|98|0|1|C||||||||||0|1|100|WELL37N9 C45423|Bilateral|Y|45423|7100736|

and here is what i am executing as per you:

 
nawk -F"|"  '$24!="" && $26!="" && !a[$24FS$26] {a[$24FS$26]++;print $24FS$26}' file.dat

output i am getting is very close but blanks are not ignored!!? :wall:

 
EXTID|ICI_ID
88|2530016690
2047|2530084990
2044|ENORBKNRX
16240|2530028790
5326|2530081290
5374|C265909
45423|
5

Try in general with:

awk -F"|" '$NF~/./{print $(NF-2)","$NF}' inputfile
EX TID,ICI_ID
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
2047,2530084990
2044,ENORBKNRX
16240,2530028790
5326,2530081290
5374,C265909
5374,C265909
5374,C265909

Or more precisely using fixed columns numbers:

awk -F"|" '$26~/./{print $24","$26}' inputfile
EX TID,ICI_ID
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
2047,2530084990
2044,ENORBKNRX
16240,2530028790
5326,2530081290
5374,C265909
5374,C265909
5374,C265909

Regards

6

I think you have space in the last column.
try this,

awk -F"|"  '$24 ~/[[:alnum:]]/ && $26~/[[:alnum:]]/ && !a[$24FS$26] {a[$24FS$26]++;print $24FS$26}' file.dat
7

@pravin
there is no space though.. its just delimited with |. New line after that..

this doesnt return anything at all :frowning:

awk -F"|"  '$24 ~/[[:alnum:]]/ && $26~/[[:alnum:]]/ && !a[$24FS$26] {a[$24FS$26]++;print $24FS$26}' file.dat

@cgkmal
i think this should work, but
still returns lines with blanks of $26

getting output

awk -F"|" '$26~/./{print $24","$26}' OTCeg.dat
EXTID,ICI_ID
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
2047,2530084990
2044,ENORBKNRX
16240,2530028790
5326,2530081290
5374,C265909
5374,C265909
5374,C265909
45423,
45423,
45423,
8

Could you please attached your input file.

9

there is one more thing..
when i run with "awk" i get an error

 
awk -F"|" '$26~/./ && !a[$24FS$26] {a[$24FS$26]++;print $24FS$26}' OTCeg.dat 
awk: syntax error near line 1
awk: bailing out near line 1

I am running this with "nawk"

 
nawk -F"|" '$26~/./ && !a[$24FS$26] {a[$24FS$26]++;print $24FS$26}' OTCeg.dat
EXTID|ICI_ID
88|2530016690
2047|2530084990
2044|ENORBKNRX
16240|2530028790
5326|2530081290
5374|C265909
45423|

is this creating a problem??

10

here is the input file attached..

but i guess the problem is with nawk/awk

m using ksh shell, cant change it coz the job is a part of another huge script written for ksh shell.

11

Your file is a DOS file with Ctl-M at the end of each line.

12

Hi mukeshguliao,

Due to the the file you uploaded it has DOS newlines(CR/LF) format, It was needed to convert that endings to UNIX format
before (adding {sub(/\r$/,"")})to search only last columns without blanks as condition.

The modified code is as follow:

awk -F"|" '{sub(/\r$/,"")} $NF~/./{print $(NF-2)","$NF}' inputfile
EXTID,ICI_ID
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
88,2530016690
88,2530016690
88,2530016690
2047,2530084990
2047,2530084990
2044,ENORBKNRX
16240,2530028790
5326,2530081290
5374,C265909
5374,C265909
5374,C265909

or using field numbers:

awk -F"|" '{sub(/\r$/,"")} $26~/./{print $24","$26}' inputfile

Best regards.

13

yeah this works for me..
just wanted to ask one more thing.. how did u guys figured out this DOS newline format thing.

14 
$ file OTCeg.txt 
OTCeg.txt: ASCII text, with very long lines, with CRLF line terminators

or look for \r \n in od -c filename output

15

thanks man..

16

Hi mukeshguliao,

Of course. Well, I detected something wrong when the original code I use worked with the sample data you put in
the thread body, but didn't work in the expected way with the file you uploaded. Therefore, when I analyse the
format in a text editor and see what happened with that uploaded file, I see that this file had line endings in DOS
format(with carriage return character=CR=\r) and the first sample had line endings with UNIX format (New line character=NL=\n)

But how is verified that?
1-) when I see the file in text editor, at first sight, both characters are represented with a different symbol (new line="�" and carriage return="�").
2.1-) When I do a search within text editor (Ctrl+F, with the "Regular Expresion" option selected), and look for New lines (\n), this character is not found
2.2-) When I do a search within text editor (Ctrl+F, with the "Regular Expresion" option selected), and look for Carriage Return (\r), this character is found
and is verified the supposition about the file possibly did had a line ending with DOS format.

In order you be more clear, below you can see a little table with this conventions and 2 attached images where you can see how it is graphical represented
in text editor both cases.

Escape     Character
\a     audible bell
\b     backspace
\f     formfeed
\n     newline
\r     carriage return
\t     horizontal tab
\v     vertical tab
\ooo     octal value ooo
\xdd     hexadecimal value dd
\/     slash
\"     quote

Hope it helps,

Best regards.