Creating an sql query using awk, bash, grep

Question

I have been trying to parse a Paypal Email and insert the resultant info into a Database of mine. I have most of the code working but I cannot get a Variable to insert into my awk code to create the sql insert query.

if [  -f email-data.txt ]; then {
    grep  -e "Transaction ID:" -e "Receipt No: " email-data.txt \
      >> ../temp
    cat ../temp \
      | awk 'NR == 1 {printf("%s\t",$NF)}  NR == 2 {printf("%s\n",$NF)}' \
      >> ../temp1
    awk '{print $1}' $email-data.txt \
      | grep @ \
      | grep -v \( \
      | grep -v href \
      >> ../address
    email_addr=$(cat ../address)
    echo  $email_addr 
    cat ../temp1 \
      | awk '{print "INSERT INTO users (email,paid,paypal_tran,CCReceipt) VALUES"; print "(\x27"($email_addr)"\x27,'1',\x27"$2"\x27,\x27"$3"\x27);"}' \
      > /home/linux014/opt/post-new-member.sql

The output looks like the following

INSERT INTO users (email,paid,paypal_tran,CCReceipt) VALUES('9MU013922L4775929  9MU013922L4775929',1,'9MU013922L4775929','');

Should look like

INSERT INTO users (email,paid,paypal_tran,CCReceipt) VALUES('[email protected]',1,'9MU013922L4775929','1234-2345-3456-4567');

(Names changed to protect the innocent)
The trial data I am using is set out below

Apr 18, 2014 10:46:17 GMT-04:00 | Transaction ID: 9MU013922L4775929

You received a payment of $50.00 USD from Dog Cat ([email protected])
Buyer:
Dog Cat
[email protected]

Purchase Details 

Receipt No: 1234-2345-3456-4567

I cannot figure out why the email-addr is not being inserted properly.

You might want to scrap this script and use a programming language with an email parser class and mysql classes. Why re-invent the wheel? I know that's an annoying answer. You asked, "How do I make this crazy bicycle work?" and I told you "screw bicycles, use a car." But honestly, you're pretty much guaranteed to create unmaintainable code this way. You will be mad at yourself a year from now for using shell utilities to parse an email and generate SQL from it. I'm sure of that, because I've done the same things in the past. — Kevin
– Kevin, Commented Apr 20, 2014 at 1:16
Point taken. Thank you for your candor. I am not well versed in Email Parsers and mysql classes. Thank you for your answer. — Robert D Weir
– Robert D Weir, Commented Apr 20, 2014 at 6:26

jaypal singh · Accepted Answer · 2014-04-20 00:12:59Z

2

You are calling a shell variable inside awk. The right way to do that is by creating an awk variable using -v option.

For example, say $email is your shell variable, then

... | awk -v awkvar="$email" '{do something with awkvar}' ...

Read this for more details.

However, having said that, here is how I would try and parse the text file:

awk '
/Transaction ID:/ { tran  =  $NF }
/Receipt No:/     { receipt = $NF }
$1 ~ /@/          { email  =  $1 }
END {
     print "INSERT INTO users (email,paid,paypal_tran,CCReceipt) VALUES"; 
     print "("q email q","1","q tran q","q receipt q");"
}' q="'" data.txt

Output:

INSERT INTO users (email,paid,paypal_tran,CCReceipt) VALUES
('[email protected]',1,'9MU013922L4775929','1234-2345-3456-4567');

edited Apr 20, 2014 at 0:12

answered Apr 19, 2014 at 23:51

jaypal singh

77.6k24 gold badges108 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert D Weir Over a year ago

Thank you so much for this answer. As you know it worked right away. I appreciate your skills and will definitely use this code. Cheers...

Collectives™ on Stack Overflow

Creating an sql query using awk, bash, grep

1 Answer 1

Output:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Output:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related