Find match in csv file

I have two CSV files.

1.csv contains:

46700468915;2000

2.csv contains:

4670046;Tele2

I am trying to searching from 2.csv if 4670046 exists in 1.csv which is does and then take from 2.csv Tele2 and add in 1.csv if no match do not add

awk 'NR==FNR {a[$1]=$2; next} $2 in a {print $0, a[$2]}' OFS='\t' 2.csv 1.csv

2 Answers

You have a few issues here:

1. you need to set awk's field separator appropriately: by default it is whitespace, whereas your files appear to be delimited by semicolons

2. you are trying to match on a partial field: 4670046 is in a but 46700468915 isn't

3. you seem to be confused about which field you are matching, $1 or $2

If you know that you want to match the first 7 characters, you can try

awk -F ';' ' NR==FNR {a[$1]=$2; next} {k = substr($1,1,7)} k in a {print $0, a[k]}
' OFS='\t' 2.csv 1.csv

or equivalently

awk ' BEGIN{FS=";"; OFS="\t"} NR==FNR {a[$1]=$2; next} {k = substr($1,1,7)} k in a {print $0, a[k]}
' 2.csv 1.csv

Ex. given

$ head ?.csv
==> 1.csv <==
46700468915;2000
==> 2.csv <==
4670046;Tele2

then

$ awk 'BEGIN{FS=";"; OFS="\t"} NR==FNR {a[$1]=$2; next} {k = substr($1,1,7)} k in a {print $0, a[k]}' 2.csv 1.csv
46700468915;2000 Tele2

The awk solution should be much faster but here is an example, how to achieve this via bash script, which read each line of 2.csv as -array and then uses sed to do the changes (the if statement is not essential part of the script).

$ cat ./script.sh

#!/bin/bash
TARGET_FILE="./1.csv"
ORIGIN_FILE="./2.csv"
# In order to append new column to a line, comment-out -i.bak
while IFS=';' read -r -a line
do if grep -q "${line[0]}" "$TARGET_FILE" then sed "/^${line[0]}/ s/$/;${line[1]}/" "$TARGET_FILE" #-i.bak fi
done < "$ORIGIN_FILE"
echo '-----'
# In order to replace the second column of a line, comment-out -i.bak
while IFS=';' read -r -a line
do if grep -q "${line[0]}" "$TARGET_FILE" then sed -r "s/(^${line[0]}.*\;).*$/\1${line[1]}/" "$TARGET_FILE" #-i.bak fi
done < "$ORIGIN_FILE"

Example of usage:

$ cat 1.csv
46700468915;2000
46700568916;3000
46700668917;4000
$ cat 2.csv
4670046;Tele2
4670047;Tele3
4670048;Tele4
$ ./script.sh
46700468915;2000;Tele2
46700568916;3000
46700668917;4000
-----
46700468915;Tele2
46700568916;3000
46700668917;4000