Shell Programming and Scripting

Hello All,

I am new to shell scripting and programming. I am looking for a guide on how I can parse specific information from a plain text file with thousands of lines. Specifically I need to parse an email address from each line. The line looks something like this:

Code:

1272574001.H742765P10724.host.domain.com,S=4155:Return-path: <hbelanger@kpmg.com>

I would like to extract the email address (exluding the < and >) and then perform an update statement via mysql with the email address as a value.

Is there an example of this somewhere that I could use to base my needs off of?

What scripting tool would I use, how do I tell that tool to gab all contents BETWEEN the < and >?

How do I then (within one script) use this value to update a DB record?

p.s. I am essentially trying to unsubscribe these email addresses from a contact database..

Thanks for the help.
John

---------- Post updated at 01:19 PM ---------- Previous update was at 01:15 PM ----------

The SQL statement that needs to be run is:

Code:

UPDATE contact_master SET subscribed='No' WHERE email=<value>

Perhaps this would work to extract all the email addresses...

Code:

awk -F">" '/@/{print $1}' RS="<" file

(use nawk or /usr/xpg4/bin/awk if your OS is Solaris)

You can then pipe this output into a while read loop and run the SQL statements inside the loop..

That worked like a charm. I have looked up your syntax and now understand how that works. I will now research how to use a while loop. Would you recommend a bash script?

It could be done in awk, but I would use bash or another shell for this, I think it looks cleaner.

I edited my response. It was a typo. It was meant to say 'I will now research'

After reading on bash I can accomplish my mysql execution using a defined variable (variable = email address) but am not sure how to read a file and have the mysql execute the statement for each line (i.e. email) value.

Code:

#!/bin/sh

email="test@test.com"
table="contact_master"
column="subscribed"

qry="select id,data from $table where id in ($ids)"
qry="UPDATE $table SET $column='No' WHERE email=$email"

echo "Executing the following query"
echo $qry

/usr/bin/mysql -u root << eof
$qry
eof

I just dont know how to incorporate the awk statement that parses my emails and loop it into the above bash script which updates DB.

You could try something like this (using the statements that you wrote):

Code:

table="contact_master"
column="subscribed"
# ids=???

awk -F">" '/@/{print $1}' RS="<" file |
while read email
do
  /usr/bin/mysql -u root << EOF
  select id,data from $table where id in ($ids)
  UPDATE $table SET $column='No' WHERE email=$email
EOF
done

I dont understand why I need the ids variable and the purpose of:

Code:

select id,data from $table where id in ($ids)

I'm not sure I need to select any data, just UPDATE

Would you mind clarifying my confusion.

p.s. You've been so helpful, thanks.

-John