1

I have 2 files as below:

a.txt (tab delimited and it has 3 columns and too many lines):

 city   plate   region
 istanbul   36  marmara
 trabzon    61  karadeniz

b.txt (comma delimited and it has 4 columns and too many lines):

name,city,age,nationality
mehmet,trabzon,,
murat,istanbul,,
john,london,,

What I want to do is: check if the 2nd column (city field) of b.txt exists in a.txt (in first column - city) and if it exists in a.txt; update the file b.txt and set 4th column (nationality) to "turkish". So expected output:

name,city,age,nationality
mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,

I tried following command but it did not generated the desired output:

nawk 'BEGIN { FS = "\t" } FNR == NR {x[$1] = $1; next;} {FS=OFS=","} FNR>1{if ($2 in x) {($4 = "turkish")} } 1' a.txt b.txt
name,city,age,nationality
mehmet,trabzon,,
murat,istanbul,,
john,london,,

I need to find the mistake in my command.

1

Your script works fine. My guess is the first file has spaces instead of tabs as separators.

Remove the useless BEGIN { FS = "\t" } to check if it is the case.

  • You are totally right, it worked after removing the BEGIN { FS = "\t" } phrase. – Murat Oct 16 '15 at 9:21
0

Try with sed

sed -n 's|\(.*\S\)\s\+[0-9].*|/\1/s/$/turkish/|p' a.txt |
sed -f - b.txt
0

With awk (notice that the first file a.txt has to be loaded into memory completely):

awk -F"[\t, ]" 'FNR==NR&&NR!=1{a[$1]="turkish"}
  FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt

  • -F"[\t,]" sets awks delimiter to tab and comma
  • FNR==NR&&NR!=1 only applies to the first file a.txt and ignore the first line (header)
    • a[$1]="turkish" set in an array called a the index to the first field $1
  • FNR!=NR applies only to the second file b.txt
  • OFS="," set the output field separator
  • if(FNR!=1) only do something when it's not the header line
  • $NF=a[$2] set the last field to the previously saved value turkish if it exists in the array
  • print and print the line if the value existed or not

The output:

name,city,age,nationality
mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,
  • Thanks for your response but it did not work. With awk (empty result): bash-3.00$ awk -F"[\t,]" 'FNR==NR&&NR!=1{a[$1]="turkish"} FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt With nawk (b.txt file not updated): bash-3.00$ nawk -F"[\t,]" 'FNR==NR&&NR!=1{a[$1]="turkish"} FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt name,city,age,nationality mehmet,trabzon,, murat,istanbul,, john,london,, – Murat Oct 16 '15 at 8:58
  • @Murat Possible you have space separator instead tab so just add space to delimiters [ \t,] – Costas Oct 16 '15 at 9:12
  • A some shorter awk -F"[ \t,]" 'FNR==NR{if(NR!=1)a[$1]=1;next} $2 in a{$NF="turkish"}1' OFS="," a.txt b.txt – Costas Oct 16 '15 at 9:17
0
tr   -s \[:blank:] , <a.txt|    #dont mess with weird columns
cut  -d, -f1  |  sort -u   |    #drop all but unique first column entries
sed  -e's/[]*^\./$[]/\\&/g'\    #handle any regex metachars properly
     -e's/.*/^[^,]*,&,/'   |    #match only second column
grep -nf - -- b.txt        |    #get line numbers at head of matched lines
sed  -e's/:.*/be/'         |    #keep only matched line numbers + 'be'
sed  -f - -eb -e:e         \    #branch away or append turkish
     -e's/$/turkish/' -- b.txt

mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,

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.