Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche
# 1  
Old 12-26-2018
script to compare Column-1 of two different CSV files and print differences
Example:
I have files in below format
file 1:
Code:
zxc,133,joe@example.com
cst,222,xyz@example1.com

File 2 Contains:
Code:
hxd
hcd
jws
zxc
cst

File 1 has 50000 lines and file 2 has around 30000 lines :

Expected Output has to be :

Code:
hxd
hcd
jws

Tried the below Code.Doesnt work

Code:
awk 'BEGIN {
 IGNORECASE = 1;
 FS =OFS = ","
  }
  NR == FNR {
  f[$1] ! = $0
  next
  }
$1 in f {
  print $1 > "finaloutput.csv";
  }' file1.csv  file2.csv

Moderator's Comments:
Mod Comment Please do not change the content of your original post (other than cosmetic changes). It makes the thread really hard to follow otherwise
Last edited by Scrutinizer; 12-26-2018 at 12:39 PM.. Reason: code tags
# 2  
Old 12-26-2018
Code:
awk 'BEGIN {
 IGNORECASE = 1;
 FS="."
  }
  NR == FNR {
  f[$1]
  next
  }
$1 in f {
  print $1, $0
  }' file2.csv  file1.csv

Last edited by vgersh99; 12-26-2018 at 11:00 AM..
This User Gave Thanks to vgersh99 For This Post:
TestPractice
# 3  
Old 12-26-2018
With the renewed content of post #1 (please in future create a new post instead) try:
Code:
awk 'NR==FNR{A[$1]; next} !($1 in A)' IGNORECASE=1 FS=, file1 FS=" " file2

# 4  
Old 12-26-2018
If you don't insist on awk, try

Code:
$ sort -t, file[12] | uniq -uw3
hcd
hxd
jws

# 5  
Old 01-10-2019
Using bash and GNU grep.

Code:
grep -v -Ff <(cut -d',' -f 1 testfile1.csv) testfile2.csv > resultfile.csv

Moderator's Comments:
Mod Comment edit by bakunin: please put CODE-tags around code, data and terminl output to have it stand out form the text. Thank you.
Last edited by ulaxmi; 01-10-2019 at 11:33 AM..
# 6  
Old 01-10-2019
Hello TestPractice,

You have NOT haven't phrased your problems clearly in your post, based on your output shown I come up with this.
As per your shown samples I have not used ignorecase here.
Code:
awk 'FNR==NR{a[$1];next} !($0 in a)' FS="," Input_file1 FS=" " Input_file2

Thanks,
R. Singh
Last edited by RavinderSingh13; 01-10-2019 at 01:12 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare first column from two csv files with greater than or equal, and less than

I have two csv files of different sizes. The output file needs to have file1 contents on top of file2 contents where file2 col1 is >= to file1 col1, and file2 col1(same value) is < file1 col1 (next value). So basically, some file2 rows will be matched to the same file1 row because it is the closet... (7 Replies)
Discussion started by: aachave1
7 Replies

2. Shell Programming and Scripting

Compare two files column values using awk

Judi # cat File1 judi /export/home 76 judi /usr 83 judi # judi # cat File2 judi /export/home 79 judi /usr 82 judi # if COLUMN3 of File2 is greater that COLUMN3 of File1, then print File2's lines juid /export/home 79 Code tags please (2 Replies)
Discussion started by: judi
2 Replies

3. UNIX for Dummies Questions & Answers

awk command to compare files by column

So I have this issue. I have 4 files. the first one is the master file who has all possible combinations: file 1 - a - b - c - d - e the other three have some of the letters and a number instead of - for example file 2 34 a 5 c file 3 10 b 12 ... (3 Replies)
Discussion started by: Quijotes
3 Replies

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

5. Shell Programming and Scripting

Help with compare two column and print out column with smallest number

Input file : 5 20 500 2 20 41 41 0 23 1 Desired output : 5 2 20 0 1 By comparing column 1 and 2 in each line, I hope can print out the column with smallest number. I did try the following code, but it don't look good :( (2 Replies)
Discussion started by: perl_beginner
2 Replies

6. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

7. Shell Programming and Scripting

Compare Two Files(Column By Column) In Perl or shell

Hi, I am writing a comparator script, which comapre two txt files(column by column) below are the precondition of this comparator 1)columns of file are not seperated Ex. file1.txt 8888812341181892 1243548895685687 8945896789897789 1111111111111111 file2.txt 9578956789567897... (2 Replies)
Discussion started by: kumar96877
2 Replies

8. Shell Programming and Scripting

Compare files column to column based on keys

Here is my situation. I need to compare two tab separated files (diff is not useful since there could be known difference between files). I have found similar posts , but not fully matching.I was thinking of writing a shell script using cut and grep and while loop but after going thru posts it... (2 Replies)
Discussion started by: blackjack101
2 Replies

9. Shell Programming and Scripting

find expression with awk in only one column, and if it fits, print whole column

Hi. How do I find an expression with awk in only one column, and if it fits, then print that whole column. 1 apple oranges 2 bannanas pears 3 cats dogs 4 hesaid shesaid echo "which number:" read NUMBER (user inputs number 2 for this example) awk " /$NUMBER/ {field to search is field... (2 Replies)
Discussion started by: glev2005
2 Replies

10. Shell Programming and Scripting

awk compare column between 2 files

Hi, I would like to compare file1 and file2 file1 1 2 3 file2 1 a 2 b 3 c 4 d The result should only print out "d" in file 2. Thanks (3 Replies)
Discussion started by: phamp008
3 Replies
Login or Register to Ask a Question