Shell Programming and Scripting

Hi,

I have two text files.The first and the 2nd file have data in the same format
For e.g. The first file has
table_name1 column1 sum(column1) max(column1) min(column1)
table_name1 column2 sum(column2) max(column2) min(column2)
table_name1 coulmn3 sum(column3) max(column3) min(column3)

table_name2 column1 sum(column1) max(column1) min(column1)
table_name2 column2 sum(column2) max(column2) min(column2)
.
.

The 2nd file has the same data but few additional columns might be present in it.

I need to compare the values of both the files for common columns and report the mismatches to a separate file.
I am new to unix. Can anyone tell me how to do this?

Assuming your columns are separated by a single space (or fixed width), you can use cut to extract the ones which should be in both files, and compare.

Code:

cut -d ' ' -f1-5 file2 | diff -u file1 - | less

You probably don't want diff -u output as the end result, but that should get you going.

Hi,

I dont understand ur reply. I can probably give you the relevant content of the file.

File1 contains

BOOKS COUNT: 40
BOOKS AUTHOR1 SUM:1018 MAX:47 MIN:1 AVG:25.45
BOOKS AUTHOR3 SUM:181 MAX:48 MIN:3 AVG:18.1

Note:Read it as Table columnname sum(column) max(column) min(column)

File2 contains

BOOKS COUNT: 40
BOOKS AUTHOR1 SUM:1018 MAX:45 MIN:2 AVG:23.55
BOOKS AUTHOR2 SUM:561 MAX:49 MIN:1 AVG:21.5769231
BOOKS AUTHOR3 SUM:181 MAX:48 MIN:3 AVG:18.1


The file 2 contains the additional column named AUTHOR2.

Comparison should be such that the column AUTHOR1 in file 1 is compared with the column AUTHOR1 in file 2.

Here The coumn AUTHOR! in the table BOOKS mismatches with the other file.

I should write a script to compare the columns of the table as said and report the mismatches to a different file and also report the additioanl columns present in the 2nd file.

The output should be in this format.

Column AUTHOR1 in the tabloe BOOKS mismatches.
BOOKS AUTHOR1 SUM:1018 MAX:47 MIN:1 AVG:25.45
BOOKS AUTHOR1 SUM:1018 MAX:45 MIN:2 AVG:23.55

Column AUTHOR2 is additional.


Please help me in writing a script to do this job.

Thanks.

Hi,

File 1 and 2 contains the statistics for many other tables in the format mentioned before. And the column are separated by a single space.

comm can be used to handle common lines between two files.

Code:

comm -3 File1 File2

Further help in

Code:

man comm

Code:

awk 'NR == FNR { 
f1[$1,$2] = $0
next
}
($1 SUBSEP $2) in f1 && $0 != f1[$1,$2] {
  printf "Column %s in the table %s mismatches\n%s\n%s\n", 
  $2, $1, f1[$1,$2], $0
}
!(($1 SUBSEP $2) in f1) {
  printf "Columns %s is additional\n", $2 
}' file1 file2

Use nawk or /usr/xpg4/bin/awk on Solaris.

Hi Radoulov,

Thanks for your help. The output of your code is almost what i expected.

The ouput i get for the additional columns present is in this format

column 'column_name' is additional.

But this doesn't tell from which table.What can be done to make my output look like this.

column 'column_name' from the table 'table_name' is additional.