Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Find fields and replace using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find fields and replace using awk
# 8  
Old 11-19-2013
Code:
mute@thedoctor:~/temp/roozo$ ./script
aster;PAGE;1234;4;KART
aster;PAGE;2355;4;HOME
aster;PAGE;1234;4;KAR2
aster;PAGE;1234;4;MART
aster;PAGE;1111;4;DART

Code:
#!/bin/sh
awk '
        FNR==1{file++}
        file==1{a[$2]=$1;next}
        file==2{b[$2]=$1;next}
        {
                for (i in a) {
                        print $1,$2,b[a[i]],$4,i
                }
        }
' config input2 FS=';' OFS=';' input1

This User Gave Thanks to neutronscott For This Post:
Roozo
# 9  
Old 11-19-2013
For input in post #6 and order is important then you may try

Code:
$ cat config.txt 
A DART
B MART
B KART
B KAR2
C HOME

$ cat input2.txt 
1111 A
1234 B
2355 C

$ cat input1.txt 
aster;PAGE;1233;4;1222

Code:
awk -F'[ ;]' '
              FNR == 1{
                        ++i
                      } 
                i == 1{
                         R1[$2]=$1
                      } 
    i==2 && ($1 in R1){
                          S[FNR]= R1[$1] OFS $2
                          s = FNR
                      } 
               i == 3 {
                            for(j =1; j<=s ; j++){
                                                      split(S[j],A,OFS)
                                                      $3 = A[1]
                                                      $5 = A[2]    
                                                      print $0
                                                 }
                      } 
              ' OFS=";" input2.txt config.txt input1.txt

Resulting
Code:
aster;PAGE;1111;4;DART
aster;PAGE;1234;4;MART
aster;PAGE;1234;4;KART
aster;PAGE;1234;4;KAR2
aster;PAGE;2355;4;HOME

This User Gave Thanks to Akshay Hegde For This Post:
Roozo
# 10  
Old 11-20-2013
Thanks Akshay...
Code:
$ cat config.txt 
A DART
B MART
B KART
B KAR2
C HOME
 
$ cat input2.txt 
1111 A
1234 B
9999 B
2355 C
 
$ cat input1.txt 
aster;PAGE;1233;4;1222

when I provide more than one(like below), it considering only last field "9999 B" and giving me the result.
Code:
$ cat input2.txt 
1111 A
1234 B
9999 B
2355 C
 
Out: output only for last parsed "9999"
aster;PAGE;1111;4;DART
aster;PAGE;9999;4;MART
aster;PAGE;9999;4;KART
aster;PAGE;9999;4;KAR2
aster;PAGE;2355;4;HOME

And I tried this but "$4" increment failed in output. Not getting the below output
Code:
Input3.txt 
1111 2
1234 1
9999 3
2355 4
 
Output: 
aster;PAGE;1111;3;DART
aster;PAGE;1234;2;MART
aster;PAGE;1234;3;KART
aster;PAGE;1234;4;KAR2
aster;PAGE;9999;4;MART
aster;PAGE;9999;5;KART
aster;PAGE;9999;6;KAR2
aster;PAGE;2355;5;HOME

And Akshay, can you explain the command which postin ?
# 11  
Old 11-20-2013
wow this is just getting more and more complex! This was fun. Would be more understandable if I knew what I was working with and could name the variables something..

Code:
mute@thedoctor:~/temp/roozo$ ./script
aster;PAGE;1111;3;DART
aster;PAGE;1234;2;MART
aster;PAGE;1234;3;KART
aster;PAGE;1234;4;KAR2
aster;PAGE;9999;4;MART
aster;PAGE;9999;5;KART
aster;PAGE;9999;6;KAR2
aster;PAGE;2355;5;HOME

Code:
#!/bin/sh
awk '
  FNR==1{file++}
  file==1{if (!d[$1]) a[b++]=$1;c[$1,d[$1]++]=$2;next}
  file==2{e[$2,f[$2]++]=$1;next}
  file==3{g[$1]=$2;next}
  {
    for (i=0;i<b;i++)
      for (j=0;e[a[i],j];j++)
        for (k=0;c[a[i],k];k++)
          print $1,$2,e[a[i],j],++g[e[a[i],j]],c[a[i],k]
  }
' config input2 input3 FS=';' OFS=';' input1

This User Gave Thanks to neutronscott For This Post:
Roozo
# 12  
Old 11-20-2013
Neutronscott it Working good Smilie
I just started working in awk and tried more examples...
Could you explain the script so that would help me to understand more about awk ?
# 13  
Old 11-20-2013
Quote:
Originally Posted by Roozo
Could you explain the script so that would help me to understand more about awk ?
I really wish I could but I made it rather obscure! Here I try to comment the code..

