shell variable in awk

Shell Programming and Scripting
1

Hi All,

How can i use the file for printing in awk

file1
----------
update table crn_ras_disc_dtl a set a.a5=$1,a.a1=$2,a.a2=$3,a.a3=$4,a.a4=$5;

file2
--------
10|KUMAR|23|MALE|US
20|RAJ|24|MALE|AU

Output
---------
update table crn_ras_disc_dtl a set a.a5=10,a.a1=KUMAR,a.a2=23,a.a3=MALE,a.a4=US;
update table crn_ras_disc_dtl a set a.a5=20,a.a1=RAJ,a.a2=24,a.a3=MALE,a.a4=AU;

2 
awk -F'|' '
   BEGIN { getline in_stmt < "file1"; }
   {
     nVars=split($0, values);
     out_stmt=in_stmt;
     for (i=1; i<=nVars; i++) {
        gsub("\\$" i, values, out_stmt);
     }
     print(out_stmt);
   }
' file2
3

Hi robotronic,

Thanks for your script.There is a problem in the script.
Eg:
If i try to two digit number field
(For Eg:$20 in the file 1.Iam getting the value as $2"1") ,iam getting the wrong result.

regards,
cskumar

4

Hi All,

I got the solution by reversing the for loop.

for (i =nVars; i>=1; i--)

For each record looping is done for number of fields.Currently i have 92 fields for file2.

If I am looping for 92 times,wont my performance decreases.Cant i have any alternative solution.

Please help me.

regards,
cskumar.

5

i think you have a design issue. I find it easier to construct the statement on the fly.
If need be you can pipe to output file.

awk 'BEGIN{FS="|"}
{ cmd = "update table crn_ras_disc_dtl a set a.a5="$1",a.a1="$2",a.a2="$3",a.a3="$4",a.a4="$5";"
  print cmd ## > "file"
  ## whatever you want to do with cmd
}
' "file2"
6

If you don't have multiple occurrences of the same variable "$n" in file1, you can substitute the "gsub" command with "sub": this may increase performances a bit. Note that, in this manner, the original increasing for loop works well too.
Otherwise, as stated by ghostdog, you'll have to hardcode the update statement in the awk script.

7

Hi, try this one( i use solaris)

while read line
do
cat file2 | nawk  'BEGIN{FS="|"}
{
l="'"$line"'"
gsub(/\$1/,$1,l)
gsub(/\$5/,$5,l)
gsub(/\$4/,$4,l)
gsub(/\$3/,$3,l)
gsub(/\$2/,$2,l)
print l
}'
done < file1
8

Hi ghostdog,

As far i know there is no design issue.For your understanding let me explain.

I have One input file SOURCEFILE,keyfields=1|2|3,delimiter=|
I have three lookupfiles JOINFILE1,JOINFILE2,JOINFILE3 which are having the keyfields 3,4,5 and delimiters :,;,| respectively

based on the above files,i have to make one script file.
the sample script file shows like this
SCRIPTFILE
-------------
update table crn_ras_disc_dtl a set a.a5=SOURCEFILE.2,a.a1=JOINFILE1.3,a.a2=JOINFILE3.5,a.a3=JOINFILE2.3,a.a4=JOINFILE1.2;

For each lookup file
Join source file and the lookupfile with the keyfields and the respective delimiters
Append the Outpufield(s) by referring the SCRIPTFILE and its field (Append $3 of JOINFILE1 with the at the last of the input file) at the last of input file.
Change the sample script file and change the position
[ Assume SOURCEFILE has 20 fields. Eg: update table crn_ras_disc_dtl a set a.a5=SOURCEFILE.2,a.a1=JOINFILE1.3,a.a2=JOINFILE3.5,a.a3=JOINFILE2.3,a.a4=JOINFILE1.2;
to be replaced as update table crn_ras_disc_dtl a set a.a5=SOURCEFILE.2,a.a1=SOURCEFILE.21,a.a2=JOINFILE3.5,a.a3=JOINFILE2.3,a.a4=JOINFILE1.2;
]
done

Run the SCRIPTFILE by referring the LAST JOIN and its position.

Please suggest me.

9

cskumar to be honest, I didn't understand very well your last explanation. However, have you thinked about inserting all the informations in database's tables and do the joins inside the db? As I said before I didn't get the point of your problem very well and maybe this approach isn't possible but... I wished to share my thought :slight_smile:

10

hi

some observations:

1) i think in all sql-dialects you must enclose a string in some kind of quotes
2) the result of executing such a sql-file will be the same as executing only the last update, perhaps you want to insert the data
3) if you want to insert data most database systems have tool to load text-files efficiently, e.g oracle has sqlldr

mfg guenter

11

probably shouldn't say this but, cat is useless here.

12

cskumar , can you please elaborate more on the question please ????

your last explanation with sample confused a lot.......:slight_smile:

13

Perhaps this.

[/tmp]$ cat file1
update table crn_ras_disc_dtl a set a.a5=$1,a.a1=$2,a.a2=$3,a.a3=$4,a.a4=$5;
[vivarkey@/tmp]$ cat file2
10|KUMAR|23|MALE|US
20|RAJ|24|MALE|AU
[/tmp]$ cat try.sh
#! /bin/sh

FINAL="$(<file1)"

while read line
do
    set -- ${line//|/ }
    eval echo $FINAL
done < file2
[/tmp]$ ./try.sh
update table crn_ras_disc_dtl a set a.a5=10,a.a1=KUMAR,a.a2=23,a.a3=MALE,a.a4=US
update table crn_ras_disc_dtl a set a.a5=20,a.a1=RAJ,a.a2=24,a.a3=MALE,a.a4=AU
[/tmp]$