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:
you need to set awk's field separator appropriately: by default it is whitespace, whereas your files appear to be delimited by semicolons
you are trying to match on a partial field:
4670046is inabut46700468915isn'tyou seem to be confused about which field you are matching,
$1or$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 
