Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Lookup Within a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Lookup Within a file
# 1  
Old 05-03-2014
Lookup Within a file
A text file has two logs of same event & both logs have to be correlated into a single line before same can be processed further

RAW
Code:
OPERATING LOG

Date    Month    Year    Time    Event
10        JAN      2014    1000    4

11        APR      2013    1230    2
12        FEB      2014    1142    4
13        JAN      2015    0132    3

11        FEB      2012    1032    3
END

<Event Description>
Event Description    Remarks
1     test            XXXXX
2     Reset            YYYYY
3     Reboot        ZZZZZ
4     Unspecified    YYYYY
END

Output
Code:
Date    Month    Year    Time    Event    Description    Remarks
10        JAN     2014    10:32   4       Unspecified    YYYYY
11        APR     2013    10:32   2       Reset        YYYYY
12        FEB     2014    10:32   4       Unspecified    YYYYY
13        JAN     2015    10:32   3       Reboot        ZZZZZ
11        FEB      2012    1032   3       Reboot        ZZZZZ

The final column in the first table needs to be looked up in the 2nd table & the matching entry has to be copied to the first table & appended to the end of the Line. Both Tables in the same file.
Attached a raw file for Simulation.
Plz suggest some way Smilieto approach the same ....
test1.txt (262 Bytes) 
# 2  
Old 05-03-2014
Code:
awk '
      # Parse Log
      /^Date/,/END/  {
			# if Date copy line to variable header and go to next line
                        if(/^Date/){
                                       header = $0
                                       next
                                   }

			# if not blank line and no END string copy line to array LOG 
                             if(NF && !/END/)
                                   { 
                                       LOG[++i] = $0 
                                   }
                      }
      # Parse Event  
      /^Event/,/END/  {
			# if Event append line to variable header and go to next line
                        if(/^Event/){ 
                                       header = header OFS $2 OFS $3
                                       next 
                                    } 

			 # if not blank line and no END string copy second and third field to array EVE 
                              if(NF && !/END/)
                                    {  
                                       EVE[$1]  = $2 OFS $3 
                                    } 
                       }
       # Combine...
		  END  {
				 # Print Header 
				print header

				for(j=1;j<=i;j++)
				{
					 # split element into an array A
					n = split(LOG[j],A)

					 # Check last field is exist in associate array EVE,
                                         # if true print elements, else ---- 
					print LOG[j],EVE[A[n]]?EVE[A[n]]:"----"
				}
                       }            
    ' OFS='\t' file

# 3  
Old 05-03-2014
Another way (keeping the original formatting intact, reading the file twice):
Code:
awk '
  /^Event/,/^END/ {
    i=$1
    sub($1,x)
    A[i]=$0
  } 
  /^Date/,/^END/ {
    if(NR>FNR && !/^END/) print $0 A[$NF]
  }
' file file

Output:
Code:
Date    Month    Year    Time    Event Description    Remarks
10        JAN      2014    1000    4     Unspecified    YYYYY

11        APR      2013    1230    2     Reset            YYYYY
12        FEB      2014    1142    4     Unspecified    YYYYY
13        JAN      2015    0132    3     Reboot        ZZZZZ

11        FEB      2012    1032    3     Reboot        ZZZZZ




--
Replace if(NR>FNR && !/END/) with if(NR>FNR && !/END/ && NF) to get rid of empty lines:
Code:
Date    Month    Year    Time    Event Description    Remarks
10        JAN      2014    1000    4     Unspecified    YYYYY
11        APR      2013    1230    2     Reset            YYYYY
12        FEB      2014    1142    4     Unspecified    YYYYY
13        JAN      2015    0132    3     Reboot        ZZZZZ
11        FEB      2012    1032    3     Reboot        ZZZZZ

Last edited by Scrutinizer; 05-03-2014 at 07:35 AM..
This User Gave Thanks to Scrutinizer For This Post:
newageBATMAN
# 4  
Old 05-03-2014
Code:
awk '/^Date/,/^END/ {A[$NF] = $0;
  B[++n] = $NF}
  /^Event/,/^END/ {x = $1;
  sub($1"[ \t]*", "")
  C[x] = $0}
  END{for(i = 1; i < n; i++)
    {if(! A[B[i]] ~ /^[ \t]*$/)
	  {print A[B[i]] "\t" C[B[i]]}}}' file

---------- Post updated at 09:45 AM ---------- Previous update was at 09:42 AM ----------

Code:
awk '/^Date/,/^END/ {if(NF)
  {A[$NF] = $0;
  B[++n] = $NF}}
  /^Event/,/^END/ {x = $1;
  sub($1"[ \t]*", "")
  C[x] = $0}
  END{for(i = 1; i < n; i++)
    {print A[B[i]] "\t" C[B[i]]}}' file

# 5  
Old 05-03-2014
Will work for a single file
Code:
awk '
  /^Event/,/^END/ {
    i=$1
    sub($1,x)
    A[i]=$0
  } 
  /^Date/,/^END/ {
    if(NR>FNR && !/^END/) print $0 A[$NF]
  }
' file file

Appreciate the logic which simplifies the implementation.

1. The code does the job if file name is Specified manually,
but how to process multiple files(60-70 files) at one go as the same file name has to be mentioned twice. Now *.* can not be used ....

2. While executing in Terminal in a single line, Code is running fine, but while trying to run the same Code from a file, its waiting for input ...
Running in Terminal:

Script.sh File1 File1

