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?
# 8  
Old 06-08-2011
Example :

Code:
$ cat f1
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.0 Fastethernet0
01HW4567 111 172.20.1.1 Fastethernet1
01HW1234 000 172.20.1.0 Fastethernet2

Code:
$ cat f2
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.1 Fastethernet1
01HW8901 111 172.20.1.1 Fastethernet1
01HW1234 000 172.20.1.0 Fastethernet2

Code:
$ nawk 'NR==FNR{a[$1]=$0;next}($1 in a) {print}' f1 f2
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.1 Fastethernet1
01HW1234 000 172.20.1.0 Fastethernet2

---> also display unchanged lines
Code:
$ nawk 'NR==1{print}NR==FNR{a[$1]=$0;next}($1 in a)&&$0!=a[$1]' f1 f2
hostname MAC SWITCH_IP SWITCH_PORT
01HW1234 000 172.20.1.1 Fastethernet1

--->does not display unchanged lines (except the header line of course)

---------- Post updated at 03:53 PM ---------- Previous update was at 03:37 PM ----------

Quote:
Originally Posted by vgersh99
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

or more simply
Code:
nawk 'NR==FNR{a[$1]=$3$4;next}($1 in a)&&($3$4!=a[$1])||FNR==1' file1 file2


Last edited by ctsgnb; 06-08-2011 at 11:43 AM..
These 2 Users Gave Thanks to ctsgnb For This Post:

Previous Thread | Next Thread
Test Your Knowledge in Computers #648
Difficulty: Medium
Window Maker was written from scratch by Tim Berners-Lee, best known as the inventor of the World Wide Web.
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