Parsing Strings

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:

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:

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

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

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.

#!/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):

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:

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

It's to show you where the query goes and how to write it. You can put whatever you want in there.

---------- Post updated at 01:44 PM ---------- Previous update was at 01:38 PM ----------

Also:

table="contact_master"
column="subscribed"

awk -F">" '/@/{print $1}' RS="<" file |
while read email
do
        echo "UPDATE $table SET $column='No' WHERE email='$email'"
done | mysql -uroot

Pretty sure you need those ' quotes around $email or mysql will complain. And piping everything into one instance of mysql means you won't need to login seperately for every single line.

The script is complaining that:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE contact_master SET subscribed='No' WHERE email=''' at line 2

I dont see where $email is defined..

is it defined with

while read email

-John

yes, it is. Each line it reads is fed into the email variable. It needs no $ there since we're giving it a variable name, not its contents.

replace mysql -uroot with cat, it will just print the query.