Awk (lower case, concatenate, & excluding matches by 'skipping'')

hattori.hanzo · 11-28-2010, 08:58 PM

I have a file like below. For all the lines (except for the ones listed as 'Unknown Owner' and N/A') I would like to change to lower case and concatenate the first and last names.

Before:

Code:

aaa.bbb.ccc.ddd,Unknown Owner
ddd.eee.fff.ggg,N/A
hhh.iii.jjj.kkk,John Doe
aaa.bbb.ccc.ddd,Mary Jane

After:

Code:

aaa.bbb.ccc.ddd,Unknown Owner
ddd.eee.fff.ggg,N/A
hhh.iii.jjj.kkk,john.doe
aaa.bbb.ccc.ddd,mary.jane

I have the following but it 'removes' the lines, I would like it to just 'skip' those lines.

Code:

awk -F "," '!/N\/A|Unknown Owner/ {print tolower ($2)}' file

I am not sure on the concatenate part as I have never done it in awk.

Thanks & Regards

grail · 11-28-2010, 09:20 PM

So you need to look at what you have asked:

Quote:

!/N\/A|Unknown Owner/

This is the only pattern construct you have and it says to ignore lines containing either of those combinations.
Yes this is what you want for the transformation.

So maybe you need a different rule for those words?

Or, if you want it more robust but kept small, maybe make your change without printing and then print all lines as a separate action.

Lastly, it could be awk is the wrong tool and maybe you should use sed?

I have refrained from supplying a direct answer as you have had many others and I believe you need to start to solve some of these simpler ones
on your own once pointed in the right direction.

hattori.hanzo · 11-28-2010, 10:32 PM

Fare enough. Here is my solution. Rough but works...

Code:

# Extract lines of IP's with POC's
awk -F "," '!/Unknown Owner|N\/A/' file > file1.tmp
# Extact names
cut -d"," -f 2 file1.tmp > file2.tmp
# Extract IP
cut -d"," -f 1 file1.tmp > file3.tmp
# Concatenate names
awk '{print $1 "." $2}' file2.tmp > file4.tmp
# Join IP and names
paste -d"," file3.tmp file4.tmp > file5.tmp
# Extract IP's with no owners (Unknown Owner, N/A)
awk -F "," '/Unknown Owner|N\/A/ {print $1 "," $2}' file.TMP > file6.tmp
# Join  back together
cat file6.tmp  file5.tmp  > output.csv

grail · 11-28-2010, 10:52 PM

Well definitely a solution

I feel your awk skills are probably a little better than that by now

If we look at the task:

1. Line containing N/A or Unknown Owner to be left as is - currently covered by your regex and not
2. Last field (I see NF here) to be set to lowercase
3. If last field has a space in it, then replace with a dot (smells like a gsub (use gsub instead of sub in case person has more than one part to last name))

Let me know if this is enough for you to complete?

I really like to remember the K.I.S.S philosophy when writing scripts - Keep It Simple Silly (or Stupid depending on how you like to say the last word)

See how you go and ask away at the questions when you get stuck.

grail · 11-28-2010, 10:52 PM

EDIT: removed double post