Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Fill the Key fields : Please help us

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fill the Key fields : Please help us
# 1  
Old 03-30-2008
Fill the Key fields : Please help us
Hi ....

we are having the below file .Column 1, Column 2 ,column 3 are key fields...
In the below ...for 2 nd , 3 rd row the repeated key column is missing ....

i want the new file to be populated with all missing key columns.


E100,0,5/29/1993,0,E001,E000,A,500000,41666.667,240.384615
_____0,1/23/1994,0,E001,E003,A,125400,10450,60.288462
_____0,6/4/1994,0,E001,E003,A,95000,7916.667,45.673077
E101,0,7/30/1993,0,E001,E003,A,87000,7250,41.826923
______ 1/9/1993,0,E001,E003,A,45200,3766.667,21.730769
E103,0,2/3/1995,0,E001,E003,A,15000,1250,7.211538
E104,0,2/15/1995,0,E001,E001,A,78000,6500,37.5


I want the result to be :


E100,0,5/29/1993,0,E001,E000,A,500000,41666.667,240.384615
E100, 0,1/23/1994,0,E001,E003,A,125400,10450,60.288462
E100, 0,6/4/1994,0,E001,E003,A,95000,7916.667,45.673077
E101,0,7/30/1993,0,E001,E003,A,87000,7250,41.826923
E101,0,1/9/1993,0,E001,E003,A,45200,3766.667,21.730769
E103,0,2/3/1995,0,E001,E003,A,15000,1250,7.211538
E104,0,2/15/1995,0,E001,E001,A,78000,6500,37.5



Please help me ........
# 2  
Old 03-30-2008
Write an awk script which remembers the fields from the previous line, and adds as many to the front as are missing to form a full line. Assume the first line indicates how many fields a full line should have. Read up on array handling in the awk documentation.
# 3  
Old 03-31-2008
hi era
Quote:
Originally Posted by era
Write an awk script which remembers the fields from the previous line, and adds as many to the front as are missing to form a full line. Assume the first line indicates how many fields a full line should have. Read up on array handling in the awk documentation.
I am new to this awk command ...........can you give a sample code on this requirment....that would be a helpful.....

Thanks your response
charan
# 4  
Old 03-31-2008
Code:
>cat file
E100,0,5/29/1993,0,E001,E000,A,500000,41666.667,240.384615
0,1/23/1994,0,E001,E003,A,125400,10450,60.288462
0,6/4/1994,0,E001,E003,A,95000,7916.667,45.673077
E101,0,7/30/1993,0,E001,E003,A,87000,7250,41.826923
1/9/1993,0,E001,E003,A,45200,3766.667,21.730769
E103,0,2/3/1995,0,E001,E003,A,15000,1250,7.211538
E104,0,2/15/1995,0,E001,E001,A,78000,6500,37.5

Code:
>awk 'NF!=10{for(i=1;i<=(10-NF);i++)printf("%s,",k[i]);print;next}{for(i=1;i<=3;i++)k[i]=$i}1' FS=',' f1
E100,0,5/29/1993,0,E001,E000,A,500000,41666.667,240.384615
E100,0,1/23/1994,0,E001,E003,A,125400,10450,60.288462
E100,0,6/4/1994,0,E001,E003,A,95000,7916.667,45.673077
E101,0,7/30/1993,0,E001,E003,A,87000,7250,41.826923
E101,0,1/9/1993,0,E001,E003,A,45200,3766.667,21.730769
E103,0,2/3/1995,0,E001,E003,A,15000,1250,7.211538
E104,0,2/15/1995,0,E001,E001,A,78000,6500,37.5

# 5  
Old 03-31-2008
Hi Klashxx,

can you explain the logic used in for loop?

awk 'NF!=10{for(i=1;i<=(10-NF);i++)printf("%s,",k[i]);print;next}{for(i=1;i<=3;i++)k[i]=$i}1' FS=',' f1


