Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to compare values on two CSV files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to compare values on two CSV files
# 1  
Old 09-07-2009
Question Need to compare values on two CSV files
Smilie

Hello,
Having a problem with reading two files using awk/nawk, am new to both them.
I need to compare field values between two csv files and arrange for an appropriate output if both the values are equal or not for each feild.
Code:
$cat File1.csv
1979181218.70,2039088797.84,48864040.62,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
1573820407.52,1536377096.18,673980681.96,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
405360811.18,502711701.66,625116641.34,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
$cat File2.csv
1979181218.70,2039088797.84,48864040.62,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
1573820407.52,1536377096.18,673925056.96,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
405360811.18,502711701.66,625061016.34,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00



Understood reading values into an array for a single file,
Code:
nawk '
 BEGIN{FS=","}
{for(i=1;i<=NF;i++)
 arr[NR","i] = $i;
{for (col = 1; col <= NF; col++) 
      {printf "%s \t" , arr[NR","col];}
       printf("\n"); }
 ' File1.csv

Code:
1979181218.70   2039088797.84   48864040.62     0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
1573820407.52   1536377096.18   673980681.96    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
405360811.18    502711701.66    625116641.34    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00

Having problems with the getline concept.Think that is the only way to read a second file.Smilie
Please advice.

# 2  
Old 09-07-2009

Code:
paste File1.csv File2.csv | awk -F '\t' '
{
  split($1,a,",")
  split($2,b,",")

  ## compare a[X] and b[X] etc....
}
'

# 3  
Old 09-07-2009
Perhaps this is good enough in your case ?
Code:
diff File1.csv File2.csv

# 4  
Old 09-07-2009
If you want to persist with (n)awk:

To print lines that are similar:
Code:
awk 'FNR==NR{arr[$0]=1;next} arr[$0]{print}' file1 flile2

To print lines that differ:
Code:
awk 'FNR==NR{arr[$0]=1;next} !arr[$0]{print}' file1 flile2

To output results in two different files:
Code:
awk 'FNR==NR{arr[$0]=1;next} arr[$0]{print > "/tmp/equal"} !arr[$0]{print > "/tmp/diff"} file1 file2'

# 5  
Old 09-08-2009
Thanks a lot!
Smilie Thanks for the replies Johnson,scrutinizer and ripat....

Used the paste and split concept,

Code:
paste MTH-tgf_cube.csv MTH-abm_cube.csv | nawk  '
BEGIN {FS="\t";}
{
split($1,a,",")
split($2,b,",")
printf " Comparing Record %s\n", NR;
   for (i=1;i<=12;i++) 
    {
    printf("%s=%d\t", i, a[i]);
    printf("%s=%d\t", i, b[i]);
    printf("Difference %s=%d\n", i, a[i]-b[i]);

    }
}
'

Was using Diff earlier, but needed the difference in field values from two files and not between files.


as for using nawk, Was definitely thinking of using it,had tried to dig-in ,before posting this question.
I still do not get the usage of FNR or using getline Smilie,
What will $0 be during the check here?
If i am not wrong $0 is the whole record of the input,probably a common record for file1 and file2.Would really help if you can explain this.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare 2 csv files

Hello, I want to compare two csv files expected.csv and actual.csv by fields "f3", "f4", "f5". Field "z" can be differnt. Each record identified by two fields: "number" +"key". Records are not sorted. Can you please help me to get such report: Error: Mismatch in the record... (4 Replies)
Discussion started by: Eugenne
4 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Script to compare count of two csv files

Hi Guys, I need to write a script to compare the count of two csv files each having 5 columns. Everyday a csv file is recived. Now we need to compare the count of todays csv file with yesterday's csv file and if the total count of records is same in todays csv file and yesterday csv file out... (3 Replies)
Discussion started by: Vivekit82
3 Replies

4. Shell Programming and Scripting

Compare 2 csv files in ksh and o/p the difference in a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 The... (7 Replies)
Discussion started by: Naresh101
7 Replies

5. Shell Programming and Scripting

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

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

6. Shell Programming and Scripting

How to compare the columns in two .csv files?

Hi I have to compare two .csv files which having 4 columns and i am expecting the output if there is difference in the 3,4columns in two files with respect to the first column. if my statement is not clear please refer the example. Input: ----- File 1 : hostname MAC SWITCH_IP SWITCH_PORT... (7 Replies)
Discussion started by: Kanchana
7 Replies

7. Shell Programming and Scripting

Help with script to open and compare csv files

We are testing an application that accesses two tables: A and B. I am to write a script to validate the ouput files of this application.The application marks any account that has become overdue as per rule. When it runs, it updates the overdue flag in the A table according to the following rules: ... (1 Reply)
Discussion started by: inkyponky
1 Replies

8. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies

9. UNIX for Dummies Questions & Answers

How to compare csv files using perl

I need to compare 2 csv files and report should containg number of matching lines,different lines ,missing lines in one file using perl. I dont want to use read line by line and scan thru the second file for matching line ,as this logic was so time consuming .Can other ideas .please respond asap... (2 Replies)
Discussion started by: kittu1979
2 Replies

10. UNIX for Dummies Questions & Answers

Compare 2 csv files in perl

need to compare 2 csv files and report should containg number of matching lines,different lines ,missing lines in one file using perl. I dont want to use read line by line and scan thru the second file for matching line ,as this logic was so time consuming .Any ideas.i need the soultion badly .... (2 Replies)
Discussion started by: kittu1979
2 Replies
Login or Register to Ask a Question