Code:
#!/bin/sh
awk '
  #each time we encounter line 1 of a file, increment the "file"
  #variable, so we know which file we are in.
  FNR==1{file++}

  #inside file1 (config), we keep the order of first column (i.e: A,B,C)
  #if not a duplicate (a key does not exist in d[] yet), we omit adding to a[]
  #and incrementing b. so that
  #A,B,B,B,C looks like
  # a[0]=A
  # a[1]=B
  # a[2]=C
  #That will make d[] (which is the count of each individually):
  # d[A]=1
  # d[B]=3
  # d[C]=1
  #and finally the meat of our work, the c[] array:
  # c[A,0]=DART
  # c[B,0]=MART
  # c[B,1]=KART
  # c[B,2]=KAR2
  # c[C,0]=HOME
  file==1{if(!d[$1])a[b++]=$1;c[$1,d[$1]++]=$2;next}

  #input2: maps the letters to a number (phone extention?)
  #we simply stuff it all in another array, e[]
  #using f[] like we used d[] before to keep count of each individually
  # e[A,0]=1111
  # e[B,0]=1234
  # e[B,1]=9999
  # e[C,0]=2355
  file==2{e[$2,f[$2]++]=$1;next}

  #input3: simplest one. hold a count for each number, with the
  #starting number from input3
  # g[1111]=2
  # g[1234]=1
  # g[9999]=3
  # g[2355]=4
  file==3{g[$1]=$2;next}

  #this is our processing function. we stored all the data we need into
  #arrays. now lets do work. and a lot of confusing work!
  #there is no condition listed. so this block executes for every line
  #(where file>3 since those condition blocks used "next" to skip all later
  #blocks from processing).
  {
    #i will be used to index a[]
    #go through each letter (A,B,C) in the order obtained
    for (i=0;i<b;i++)
      #j indexes e[], which mapped i.e. A->1111
      for (j=0;e[a[i],j];j++)
        #k indexes c[], which mapped i.e. A->DART
        for (k=0;c[a[i],k];k++)
          print $1,$2,e[a[i],j],++g[e[a[i],j]],c[a[i],k]
  }
#we start with default FS and OFS, but change to FS=; only for input1
' config input2 input3 FS=';' OFS=';' input1

# 14  
Old 11-20-2013
for input posted in #10

Code:
$ cat input1.txt 
aster;PAGE;1233;4;1222

$ cat input2.txt 
1111 A
1234 B
9999 B
2355 C

$ cat input3.txt 
1111 2
1234 1
9999 3
2355 4

$ cat config.txt 
A DART
B MART
B KART
B KAR2
C HOME

Code:
awk -F'[ ;]' '
              # File Counter
              FNR == 1{
                        ++i
                      } 

              # File1 on statements goes here....
                i == 1{
                         R[1,FNR] = $1 ; R[2,FNR] = $2; d = FNR
                      } 

              # File2 on statements goes here....
                i == 2{ 
                              for(j = 1; j <= d; j++)
                                             if(R[1,j] ~ $2)
                                                    S[++s] = R[2,j] OFS $1          
                       } 

              # File3 on statements goes here....
                i == 3{
                          c[$1]=$2
                      }

              # File4 on statements goes here....
                i == 4{
                            for(j = 1; j <= s ; j++){
                                                      split(S[j],A,OFS)
                                                      $3 = A[2]
                              $4 = ++c[$3]
                                                      $5 = A[1]     
                                                      print $0
                                                 }
                      } 
              ' OFS=";" config.txt input2.txt input3.txt input1.txt

OR
processing in END block

Code:
awk -F'[ ;]' '
           FNR==1{++i}{LC[i]=FNR}{for(j=1;j<=2;j++)A[i,FNR,j]=$j}
              END{
                           for(j=1;j<=LC[2];j++)
                           for(i=1;i<=LC[1];i++)
                                if(A[2,j,2]~A[1,i,1]){
                                               for(k=1;k<=LC[3];k++){
                                                                     n = A[3,k,2]
                                                                     if(A[2,j,1] == A[3,k,1]){
                                                                                              ++n
                                                                                              print A[4,1,1],A[4,1,2],A[2,j,1],n,A[1,i,2]                
                                                                                             }
                                                                     A[3,k,2] = n
                                                                    }
                        
                                                     }
        
                 }
              ' OFS=";" config.txt input2.txt input3.txt input1.txt

Code:
aster;PAGE;1111;3;DART
aster;PAGE;1234;2;MART
aster;PAGE;1234;3;KART
aster;PAGE;1234;4;KAR2
aster;PAGE;9999;4;MART
aster;PAGE;9999;5;KART
aster;PAGE;9999;6;KAR2
aster;PAGE;2355;5;HOME

