Visit Our UNIX and Linux User Community


Compare values in two files. For matching rows print corresponding values from File 1 in File2.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare values in two files. For matching rows print corresponding values from File 1 in File2.
# 8  
Old 05-17-2012
What do you want to do with the very long SQL statements? Do you want to indent them on second/third lines?

BTW: This is what perl was designed for:
Code:
#!/usr/bin/perl
format =
@######  @<<<<<<<<  @<<<<<<<<<<<<<<<  ^<<<<<<<<<<<<<<<<<<<<<<<<<<<
$pid, $status, $datetime, $sql
~~       ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$sql                                                                                           .
while (<>) {
  ($pid,$status,$datetime,$sql) = split(" ",$_,4);
  write;
}

pipe the output of awk into this program and save as your report. It will look real pretty Smilie

If you need more help with formatting, do man perlform

---------- Post updated at 06:45 PM ---------- Previous update was at 06:38 PM ----------

Jack, here's a break-down of the awk command:
Code:
awk 'NR==FNR{A[$1]=$2;next} A[$1]{$5=A[$1];print}'

In awk, NR is the total input lines seen, while FNR is the number of input lines seen in the current file. So this essentially means: if we're processing the first file, "do this" (the code in the first set of braces {...}).

That code sets an associate array to the value of the 2nd column (in the first file, remember), where the index is the 1st column -- which is common in both files.

Now do a "next" which means do not process any more code for the current line. This ensures the rest of the awk script is not executed for the first file.

So the first "pattern/program" applies to the first file -- and only the first -- while the second "pattern/program" applies to the second (and subsequent) file(s).

The second pattern/program looks at each line (in the second file) and if the first column is found in the array A, and if the value is not null or not blank, it runs the portion between the braces.

The code in the braces simply replaces the 5th field of that line with the contents of what was seen in line indexed by the first column in the first file.
This User Gave Thanks to otheus For This Post:
# 9  
Old 05-18-2012
Quote:
Originally Posted by otheus
Jack, here's a break-down of the awk command:
Code:
awk 'NR==FNR{A[$1]=$2;next} A[$1]{$5=A[$1];print}'

In awk, NR is the total input lines seen, while FNR is the number of input lines seen in the current file. So this essentially means: if we're processing the first file, "do this" (the code in the first set of braces {...}).

That code sets an associate array to the value of the 2nd column (in the first file, remember), where the index is the 1st column -- which is common in both files.

Now do a "next" which means do not process any more code for the current line. This ensures the rest of the awk script is not executed for the first file.

So the first "pattern/program" applies to the first file -- and only the first -- while the second "pattern/program" applies to the second (and subsequent) file(s).

The second pattern/program looks at each line (in the second file) and if the first column is found in the array A, and if the value is not null or not blank, it runs the portion between the braces.

The code in the braces simply replaces the 5th field of that line with the contents of what was seen in line indexed by the first column in the first file.
thanks otheus, very well explained. one question though. "and if the value is not null or not blank"

which bit is doing the above validation?
# 10  
Old 05-18-2012
The A[$1] before the code block: A[$1]{$5=A[$1];print}

nulls and blanks are considered false and won't run the code, anything else is true and will run the code block.
These 3 Users Gave Thanks to Corona688 For This Post:
# 11  
Old 05-21-2012
Thank You guys for the clear and detailed explanation.

man perlform has great options for formatting. I will try to use those options.

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare values in multiple rows in one column using awk

I would like to compare values in column 8, and grep the ones where the different is > 1, columns 1 and 2 are the key for array. Every 4 rows the records values in columns 1 and 2 changed. Then, the comparison in the column 8 need to be done for the 4 rows everytime columns 1 and 2 changed ... (4 Replies)
Discussion started by: jiam912
4 Replies

2. UNIX for Dummies Questions & Answers

Compare file1 and file2, print matching lines in same order as file1

I want to print only the lines in file2 that match file1, in the same order as they appear in file 1 file1 file2 desired output: I'm getting the lines to match awk 'FNR==NR {a++}; FNR!=NR && a' file1 file2 but they are in sorted order, which is not what I want: Can anyone... (4 Replies)
Discussion started by: pathunkathunk
4 Replies

3. Shell Programming and Scripting

Compare file1 for matching line in file2 and print the difference in matching lines

Hello, I have two files file 1 and file 2 each having result of a query on certain database tables and need to compare for Col1 in file1 with Col3 in file2, compare Col2 with Col4 and output the value of Col1 from File1 which is a) not present in Col3 of File2 b) value of Col2 is different from... (2 Replies)
Discussion started by: RasB15
2 Replies

4. Shell Programming and Scripting

How to print in awk matching $1 values ,to $1,$4 example given.?

Hi Experts, I am trying to get the output from a matching pattern but unable to construct the awk command: file : aa bb cc 11 dd aa cc 33 cc 22 45 68 aa 33 44 44 dd aa cc 37 aa 33 44 67 I want the output to be : ( if $1 match to "aa" start of the line,then print $4 of that line, and... (3 Replies)
Discussion started by: rveri
3 Replies

5. Shell Programming and Scripting

Print columns matching to specific values

Hello Friends, I have a CDR file and i need to print out 2 columns with their field position which matches to some constant values, a part of input file CZ=1|CZA=1|DIAL=415483420001|EE=13|ESF=1|ET=|FF=0|9|MNC=99|MNP=9041|MTC=0|NID=2|NOA=international|ON=1| OutPut ... (3 Replies)
Discussion started by: EAGL€
3 Replies

6. Shell Programming and Scripting

Get values from different columns from file2 when match values of file1

Hi everyone, I have file1 and file2 comma separated both. file1 is: Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10 Code7,,,,,,,,, Code5,,,,,,,,, Code3,,,,,,,,, Code9,,,,,,,,, Code2,,,,,,,,,file2... (17 Replies)
Discussion started by: cgkmal
17 Replies

7. Shell Programming and Scripting

Print rows in reverse order if values decrease along the column

Hi, Guys. Please help me to find solution to this problem using shell scripting. I have an INPUT file with 4 columns separated by tab. Each block of records is separated by ----- ----- Sample1 5402 6680 Pattern01 Sample2 2216 2368 Pattern02... (6 Replies)
Discussion started by: sam_2921
6 Replies

8. Shell Programming and Scripting

AWK: read values from file1; search for values in file2

I have read another post about this issue and am wondering how to adapt it to my own, much simpler, issue. I have a file of user IDs like so: 333333 321321 546465 ...etc I need to take each number and use it to print records wherein the 5th field matches the user ID pulled from the... (2 Replies)
Discussion started by: Bubnoff
2 Replies

9. Shell Programming and Scripting

compare two columns of different files and print the matching second file..

Hi, I have two tab separated files; file1: S.No ddi fi cu o/l t+ t- 1 0.5 0.6 o 0.1 0.2 2 0.2 0.3 l 0.3 0.4 3 0.5 0.8 l 0.1 0.6 ... (5 Replies)
Discussion started by: vasanth.vadalur
5 Replies

10. Shell Programming and Scripting

perl script to print to values in rows and columns

Hi guys I want to print the values by using this script but its giving the no of rows and columns as input instead of values Would you plz help me on this FILE- chr1.txt 1981 1 1971 1 1961 1 1941 1 perl script #!/usr/bin/perl -w $infile1 = 'chr1.txt'; $outfile3 = 'out3.txt'; ... (3 Replies)
Discussion started by: nogu0001
3 Replies

Featured Tech Videos