Visit Our UNIX and Linux User Community


How to compare the columns in two .csv files?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to compare the columns in two .csv files?
# 1  
Old 06-08-2011
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 :
Code:
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.0 Fastethernet0
01HW4567 111 172.20.1.1 Fastethernet1

FILE 2:
Code:
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.1 Fastethernet1
01HW8901 333 172.20.1.2 Fastethernet3

output:
-------
Code:
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.1 Fastethernet1

Note: the comparision is performing with respect to 1st column(hostname).If there is any changes in SWITCH_IP and SWITCH_PORT then it wil display all the values in the file 2.

Thanks in Advance
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!

Last edited by vgersh99; 06-08-2011 at 11:24 AM.. Reason: code tags, please!
# 2  
Old 06-08-2011
Code:
 
 nawk 'NR==FNR{a[$1]=$0;next}($1 in a) {print}' file1 file2

Thanks to shahul for this code.
# 3  
Old 06-08-2011
Quote:
Originally Posted by Kanchana
If there is any changes in SWITCH_IP and SWITCH_PORT then it wil display all the values in the file 2.

But in the expected output, you have skipped the differed rows!
# 4  
Old 06-08-2011
Code:
awk 'NR==1{print}NR==FNR{a[$1]=$0;next}($1 in a)&&$0!=a[$1]' file1 file2


The code :
Code:
nawk 'NR==FNR{a[$1]=$0;next}($1 in a) {print}' file1 file2

May not behave the way you expect since it print lines whose $1 appear in both file in all case, even in the case the line is unchanged in both file
This User Gave Thanks to ctsgnb For This Post:
# 5  
Old 06-08-2011
Thanks for your update. But it this "nawk" is not working in my server. Please find the version of my linux server.

uname -a
Linux unixteam.tcs.com 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

---------- Post updated at 08:32 AM ---------- Previous update was at 08:28 AM ----------

Thanks a lot CTSGNB!! It's working fine!!! Have a Great day!!
# 6  
Old 06-08-2011
Code:
nawk 'NR==FNR{a[$1]=$1;b[$0]=$0;next}($1 in a)&& !($0 in b)' file1 file2

Thanks
SHa
This User Gave Thanks to Shahul For This Post:
# 7  
Old 06-08-2011
Code:
nawk 'FNR==NR {f1_3[$1]=$3;f1_4[$1]=$4;next} FNR==1{print;next}$1 in f1_3 && ($3!=f1_[$3] || $4!=f1_4[$1])' file1 file2


Previous Thread | Next Thread
Test Your Knowledge in Computers #208
Difficulty: Medium
Open Shortest Path First (OSPF) was designed as an exterior gateway protocol (EGP) for use in an autonomous systems such as a local area network (LAN).
True or False?

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

Import 2 columns from 8 .csv files into pandas df (side by side) and write a new csv

I have 8 .csv files with 16 columns and "n" rows with no Header. I want to parse each of these .csv and get column and put the data into a new.csv. Once this is done, the new.csv should have 16 columns (2 from each input.csv) and "n" rows. Now, I want to just take the average of Column from... (3 Replies)
Discussion started by: Zam_1234
3 Replies

3. 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

4. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

5. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

6. 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

7. Shell Programming and Scripting

How to change value in CSV columns and compare two files where Column1 is identical

Hi all, Could someone help me with the following issue: 1st I have an CSV file delimiter is ";" I I have a column 7 where I need to do some multiple mathem. operation, I need all values in this columns to be multiplied by 1.5 and create a new CSV file with the replaced values. 2nd. I... (3 Replies)
Discussion started by: kl1ngac1k
3 Replies

8. Shell Programming and Scripting

How to compare 2 files & get only few columns based on a condition related to both files?

Hiiiii friends I have 2 files which contains huge data & few lines of it are as shown below File1: b.dat(which has 21 columns) SSR 1976 8 12 13 10 44.00 39.0700 70.7800 7.0 0 0.00 0 2.78 0.00 0.00 0 0.00 2.78 0 NULL ISC 1976 8 12 22 32 37.39 36.2942 70.7338... (6 Replies)
Discussion started by: reva
6 Replies

9. 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

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

Featured Tech Videos