Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Comparison of Cells in EXCEL using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparison of Cells in EXCEL using awk
# 1  
Old 02-12-2010
Comparison of Cells in EXCEL using awk
Hi

I have 2 csv files which looks like the following and i have to compare the 2 CSVs and create a third file such that if the value of the 1st cell in A.CSV and the value of the first cell in the B.CSV are same, it should print "SAME" in the third file or else print NOT SAME. Likewise i need to create the third file with the SAME/NOT SAME corresponding to each cell in the 2 CSVs.

Code:
A.CSV
"1","2009/1/12","DATA",+10.00
"2","2009/1/12","DATA",+11.00
"3","2009/1/12","DATA",+12.00
"4","2009/1/12","DATA",+13.00

Code:
B.CSV
"1","2009/1/12","DATA",+99.00
"2","2009/1/12","DATA",+11.00
"3","2009/1/12","DATA",+12.00
"4","2009/1/12","DATA",+13.00

I used the code as below
Code:
awk -F',' 'FNR==NR {_[$4]=$0;next} 
                   { split(_[$4],ar);
                     for(i=1;i<=NF;i++) 
                        if(ar[i]=$i) $i="SAME,"; 
                        else  $i="NOT SAME,"; 
                     print 
                   }' A.csv B.csv>> C.csv

but it always showed the following output. whether the data was same or not same.
Code:
OUTPUT
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,

but it is expected to show
Code:
SAME,SAME,SAME,NOT SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,

Thanks in advance for the help.
Regards
Meva
whereas the expected
# 2  
Old 02-12-2010
Try this:
Code:
awk -F, '{
  getline name < "B.csv"
  split(name, a, ",")
  for(i=1;i<=NF;i++) {
    if(a[i]==$i) {
      printf("SAME,")
    } 
    else {
      printf("NOT SAME,")
    }
  }
  print ""
}' A.csv > C.csv

# 3  
Old 02-14-2010
Hi Franklin.....

This is working...Thanks a lot.....Smilie

But we still have a problem...File A looks like the one given below
Code:
A.CSV
"1","2009/1/12","DATA",+10.00
"2","2009/1/12","DATA",+11.00
"3","2009/1/12","DATA",+12.00
"4","2009/1/12","DATA",+13.00

and file B looks like this
Code:
B.CSV
1,"2009/1/12","DATA",+99.00
2,"2009/1/12","DATA",+11.00
3,"2009/1/12","DATA",+12.00
4,"2009/1/12","DATA",+13.00

so as a result of excuting the code we get the following output

Code:
NOT SAME,SAME,SAME,NOT SAME,
NOT SAME,SAME,SAME,SAME,
NOT SAME,SAME,SAME,SAME,
NOT SAME,SAME,SAME,SAME,

But we expect the output to be

Code:
SAME,SAME,SAME,NOT SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,
SAME,SAME,SAME,SAME,

Please help with your thoughts on how to get the expected result.

Thanks In Advance
Meva.
# 4  
Old 02-14-2010
Try...
Code:
awk -F, '{gsub("\"","",$1)}
{
  getline name < "B.csv"
  split(name, a, ",")
  for(i=1;i<=NF;i++) {
    if(a[i]==$i) {
      printf("SAME,")
    } 
    else {
      printf("NOT SAME,")
    }
  }
  print ""
}' A.csv

Last edited by malcomex999; 02-14-2010 at 06:39 AM..
# 5  
Old 02-14-2010
Still not working....
# 6  
Old 02-14-2010
Sorry, didn't try that before but this one is working now...

Code:
awk -F, '{gsub("\"","",$1)}
{
  getline name < "B.csf"
  split(name, a, ",")
  for(i=1;i<=NF;i++) {
    if(a[i]==$i) {
      printf("SAME,")
    } 
    else {
      printf("NOT SAME,")
    }
  }
  print ""
}' A.csv

# 7  
Old 02-14-2010
Thanks a Lot....Its workingSmilie

Meva
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk replace cells with NaN/delete if condition

