CODEWITHSUNDEEP
bashawk
user3671049
user3671049

Reputation: 23

Combining multiple files and columns in AWK

So i have 2 files, a list of people, and a list of people and a bit of a description. im trying to combine these 2 files with AWK, but i have never used AWK on more than one file at a time and i just cant figure it out.

Maybe AWK isnt the easiest way to do it, but i just assumed it is.

file 1

5 7/5/93 Steve
21 21/1/90 Bob
52 1/1/89 dale
21 21/1/90 Bob
52 1/1/89 dale

file 2

dale - is a cool guy
Steve - works at cosco
dale - is a cool guy
Steve - works at cosco
Steve - works at cosco

OUTPUT

5 7/5/93 Steve - works at cosco
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy

Upvotes: 2

Views: 197

Answers (2)

John B
John B

Reputation: 3646

You could use Bash:

while read line; do
    while read name trait; do
        if [[ $line == *"$name" ]]; then
            line="$line $trait"
            break
        fi
    done < file2
    echo $line
done < file1

Upvotes: 0

Jotne
Jotne

Reputation: 41460

Here you go:

awk -F" - " 'FNR==NR {a[$1]=$2;next} {split($0,b," ");print $0 (a[b[3]]?FS a[b[3]]:"")}' file2 file1
5 7/5/93 Steve - works at home
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy

awk is very good to join files based on different criteria.

In your file 2 you do repeat data for same person. That works fine. If there are different data for one and same person, awk will use the last it find and ignore all other.

Another variation:

awk 'FNR==NR {sub(/ /,"| ");split($0,f,"|");a[f[1]]=f[2];next} {print $0 a[$3]}' file2 file1
5 7/5/93 Steve - works at home
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy
21 21/1/90 Bob
52 1/1/89 dale - is a cool guy

Here is how it works:

awk '
FNR==NR {               # Run this section for the first file in the list (file2)
    sub(/ /,"| ")       # Change first space to "| " so we can split username from data
    split($0,f,"|")     # Split the sting in to "f" array devided by "|"
    a[f[1]]=f[2]        # Store data into array "a" using username as index
    next}               # Skip the next record.
    {                   # Run this section for file1
    print $0 a[$3]}     # Print all data from file1 and the data from array "a" (the user information)
    ' file2 file1       # Read the two files.

Upvotes: 2

Related Questions