Thanks
Ashish
# 6  
Old 03-31-2008
It does pretty much what I suggested, except it hard-codes the number of expected fields to 10. You could count the number of fields on the first line and use that on subsequent lines for a slightly more general approach. It remembers $1 in k[1], $2 in k[2], and $3 in k[3]; and if the line has too few fields, prints what's in k[1], k[2], etc from the previous line as many as is required. This explanation is backwards relative to the script because it prints first, and then remembers for the next line.
# 7  
Old 03-31-2008
Here's an attempt at generaliziing this.

Code:
awk 'NR==1{ for (i=1; i<=NF; ++i) { k[i]=$i } keep=NF; print; next}
NF!=keep{ for(i=1;i<=keep-NF;++i)printf("%s,",k[i]); print;
                   for(j=i;j<=keep;++j) { n=j-i+1;k[j]=$n}; next; }
{ for (i=1;i<=NF;i++) k[i]=$i; print}' FS=',' f1

Completely untested but might even work. Note the addition of the code to copy down stuff from a line which was too short, so if you have lines with successively more information missing, that too should work now. But again, I didn't test this code properly.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies

2. UNIX for Dummies Questions & Answers

Fill fields with awk from an array?

Hi experts, I have been trying for a while to accomplish the following task using awk, and I just don't seem find find a way. I am not particular about using awk, it just seemed like the logical choice at first. I have a file that contains 5 fields that are delimited by a space character.... (1 Reply)
Discussion started by: GermanicGalore
1 Replies

3. Solaris

Solaris 8 ssh public key authentication issue - Server refused our key

Hi, I've used the following way to set ssh public key authentication and it is working fine on Solaris 10, RedHat Linux and SuSE Linux servers without any problem. But I got error 'Server refused our key' on Solaris 8 system. Solaris 8 uses SSH2 too. Why? Please help. Thanks. ... (1 Reply)
Discussion started by: aixlover
1 Replies

4. Shell Programming and Scripting

Compare Fields from two text files using key columns

Hi All, I have two files to compare. Each has 10 columns with first 4 columns being key index together. The rest of the columns have monetary values. Using Perl, I want to read one file into hash; check for the key value availability in file 2; then compare the values in the rest of 6... (2 Replies)
Discussion started by: Sangtha
2 Replies

5. Shell Programming and Scripting

Perl function to sort a file based on key fields

Hi, I am new to PERL.I want to sort all the lines in a file based on 1,2 and 4th filelds. Can U suggest me a command/function in perl for this operation.. (5 Replies)
Discussion started by: karthikd214
5 Replies

6. Shell Programming and Scripting

Matching by key fields

I have a file (key.dat) that contains two columns: AA|1234| BB|567| CC|8910| I have another file (extract.dat) that contains some data: SD|458|John|Smith| AA|3345|Frank|Williams| AA|1234|Bill|Garner| BD|0098|Yu|Lin| BB|567|Gail|Hansen| CC|8910|Ken|Nielsen| I want to compare the... (5 Replies)
Discussion started by: ChicagoBlues
5 Replies

7. UNIX for Dummies Questions & Answers

Pressing backspace key simulates enter key

Hi, Whenever i press the backspace key, a new line appears, i.e. it works like a enter key. :confused: Thanks (2 Replies)
Discussion started by: vibhor_agarwali
2 Replies

8. Shell Programming and Scripting

align several fields and fill spaces with zero

hi all, i have a big problem, and i don“t know what to do. i have a flat file with several fields, which are separated by ";" like this: 5656838-7B;97030000-7;*;V16106133 ;1;1; 4612062-0B;97030000-7;*;C14038149 ;1;2; 8044938-0B;97030000-7;*;V16034219 ;1;2; where B is a blank space. ... (2 Replies)
Discussion started by: DebianJ
2 Replies

9. Solaris

Esc key/Auto-fill

I'm an HPUX/Linux guy, who appreciates occasionally hitting the Esc key for Auto-fill... Does sunOS have anything like this? This is my 1st post, and I didn't want to waste it :D thanks, manuel (3 Replies)
Discussion started by: mr_manny
3 Replies
Login or Register to Ask a Question