Hello, I will like to delete/replace $3 with NaN. condition $3>-2000 (file1.dat) to produce out.dat. I want to retain the structure of the table. I use this code, this output only $3. Any idea on how to modify this code. Thank you. awk -v OFS='' '{for(i=1; i<=NF; i++) if ($i > -2000 || $i ==" >... (4 Replies)
Discussion started by: geomarine
4 Replies

2. Programming

Perl script to merge cells in column1 which has same strings, for all sheets in a excel workbook

Perl script to merge cells ---------- Post updated at 12:59 AM ---------- Previous update was at 12:54 AM ---------- I am using below code to read files from a dir and print to excel. open(my $in, '<', $file) or die "Could not open file: $!"; my $rowCount = 0; my $colCount = 0;... (11 Replies)
Discussion started by: Jack_Bruce
11 Replies

3. Shell Programming and Scripting

awk comparison with two patterns

Here is my list, which contains URLs for file downloads: //servername.com/version/panasonic1,1_1.1.1 //servername.com/version/panasonic3,1_6.7.1 //servername.com/version/panasonic3,2_6.8 //servername.com/version/panasonic2,6_3.0.2 //servername.com/version/panasonic3,1_7.1.3... (5 Replies)
Discussion started by: ibanezpete
5 Replies

4. Shell Programming and Scripting

Need help with simple comparison in AWK

Hi, I'm new to AWK and I'm having problems comparing a field to a string variable. /ARTIST/ {x = $2} $1 ~ x {print $0}My code tries to find a record with the string "ARTIST". Once it finds it, it stores the second field of the record into a variable. I don't know what the problem is for the... (7 Replies)
Discussion started by: klusps
7 Replies

5. Shell Programming and Scripting

Merge two cells in excel via UNIX?

Hi UNIX Gods! Is it possible to merge two cells in .csv file using unix commands? Imagine that this is my present csv file opened via excel: Gate Reports| | fatal alerts | 200 | is is possible to make it look like this using unix? Gate Reports | fatal... (1 Reply)
Discussion started by: 4dirk1
1 Replies

6. Shell Programming and Scripting

awk comparison

Hello all, Probably a very simple question, I am stuck with a small part of a code: I am trying to do a comparison to get the maximum value of column 6 if columns 1, 4 and 5 of two or more rows match. Here is what I am doing: awk -F'\t' '{if ($6 > a)a=$6}END{for (i in a) print i"\t"a}' ... (4 Replies)
Discussion started by: jaysean
4 Replies

7. Shell Programming and Scripting

Comparison and editing of files using awk.(And also a possible bug in awk for loop?)

I have two files which I would like to compare and then manipulate in a way. File1: pictures.txt 1.1 1.3 dance.txt 1.2 1.4 treehouse.txt 1.3 1.5 File2: pictures.txt 1.5 ref2313 1.4 ref2345 1.3 ref5432 1.2 ref4244 dance.txt 1.6 ref2342 1.5 ref2352 1.4 ref0695 1.3 ref5738 1.2... (1 Reply)
Discussion started by: linuxkid
1 Replies

8. Programming

grouping and ungrouping of excel cells using java

Dear Folks, Is there anyone know how to do the group and ungroup cells feature of MS Excel through java. Please help. Please find the attachment.I want the code to do something like this. (0 Replies)
Discussion started by: dinesh1985
0 Replies

9. UNIX for Dummies Questions & Answers

multiple comparison in awk

I have an input file. Each line in it has several characters. If the first three characters of the line is '000' or '001' or '002' or '003', I need to print it in output. How can I do this in awk. I am able to do if the search string is only one (let us say 000). cat <filename> | awk... (1 Reply)
Discussion started by: paruthiveeran
1 Replies

10. Shell Programming and Scripting

UNIX -> send data as excel in seperate cells

Hi I have a data file in UNIX as follows. I need to send this file in Mail as excel format. but the problem i face is i get the data all in single cells per row. what can i do to get the data in seperate cells. File -> attachment.xls data data data data data1 data1 ... (1 Reply)
Discussion started by: vj8436
1 Replies
Login or Register to Ask a Question