4

Having input.csv as following:

field_name,field_friendly_name
LastNm,Last_Name
cntn_last_mod_wrkr_full_nm,Last_Name
contact_last_nm,Last_Name
contact_first_last_nm,Last_Name
last_english_nm,Last_Name
last_pronunciation_nm,Last_Name
last_nm,Last_Name
lead_space_last_nm,Last_Name
last_mod_usr_nm,Last_Name
lcl_last_nm,Last_Name
adobe_last_topic_nm,Last_Name
last_changed_user_nm,Last_Name
last_purchased_product_service_nm,Last_Name
last_imported_source_nm,Last_Name
submt_last_nm,Last_Name
cntct_last_nm,Last_Name
cust_submt_last_nm,Last_Name
cust_cntct_last_nm,Last_Name
last_mod_by_nm,Last_Name
last_mod_als_nm,Last_Name
last_mod_nm,Last_Name
ship_last_nm,Last_Name
billing_last_nm,Last_Name
last_upd_by_nm,Last_Name
wrkr_last_nm,Last_Name
trns_line_itm_last_chg_psn_nm,Last_Name
trns_line_itm_last_cre_psn_nm,Last_Name
trns_hdr_last_chg_psn_nm,Last_Name
altr_last_nm,Last_Name
trns_last_chg_nm,Last_Name
lastrepaction_nm,Last_Name
last_build_nm,Last_Name
LegalLastNm,Last_Name
ManagerLastNm,Last_Name
4-LastNm,Last_Name
NextLevelManagerLastNm,Last_Name
ManagerLegalLastNm,Last_Name

from this file I would like to filter on column1 where condition is column1 value should be made of given set of words in this case (last, name, nm, lst, -, _, [0-9] ) and exclude if contains any other words. And also update column2 as "Found". And my search should be case insensitive.

LastNm,Found
last_nm,Found
4-LastNm,Found

I'm using this way wchich doesn't work:

awk -v q="'" --field-separator ',' '((tolower($1) ~ /last/) && (tolower($1) ~ /name/)) || ((tolower($1) ~ /last/) && (tolower($1) ~ /nm/)) && ($2="found") {print $1 "," $2  }' raw.csv
2
  • 1
    @αғsнιη can you re-post your answer plz
    – Achar007
    Commented Jun 19, 2020 at 8:58
  • my commamd was awk -F, ' BEGIN{ IGNORECASE=1; split("last|name|nm|lst|-|_|[0-9]", words, "|") } { len=RLENGTH=0; for (x in words){ if (match($1,words[x])) len+=RLENGTH; }; if (len==length($1)) { $2="found"; print }; }' OFS=, infile, however this answer is your best choice Commented Jun 19, 2020 at 18:44

2 Answers 2

6

With GNU awk, gensub could be used to remove all those words, print if empty:

awk -F , -v OFS=, 'gensub(/last|lst|name|nm|[0-9_-]*/,"","g",tolower($1))=="" {
    $2="found";
    print $1, $2
}' file

Unlike sub/gsub, gensub leaves the original record intact and instead returns the resulting string. The same approach could be used with standard awk by copying field into a variable.

To include more characters than [0-9_-], you could use [^[:alpha:]] (i.e. anything that isn't a letter):

last|lst|name|nm|[^[:alpha:]]
7
  • 1
    Using [^a-zA-Z] or [^[:alpha:]] instead of [0-9_-] would make it a bit more robust if there are more characters to be excluded between words.
    – pLumo
    Commented Jun 19, 2020 at 7:45
  • @guest "","g" what is the use of this ?
    – Achar007
    Commented Jun 19, 2020 at 8:27
  • gensub(pattern,replacement,modifiers). This replaces pattern with empty string, with modifier globally (=do not stop after first match).
    – pLumo
    Commented Jun 19, 2020 at 8:41
  • i do not want to replace with empty string; I've a big file running this code getting something else like,found ,found ,found ,found ,found ,found ,found ,found ,found ,found ,found awk -F , -v OFS=, 'gensub(/last|lst|name|nm|[0-9_-]*/,"","g",tolower($1))=="" { $2="found"; print $1, $2 }' raw2.csv | wc -l 144
    – Achar007
    Commented Jun 19, 2020 at 8:46
  • 1
    Then you're not running that code. The replacement is only for the comparison, it does not overwrite anything.
    – pLumo
    Commented Jun 19, 2020 at 8:47
4

Try this,

awk -F, -v OFS=, '
{
split($1,w,/[^[:alnum:]]/);
for (i in w) {
    if (!(match(tolower(w[i]),/\<([0-9]*|last|nm|name|lastnm|lastname)\>/))) next;
}
$2="Found"; print; 
}' file

Output:

LastNm,Found
last_nm,Found
4-LastNm,Found

Explanation:

  • split field $1 by all not ([^.]) alphanumeric ([:alnum:]) characters to get list of words.
  • for loop over these words.
  • if a word does not match the given regex holding the allowed words, jump to next record.
  • if that did not happen, we can finally assign $2="Found" and print the record

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .