4

I need to compare 2 files and print matched lines. If file1 username is in file2 (field 1) I want to print it to new matched file.

File1.txt:

Hey123
Johnson
Hanny123
Fanny

(file1 is 240MB - 20.000.000 lines)

File2.txt:

Gromy123:hannibal
Hey123:groll
Hanny123:tronda9
Kroppsk:football23

(file2 is 1.4GB - 69.000.000 lines)

Expected matched lines output:

Hanny123:tronda9
Hey123:groll

I have been trying for 4 hours without success. Both the files are sorted and I have tried join + countless of grep / awk commands. My big problem is RAM exhaustion. Would love some help how I could approach this, so large files.

5

If the files are sorted (the samples you posted are) then it's as simple as

join -t : File1.txt File2.txt

join pairs up lines from two files where the join field is equal. By default, the join field is the first field, the fields are output in order except that the join field is not repeated, and non-pairable lines are skipped, which is exactly what you want.

Note that if the files have Windows line endings, they appear under Unix systems to have an extra carriage return character at the end of each line. The CR is mostly visually invisible, but as far as join and other text tools are concerned, it's a character like any one else, and it means the fields of File1.txt all end with a CR whereas the ones in File2.txt don't so they don't match. You need to strip the CR, at least in File1.txt.

<File1.txt tr -d '\r' | join -t : - File2.txt

You do need to sort the files. If they aren't, then ksh/bash/zsh, you can use process substitutions. (Add tr -d '\r' | if needed.)

join -t : <(sort File1.txt) <(sort File2.txt)

In plain sh, if your Unix variant has /dev/fd (most do), you can use that instead to pipe the output of two programs through two file descriptors.

sort File2.txt | { sort File1.txt | join -t : /dev/fd/0 /dev/fd/3; } 3<&1

If you need to preserve the original order of File1.txt and it isn't sorted by the join field, then add line numbers to remember the original order, sort by the join field, join, sort by line numbers and strip the line numbers. (You can do something similar if you want to preserver the order of the other file.)

<File1.txt nl -s : |
sort -t : -k 2 |
join -t : -1 2 - <(sort File2.txt) |
sort -t : -k 2,2n |
cut -d : -f 1,3
  • I get no output. not sure why – Axel Tobieson Mar 6 '17 at 0:18
  • @AxelTobieson, could you check if File1 does not haze extra spaces, \r or similar? head -n 1 | od -c – JJoao Mar 6 '17 at 0:24
  • 1
    @AxelTobieson A possible explanation would be that you have files in Windows encoding, which means that on Linux there's a spurious carriage return character at the end of each line. A CR is mostly invisible, visually speaking, but it means that a text processing tool would see Hey123+CR in File1 and Hey123 in File2. What does file File1.txt File2.txt say? – Gilles Mar 6 '17 at 0:24
  • file1.txt: ASCII text, with CRLF line terminators – Axel Tobieson Mar 6 '17 at 0:27
  • @AxelTobieson See my edit – Gilles Mar 6 '17 at 0:31
4

If you have 2Gbyte of free RAM, try

awk -F: 'NR==FNR { n[$0]++ ; next}; $1 in n ' file1 file2 > file3
  • don't think it worked. didn't get it to work at least – Axel Tobieson Mar 6 '17 at 0:02
  • @AxelTobieson, linux? gawk? Does it work with your simple text? A made an update to save the ouput – JJoao Mar 6 '17 at 0:20
  • I try with the test I used in this thread and I get no output. does it work for you? – Axel Tobieson Mar 6 '17 at 0:24
  • @AxelTobieson, Yes: I simulated with a file of 500Mb of keys and it worked using 2.1GBytes. – JJoao Mar 6 '17 at 0:27
0

A possible solution (which certainly works for your small example):

#!/bin/bash

# because File2.txt is bigger, it gets the main loop.
# read each line of File2.txt
while read string; do
    # read each line of File1.txt
    while read string2; do
    # check match, and write if needed.
    if [[ $string == *"$string2"* ]]; then
        echo $string >> match_output.txt
        echo "wrote "$string" to match_output.txt..."
    fi
    done < File1.txt
done < File2.txt

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.