Last edited by Akshay Hegde; 11-20-2013 at 04:40 AM.. Reason: Some more code
This User Gave Thanks to Akshay Hegde For This Post:
Roozo
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

find pattern matches in consecutive lines in certain fields-awk

I have a text file with many thousands of lines, a small sample of which looks like this: InputFile:PS002,003 D -1 5 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 6 6 -1 -1 -1 -1 0 509 0 PS002,003 PSQ 0 1 7 18 1 0 -1 1 1 3 -1 -1 ... (5 Replies)
Discussion started by: jvoot
5 Replies

2. Shell Programming and Scripting

Find and replace in awk

I have a file that I am trying to find a specific word, then replace text within that string. file TestA2015 TestB2016 Example. Replace TestB2016 to TestB0000, so if TestB is found replace the original "2016" to "0000". Thank you :). awk tried awk '{ sub(/TestB$/, "0000", $6) }1'... (5 Replies)
Discussion started by: cmccabe
5 Replies

3. Shell Programming and Scripting

Compare 2 files and find missing fields awk

Hello experts! I have 2 files. file1 is a list file containing uniquely names. e.g.: name1 number number name2 number number name5 number number name10 number number ... file2 is a data file arbitrary containing the names of file1 in paragraphs separated by "10" e.g. name4 ... (3 Replies)
Discussion started by: phaethon
3 Replies

4. Shell Programming and Scripting

Replace 0 with 1 in multiple fields with awk

Hello, I have the following input file: 1 3 3 2 3 3 4 0 4 0 5 4 5 2 2 0 5 3 4 0 6 0 3 2 I am trying to remove all zeroes in fields 2 and 4 and replace them with "1's" I tried the following, but it's not working awk -F"\t" '{ if (($2==0) || ($4==0) $2=1; $4=1; print $0 ) }' input ... (8 Replies)
Discussion started by: Rabu
8 Replies

5. Shell Programming and Scripting

Find and Replace in awk

Friends, I have more the thousand lines like this. check.cloud1.port=342 check.cloud2.port=5456 check.cloud3.port-4564 But we need to transfer it to _CHECK.CLOUD1.PORT_=342 _CHECK.CLOUD2.PORT_=5456 _CHECK.CLOUD3.PORT_=4564 Any one could pls help of this. Thanks in Advance ... (1 Reply)
Discussion started by: jothi basu
1 Replies

6. Shell Programming and Scripting

Find and Replace in multiple fields using awk

Hi, Say I have a record "1|22| | |". In which the third and fourth fields are <space> alone. I have to replace the <Space> with <null>. Input: "1|22| | |" --> "1|22|<space> |<space> |" Expected output: "1|22|||" --> "1|22|<null> |<null>|" I tried: echo "1|22| | |" | awk -F... (4 Replies)
Discussion started by: machomaddy
4 Replies

7. Shell Programming and Scripting

AWK: merge two files and replace some fields

Need some code tweak: awk 'END { for (i=1; i<=n; i++) if (f2]) print f2] } NR == FNR { f2 = $1] = $0 next } $1 in f2 { delete f2 }1' FS=, OFS=, 2.csv 1.csv > 3.csvfile 1.csv have: $1,$2,$3,$4,$5,$6,$7,$8,$9...... file 2.csv have: $1,$2,$3,$4,$5,$6 (2 Replies)
Discussion started by: u10
2 Replies

8. Shell Programming and Scripting

use awk to replace empty fields with the latest nonempty field

Hi suppose I have a csv file like this count,1977,1978,1979 usa, , , blue japan, red, yellow,green india, , yellow,blue china, blue, yellow, green I want the output to be(replace everything, including empty data, with the most recent data): ... (1 Reply)
Discussion started by: grossgermany
1 Replies

9. Shell Programming and Scripting

awk find/replace

Greetings all. I have web site that has long option and switch lists. When I insert something new into these files, the lists need to be reordered. IE: 1 => apple 2 => pear 3 => bannana 4 => orange --------------------- Add grape as #2 1 => apple 2 => grape 3 => pear 4 =>... (2 Replies)
Discussion started by: RobertSubnet
2 Replies

10. UNIX for Dummies Questions & Answers

How do I read/find/replace fields in a csv datafile?

hello. I'm somewhat a novice here so please be patient. My stumbling block when loading csvs into ORACLE tables is this: I need to read a csv datafile, check several fields in each line, and if any of stated fields contain A ZERO only then replace it with a null/blank character. I had a... (9 Replies)
Discussion started by: MrCarter
9 Replies
Login or Register to Ask a Question