How to compare the columns in two .csv files?

Login or Register to Ask a Question and Join Our Community

How to compare the columns in two .csv files?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to compare the columns in two .csv files?

06-08-2011

Registered User

4, 0

Join Date: Jun 2011

Last Activity: 26 October 2012, 6:51 AM EDT

Posts: 4

Thanks Given: 4

Thanked 0 Times in 0 Posts

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 10:24 AM.. Reason: code tags, please!

Kanchana

View Public Profile for Kanchana

Find all posts by Kanchana

06-08-2011

Registered User

124, 29

Join Date: May 2011

Last Activity: 16 July 2018, 1:31 AM EDT

Location: India

Posts: 124

Thanks Given: 2

Thanked 29 Times in 29 Posts

Code:

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

Thanks to shahul for this code.

getmmg

View Public Profile for getmmg

Find all posts by getmmg

06-08-2011

Registered User

1,690, 205

Join Date: Jun 2007

Last Activity: 13 July 2020, 5:35 PM EDT

Location: Mumbai, India

Posts: 1,690

Thanks Given: 139

Thanked 205 Times in 199 Posts

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!

clx

View Public Profile for clx

Find all posts by clx

06-08-2011

Registered User

2,977, 644

Join Date: Oct 2010

Last Activity: 14 September 2019, 1:15 PM EDT

Location: France

Posts: 2,977

Thanks Given: 88

Thanked 644 Times in 613 Posts

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:

ctsgnb

View Public Profile for ctsgnb

Find all posts by ctsgnb

06-08-2011

Registered User

4, 0

Join Date: Jun 2011

Last Activity: 26 October 2012, 6:51 AM EDT

Posts: 4

Thanks Given: 4

Thanked 0 Times in 0 Posts

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!!

Kanchana

View Public Profile for Kanchana

Find all posts by Kanchana

06-08-2011

Registered User

237, 10

Join Date: Aug 2008

Last Activity: 18 March 2013, 11:43 AM EDT

Location: India

Posts: 237

Thanks Given: 3

Thanked 10 Times in 10 Posts

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:

Shahul

View Public Profile for Shahul

Find all posts by Shahul

06-08-2011

Moderator

8,825, 1,112

Join Date: Feb 2005

Last Activity: 23 August 2021, 11:26 AM EDT

Location: Foxborough, MA

Posts: 8,825

Thanks Given: 579

Thanked 1,112 Times in 1,003 Posts

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

vgersh99

View Public Profile for vgersh99

Find all posts by vgersh99

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

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

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

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

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

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

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

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

Login or Register to Ask a Question