-1

I really need your help Guys, I am new here in Unix. I want to compare my File2(3 column) to my File1(raw data), and save the result in a new file. But my files have a different delimiter.

Example:

File1 (in tab delimited)
Col1    Col2    Col3    Col4    Col5
a       1       JJ      KK      DD
b       2       TT      RR      EE
c       3       QQ      ZZ      PP

File2 (in pipe delimited)
Col1    Col2    Col3
b       TT      RR
c       QQ      ZZ

Result
Col1    Col2    Col3    Col4    Col5
b       2       TT      RR      EE
c       3       QQ      ZZ      PP

Note that my files are large, I need a code that execute fast. And I need to match the 3 column of my file2 to be match on the file1. Thanks a lot!

  • 1
    Why not loading them in a database (eg: MySQL) and then some SQL Joins will do the trick for you ? – dev93 Jun 20 at 9:50
  • Our DB is down. so we make a manual process. – Ace Jun 20 at 10:11
  • what type of files are these? If these are mysql files you can still import to your local machine using mysql. And then execure relevant query. – Pushpam Kumar Jun 20 at 12:08
  • It's a csv file. We can't use our sql's for now as we experiencing a problem. So we need it manually process in unix. – Ace Jun 21 at 0:04
  • Please clarify exactly what you're matching on between the 2 files (one col, many cols, string, regexp, partial, full word, full line, something else) as right now it's very vague and your posted example doesn't help since all columns in file2 are present in the matching rows of file1 (so fix your example to clarify your requirements) – Ed Morton Jun 21 at 4:54
1

command

u=`awk 'NR==1{print $0}' fil1`

awk -v u="$u"  'BEGIN {print u}NR==FNR{a[$3];next}($4 in a){print $0}' fil2 fil1

output

Col1    Col2    Col3    Col4    Col5
b       2       TT      RR      EE
c       3       QQ      ZZ      PP
  • Hi @praveen, thanks for this, but kindly put some explanation and how to use these codes? So glad to here these from you. Thanks a lot. – Ace Jun 21 at 0:10
  • Also, I have to use the 3 column of my File2 to match on the File1. – Ace Jun 21 at 0:26
  • 1
    Does this account for File2 (in pipe delimited)? – Jeff Schaller Jun 21 at 0:42
0

You could sort(1) them, reformat to use the same delimiter (with e.g. sed(1)), and process the result through a program that does the custom matching (I'd write it in perl(1), but there are lots of options, perhaps Python is more familiar today).

  • Thanks! I also tried to use perl but this only matched on the 1st and 2nd column. See codes and help me to have an output on the said problem. perl -e 'open(A, "File1"); while(<A>){/.+?\|[^|]+/ && $k{$&}++}; while(<>){/.+?\|[^|]+/ && do{print if defined($k{$&})}}' File2 – Ace Jun 21 at 0:13
  • @Ace, Perl is a complete programming language, sporting very powerful string matching and manipulation operations, What you want to do isn't an one-liner, though. – vonbrand Jun 21 at 11:56
0

You don't say WHAT you want to match on in file1 (a column, the whole line, a regexp, a string, partial or full, etc.) so it's a guess but maybe this is what you're trying to do ( a full-field string match of file1 column 4 against file2 column 3):

$ awk 'NR==FNR{a[$3]; next} (FNR==1) || ($4 in a)' FS='[|]' file2 FS='\t' file1
Col1    Col2    Col3    Col4    Col5
b       2       TT      RR      EE
c       3       QQ      ZZ      PP

or maybe this (comparing all cols from file2 against 3 specific cols from file2 in full-field string matches in order):

$ awk 'NR==FNR{a[$1,$2,$3]; next} (FNR==1) || (($1,$3,$4) in a)' FS='[|]' file2 FS='\t' file1
Col1    Col2    Col3    Col4    Col5
b       2       TT      RR      EE
c       3       QQ      ZZ      PP

but there's dozens of other possibilities too. The problem with the example you provided is that there's so many combinations of matches could be producing the expected output given your posted sample input.

0

One way with Perl can be:

 $ perl -F'\t' -lane '$"="|";
     $h{$_}++,next if @ARGV;
     print if $h{"@F[0,2,3]"};
 ' File2 File1

Readin the File2 first and populate the hash keyed with lines of file2, containing pipe symbols remember.

Then readin file1 and try to match its fields 0,2, and 3, split on tab and joined by pipe. For a match get the record.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.