Code in file "Script.sh"
Code:
awk '/^Event/,/^END/ {i=$1;A[i]=$0;} /^Date/,/^END/ {if(NR>FNR && !/^END/ && NF>4) {printf "%5s %2s %2s %15s %s\n" ,$1,$2,$3,$4,A[$NF]} }'

Last edited by newageBATMAN; 05-03-2014 at 04:23 PM..
# 6  
Old 05-03-2014
Are the event descriptions (contained in every file) the same for every file or can they differ per file and are they only applicable to the operating log in the file itself?


----
At any rate you could do something like:
Code:
for i in *.*; do
  awk '
     /^Event/,/^END/ {
      i=$1
      sub($1,x)
      A[i]=$0
    } 
    /^Date/,/^END/ {
      if(NR>FNR && !/^END/) print $0 A[$NF]
    }
  ' "$i" "$i"
done > outfile


---
Ad. 2. You need to supply it with the files:
Code:
awk '......' "$@"

Last edited by Scrutinizer; 05-03-2014 at 04:29 PM..
This User Gave Thanks to Scrutinizer For This Post:
newageBATMAN
# 7  
Old 06-04-2014
Thanks ...Solved
Quote:
Originally Posted by Scrutinizer
Are the event descriptions (contained in every file) the same for every file or can they differ per file and are they only applicable to the operating log in the file itself?


----
At any rate you could do something like:
Code:
for i in *.*; do
  awk '
     /^Event/,/^END/ {
      i=$1
      sub($1,x)
      A[i]=$0
    } 
    /^Date/,/^END/ {
      if(NR>FNR && !/^END/) print $0 A[$NF]
    }
  ' "$i" "$i"
done > outfile

---
This one worked for me... My final code for others' future reference is as follows

Code:
for i in *.*; do awk '/^Event/,/^END/ {i=$1;A[i]=$0;} /^Date/,/^END/ {if(NR>FNR && !/^END/ && NF>4) {printf "%5s %2s %2s %15s %s\n" ,$1,$2,$3,$4,A[$NF]} }' "$i" "$i"; done>Output.log
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Lookup value from another file

Hi Experts, I need you help in coding the below scenario in unix. Please help Lets say i have below 2 records in my file1 a;b;c a;d and in file 2 , i have the below information a:alpha b:beta c:code d:delta then i want to lookup on file2 and replace my content in file... (2 Replies)
Discussion started by: ashishagg2005
2 Replies

2. Shell Programming and Scripting

Lookup name from another file

Hi All, I want to lookup name for an id in col2 input from another file and add the name to each line. Input 1 comp100001_c0_seq1 At1g31340 30.40 569 384 11 3 1673 313 834 7e-62 237 comp100003_c0_seq1 At1g35370_2 35.00 80 50 ... (7 Replies)
Discussion started by: gina.lizar
7 Replies

3. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files: 1) Third field from file1.txt should be compared to the first field of lookup.txt. 2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt. 3)Else just print the line from file1.txt. File1.txt:... (4 Replies)
Discussion started by: venalla_shine
4 Replies

4. Shell Programming and Scripting

Lookup file

Hi, need your help to lookup these 2 files main.txt RNPMS01,PMS717W_Marasi,CXP9016141/1_R7G04,EXECUTING RNPMS01,RAP765W_BakaranBatu,CXP9014346/1_R6AG03,EXECUTING RNPMS01,RNPMS01,CXP9014711/2_R5Z,EXECUTING RNPMS01,TBT510W_Bandar_Utama,CXP9014346/1_R6AG03,EXECUTING... (8 Replies)
Discussion started by: singgih
8 Replies

5. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies

6. Shell Programming and Scripting

Lookup file

I have two files ,File1 lookup to file2 based on 1st&3rd against 1st and 2nd column and produce the following o/p File1 450000|USD|USD 450006|GKSGD|SGD 450002|XSGD|SGD File2 ----- 450000|USD|2000.00|10000.000 450006|SGD|1000.200|3000.000 450002|SGD|3000.000|20000.00 O/p ... (4 Replies)
Discussion started by: mohan705
4 Replies

7. Shell Programming and Scripting

Lookup on a file

Hi, I have the following requirement. I have one lookup file which contains 15 columns and 7000 records. Ex: 123,MEDICA,134,145,1178,123,678,345,2345,HP,COL,K12,SR,OX,78919 I have input file which contains 14 columns and 20 million records.Some times the record count is more... (4 Replies)
Discussion started by: ukatru
4 Replies

8. UNIX for Advanced & Expert Users

Lookup on a file

Hi, I have the following requirement. I have one lookup file which contains 15 columns and 7000 records. Ex: 123,MEDICA,134,145,1178,123,678,345,2345,HP,COL,K12,SR,OX,78919 I have input file which contains 14 columns and 20 million records.Some times the record count is more than 20... (1 Reply)
Discussion started by: ukatru
1 Replies

9. UNIX for Dummies Questions & Answers

Lookup with a file

Hi All, i have a variable which has a value in it. RETAILER='JEWL' i have a text file. Name: file.txt file.txt ________ WLG 150 JEWL 60 CVS 240 FLN 120 WND 120 I am trying to write a korn script.the script, based on the value in the RETAILER will do a look up against the... (5 Replies)
Discussion started by: pavan_test
5 Replies

10. UNIX for Dummies Questions & Answers

file lookup

I need to do a text lookup for multiple records against a file that contains a key and two results. I found this code: str=`awk '$1 == search' search=$1 lkup.tbl It's a little tricky because the first $1 is the key variables location in the lkup.tbl and the second $1 is the parameter passed... (6 Replies)
Discussion started by: gillbates
6 Replies
Login or Register to Ask a Question