string substitution from text file to another string

Question

I have a text file with three columns of text (strings) per line. I want to create an SQL insert command by substituting each of the three strings into a skeleton SQL command. I have put place markers in the skeleton script and used SED s/placemarker1/first string/ but with no success. Is there an easier way to accomplish this task. I used pipes to repeat the process for 'second string' etc. I actually used awk to get the fields but could not convert to the actual values.

enter code here
    for i in [ *x100* ]; do
    if [ -f "$i" ]; then {
        grep -e "You received a payment" -e "Transaction ID:" -e "Receipt No: " $i  >> ../temp
        cat ../temp | awk 'NR == 1 {printf("%s\t",$9)} NR == 2 {printf("%s\t",$9)}  NR == 3 {printf("%s\n",$3)}' | awk '{print $2,$1,$3}' | sed 's/(/ /' | sed 's/)./ /' >> ../temp1
        cat temp1 | awk 'email="$1"; transaction="$2"; ccreceipt="$3";'
        cat /home/linux014/opt/skeleton.sql | sed 's/EMAIL/"$email"/' | sed 's/TRANSACTION/"$transaction"/' | sed 's/CCRECEIPT/"$ccreceipt"/' > /home/linux014/opt/new-member.sql
        rm -f ../temp
    } fi
    done

I cannot figure out how to get the values instead of the names of the variables inserted into my string.

Sample input (one line only):

[email protected] 2w4e5r6t7y8u9i8u7 1111-2222-3333-4444

Sample actual output:

INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('"$email"','"$transaction"','"$ccreceipt"');

Preferred output:

INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('[email protected]','2w4e5r6t7y8u9i8u7','1111-2222-3333-4444');

sample input and expected output would help

user918938
– user918938

2014-04-16 17:32:32 +00:00
Commented Apr 16, 2014 at 17:32 — user918938
– user918938, Commented Apr 16, 2014 at 17:32

a5hk · Accepted Answer · 2014-04-16 18:36:47Z

2

awk '{print "INSERT INTO users (email,paypal_tran,CCReceipt) VALUES"; print "(\x27"$1"\x27,\x27"$2"\x27,\x27"$3"\x27);"}' input.txt

Converts your sample input to preferred output. It should work for multi line input.

EDIT

The variables you are using in this line:

cat temp1 | awk 'email="$1"; transaction="$2"; ccreceipt="$3";'

are only visible to awk and in this command. They are not shell variables. Also in your sed commands remove those single quotes then you can get the values:

sed "s/EMAIL/$email/"

edited Apr 16, 2014 at 18:36

answered Apr 16, 2014 at 17:58

a5hk

7,9043 gold badges29 silver badges41 bronze badges

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

3 Comments

Robert D Weir Over a year ago

I am trying this approach but I still get the $email and not its value [email protected]. I cannot seem to get the values but the names of the input. My assignment must be wrong.

a5hk Over a year ago

This works if you have a file which its content is like the one that you posted as sample input

Robert D Weir Over a year ago

Thank you. This seems to have resolved my problem. I appreciate the effort and code!

sat · Accepted Answer · 2014-04-16 18:03:58Z

1

You can try this bash,

while read email transaction ccreceipt; do echo "INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('$email','$transaction','$ccreceipt');"; done<inputfile

inputfile:

[email protected] 2w4e5r6t7y8u9i8u7 1111-2222-3333-4444
[email protected] 2dsdsda53563u9i8u7 3333-4444-5555-6666

Test:

sat:~$ while read email transaction ccreceipt; do echo "INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('$email','$transaction','$ccreceipt')"; done<inputfile
INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('[email protected]','2w4e5r6t7y8u9i8u7','1111-2222-3333-4444')
INSERT INTO users (email,paypal_tran,CCReceipt) VALUES ('[email protected]','2dsdsda53563u9i8u7','3333-4444-5555-6666')

answered Apr 16, 2014 at 18:03

sat

15k7 gold badges49 silver badges69 bronze badges

1 Comment

Idriss Neumann Over a year ago

@user3386373 : don't forget to vote on answers that you find useful even if it's not those who fix the issue at first ;)

Rizwan Ahmed · Accepted Answer · 2014-04-16 18:04:10Z

0

You can write a small procedure for this

CREATE PROCEDURE [dbo].[appInsert]--
@string VARCHAR(500)
AS
BEGIN

DECLARE @I INT  
DECLARE @SubString VARCHAR(500)
SET @String = '[email protected] 2w4e5r6t7y8u9i8u7 1111-2222-3333-4444'
SET @I = 1  
SET @String = REPLACE(@String, ' ', '`~`')

    WHILE @I > 0
        BEGIN
        SET @SubString = SUBSTRING (REVERSE(@String), 1, ( CHARINDEX( '`~`', REVERSE(@String)) - 1))
        SET @String = SUBSTRING(@String, 1, LEN(@String) - CHARINDEX( '`~`', REVERSE(@String)) - 2 )
        print REVERSE(@SubString) + ' === ' + @String
        SET @i = CHARINDEX( '`~`', @String)
        END

END

answered Apr 16, 2014 at 18:04

Rizwan Ahmed

1462 silver badges9 bronze badges

Collectives™ on Stack Overflow

string substitution from text file to another string

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related