CODEWITHSUNDEEP
linuxshellunixawk
Marjer
Marjer

Reputation: 1403

compare two columns and prints the 2nd column in 2nd file

I Need to compare 1st two columns of two files, if the results are equal need to print the 2nd column of 1st file into 2nd file for all the matches in 1st file.

Inputs:

cat sample.txt
135,135-00
135,135-13
135,135-134
235,120-23
235,190-56

cat dump_test.csv
135,121,4,VA,date,date
135,128,6,VA,date,date
135,131,9,VA,date,date
235,128,10,date,date

Output File:

cat output.txt
135,121,4,VA,date,date,135-00
135,128,6,VA,date,date,135-00
135,131,9,VA,date,date,135-00
135,121,4,VA,date,date,135-13
135,128,6,VA,date,date,135-13
135,131,9,VA,date,date,135-13
135,121,4,VA,date,date,135-134
135,128,6,VA,date,date,135-134
135,131,9,VA,date,date,135-134
235,128,10,date,date,120-23
235,128,10,date,date,190-56

Upvotes: 0

Views: 875

Answers (2)

nu11p01n73R
nu11p01n73R

Reputation: 26667

$ awk -F, 'BEGIN{i=0}FNR==NR{first[i]=$0; i=i+1;}NR!=FNR{for(j=0;j<i;j++){split(first[j],line); if(line[1]==$1) print $0,line[2]}}' sample.txt dump_test.csv

135,121,4,VA,date,date 135-00
135,121,4,VA,date,date 135-13
135,121,4,VA,date,date 135-134
135,128,6,VA,date,date 135-00
135,128,6,VA,date,date 135-13
135,128,6,VA,date,date 135-134
135,131,9,VA,date,date 135-00
135,131,9,VA,date,date 135-13
135,131,9,VA,date,date 135-134
235,128,10,date,date 120-23
235,128,10,date,date 190-56

What it does?

In short

creates an array first from each line in the first file, sample.txt

for each line in second file dumb_test it checks if the first field is in first, if yes appends the second colum in first with the line and prints.

Long

FNR==NR ensures that the following command works only for the first file, sample.txt

first is an array, which saves each line in file one, sample.txt and is indexed using i

first[i]=$0 copies the entire line into the array, and increments the index i

NR!=FNR ensures that the following command is excecuted for second file dumb_test.csv

the for iterates through the array first, that is lines in the first file.

split(first[j],line) the first[i] into the array line

eg when first[0] = 135,135-00

split(first[0], line) makes line[1] = 135 and line[2] = 135-00 here line[1] is column one and line[2] is column two of first file

the if checks the line[1] == $1 that is the first column in file one sample.txt is equal to first colum in second file dumb_test.csv

if so prints the entire line in second file, $0 appeded with the second colum of first file, line[2]

print $0,line[2]

Upvotes: 2

Kent
Kent

Reputation: 195229

is this okay for you?

join -t',' test sample

output:

135,121,4,VA,date,date,135-00
135,121,4,VA,date,date,135-13
135,121,4,VA,date,date,135-134
135,128,6,VA,date,date,135-00
135,128,6,VA,date,date,135-13
135,128,6,VA,date,date,135-134
135,131,9,VA,date,date,135-00
135,131,9,VA,date,date,135-13
135,131,9,VA,date,date,135-134
235,128,10,date,date,120-23
235,128,10,date,date,190-56

Upvotes: 2

Related Questions