Visit Our UNIX and Linux User Community


Passing parameter to awk command


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing parameter to awk command
# 1  
Old 07-09-2013
Passing parameter to awk command

Hi,

I have a situation where I need to create a SQL statement using Unix script for each value in first field (ID). The file looks like this.

Id,TARGET_FIELD_NAME,SOURCE_FIELD_NAME
1,Test_Rate,Field1
1,Test_Factor,Field2
1,Test_Size,Field3
2,Test_Rate,Field4
2,Test_Factor,Field5
2,Test_Size,Field6

So I need to create a insert script which would look like this.

INSERT INTO MYTABLE (<List Column3>) SELECT <List Column5> SOURCE;


Expected output -
ID - 1:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field1, Field2, Field3 FROM SOURCE;


ID - 2:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field2, Field3, Field4 FROM SOURCE;


The Current code I have generates this output, but I am unable to use the loop variable in awk. (See highlighted)

Code:
Id=`awk -F"," '{print $1}' AboveFile.txt | sort -u`
for i in $Id
do

insertlist=`awk -F"," '{if ($1 == 1) print $2 ","}' AboveFile.txt`
selectlist=`awk -F"," '{if ($1 == 1) print $3 ","}' AboveFile.txt`

echo "INSERT INTO MYTABLE ($insertlist) SELECT $selectlist FROM SOURCE;"

done

I am unable to parameterize the highlighted part of the code to use the loop variable i.

insertlist=`awk -F"," '{if ($1 == $i) print $2 ","}' AboveFile.txt`
selectlist=`awk -F"," '{if ($1 == $i) print $3 ","}' AboveFile.txt`

If there is anyway I can do this, I will be able to get my expected result. Is there a way I can parameterize this awk statement to use the loop variable? Please suggest.

Thanks in advance for sharing your thoughts

---------- Post updated at 11:02 PM ---------- Previous update was at 11:00 PM ----------

Sorry, I noticed that the expected result is wrong for ID2.
Here is the right expected result -

Expected output -
ID - 1:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field1, Field2, Field3 FROM SOURCE;


ID - 2:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field4, Field5, Field6 FROM SOURCE;
# 2  
Old 07-09-2013
try..

Code:
awk -F, 'NR>1{A[$1]=A[$1]","$2;B[$1]=B[$1]","$3}END{for(i in A){sub(",","",A[i]);sub(",","",B[i]);print "INSERT INTO MYTABLE ("A[i]") SELECT "B[i]" FROM SOURCE;"}}' filename

# 3  
Old 07-09-2013
Another approach:
Code:
awk -F, '
        NR > 1 {
                C[$1] = C[$1] ? C[$1] OFS $2 : $2
                F[$1] = F[$1] ? F[$1] OFS $3 : $3
        }
        END {
                for ( k in C )
                {
                        print "ID - " k ":"
                        print "INSERT INTO MYTABLE (" C[k] ") SELECT " F[k] " FROM SOURCE;"
                }
        }
' OFS=, file

# 4  
Old 07-09-2013
Parameter can be hacked into awk like this
Code:
insertlist=`awk -F"," '{if ($1 == '"$Id"') print sep $2; sep=","}' AboveFile.txt`

or passed like this
Code:
insertlist=`awk -F"," '{if ($1 == id) print sep $2; sep=","}' AboveFile.txt id="$Id"`

or
Code:
insertlist=`awk -v id="$Id" -F"," '{if ($1 == id) print sep $2; sep=","}' AboveFile.txt`

Your method is inefficient: if your file is long it is read too often.

Previous Thread | Next Thread
Test Your Knowledge in Computers #28
Difficulty: Easy
Unix was the first fully supported 64-bit operating system for personal computers.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Loop over awk or passing parameter

I wrote this script which works well when I manually input 55518622 and 1 but I need this script to be generic and loop over the following table awk '$4>(55518622-500000) && $4<(55518622+500000)' chr1_GEN2bim | awk 'BEGIN {min=1000000000; max=0;}; {\ if($4<min && $4 != "") min = $4; if($4>max... (8 Replies)
Discussion started by: fat
8 Replies

2. UNIX for Dummies Questions & Answers

Passing shell script parameter value to awk command in side the script

I have a shell script (.sh) and I want to pass a parameter value to the awk command but I am getting exception, please assist. diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff |... (1 Reply)
Discussion started by: Sarita Behera
1 Replies

3. Shell Programming and Scripting

Passing command as a function parameter

Hi All, Just trying to implement the below shell script using AIX ksh shell. myfunc { eval "$*" } CMD='ls -la /etc/hosts | awk '{print $9"|"$5}'' myfunc $CMD Keeping getting "|}: not found" errors, any pointers would greatly be appreciated. Kind Regards Ed Please... (2 Replies)
Discussion started by: eo29
2 Replies

4. UNIX for Dummies Questions & Answers

Passing a Unix parameter to SQLPlus login command

hi All, i m trying to pass a user choice paramter from unix to sqlplus connect command here i want the user to enter the username and password he wants to connect in sql plus through read in unix and then automatically connect to that instance. sqlplus -s $1/$2 where $ 1 and $2 will b... (2 Replies)
Discussion started by: Jcpratap
2 Replies

5. Shell Programming and Scripting

Error passing parameter in "sub" command in awk

I have to replace the pattern found in one file in another file with null/empty "" if found on the fields 3 or 4 ONLY File 1 ==== 10604747|Mxdef|9999|9999|9999|2012-03-04 00:00:59 10604747|Mcdef|8888|9999|8888|2012-03-04 00:00:59 . . . File 2 ==== 9999 8888 . . . Expected... (7 Replies)
Discussion started by: machomaddy
7 Replies

6. Shell Programming and Scripting

Passing parameter in sed or awk commands to print for the specific line in a file

Hi, I am trying to print a specific line in a file through sed or awk. The line number will be passed as a parameter from the previous step. My code looks as below. TEMP3=`sed -n '$TEMP2p' $FILEPATH/Log.txt` $TEMP2, I am getting from the previous step which is a numerical value(eg:3). ... (2 Replies)
Discussion started by: satyasrin82
2 Replies

7. UNIX and Linux Applications

Passing date parameter on Kshell command line

I need to execute a .ksh from command line. The ksh calls a control file and has 3 parameters. First parameter is a csv file, second the target table in oracle and third parameter is a date parameter. I am attempting the below from the ksh command line {code} => testfile.ksh filname.csv... (1 Reply)
Discussion started by: kobe24
1 Replies

8. Shell Programming and Scripting

Passing a parameter to AWK

Hi All, I am trying to pass a parameter to AWK on my KSH shell prompt as below. var1=2 echo $var1 awk -v var2=${var1} '{print var2}' testfile.txt I am passing the input file (testfile) to awk to get some o/p. It is having 10 records. When I run AWK, it is throwing the following errors... (1 Reply)
Discussion started by: Raamc
1 Replies

9. Shell Programming and Scripting

AT command parameter passing to php file

I have a bash script which utilizes a random function and then runs a file at now plus a random time. The problem is, that the php file requires a parameter after it eg: phpfile.php?code=123245b3 When i put in the file including the full path, with the at command, it will run, but not with... (1 Reply)
Discussion started by: thruxmore
1 Replies

10. UNIX for Dummies Questions & Answers

Passing Parameter in SED command

Hi, I am trying to replace a URL by another URL in the SED command e.g. cat dir/filename1 | sed -e 's/"http:\/\/*dtd"/"http:\/\/abc.def.com\/xyz.dtd"/' > dir/newfile.xml But I need to pass a parameter to the SED command which should have the new url string i.e.... (2 Replies)
Discussion started by: dsrookie
2 Replies

Featured Tech Videos