Can we use shell variables in awk?

Can we use shell variables in AWK like $VAR instead of $1, $2? For example:

UL=(AKHIL:AKHIL_NEW,SWATHI:SWATHI_NEW)

NUSR=`echo ${UL[*]}|awk -F, '{print NF}'`
echo $NUSR
echo ${UL[*]}|awk -F, '{print $NUSR}'

Actually am an oracle DBA we get lot of import requests. I'm trying to automate it using the script. The script will find out the users in the dump and prompt for the users to which dump needs to be loaded.

Suppose the dumps has two users AKHIL, SWATHI (there can be may users in the dump and i want to import more number of users). I want to import the dumps to new users AKHIL_NEW and SWATHI_NEW. So the input to be read some think like AKHIL:AKHIL_NEW,SWATHI:SWATHI_NEW.

First, I need to find the Number of users to be created, then I need to get new users i.e. AKHIL_NEW,SWATHI_NEW from the input we have given. So that I can connect to the database and create the new users and then import. I'm not copying the entire code: I just copied the code from where it accepts the input users.

UL=(AKHIL:AKHIL_NEW,SWATHI:SWATHI_NEW) ## it can be many users like     USER1:USER1_NEW,USER2_USER2_NEW,USER3:USER_NEW..

NUSR=`echo ${UL[*]}|awk -F, '{print NF}'` #finding  number of fields or users
y=1
while [ $y -le $NUSR ] ; do
    USER=`echo ${UL[*]}|awk -F, -v NUSR=$y  '{print $NUSR}' |awk -F: '{print $2}'` #getting     Users to created AKHIL_NEW and SWATHI_NEW and passing to SQLPLUS
    if [[ $USER = SCPO* ]]; then
        TBS=SCPODATA
    else
        if [[ $USER = WWF* ]]; then
            TBS=WWFDATA
        else
            if [[ $USER = STSC* ]]; then
                TBS=SCPODATA
            else
                if [[ $USER = CSM* ]]; then
                    TBS=CSMDATA
                else
                    if [[ $USER = TMM* ]]; then
                        TBS=TMDATA
                    else
                        if [[ $USER = IGP* ]]; then
                        TBS=IGPDATA
                        fi
                    fi
                fi
             fi
        fi
    fi

    sqlplus -s '/ as sysdba'  <<EOF   # CREATING the USERS in the database 
    CREATE USER $USER IDENTIFIED BY $USER  DEFAULT TABLESPACE $TBS TEMPORARY TABLESPACE TEMP QUOTA 0K on SYSTEM QUOTA UNLIMITED ON $TBS;

    GRANT
    CONNECT,
       CREATE TABLE,
       CREATE VIEW,
       CREATE SYNONYM,
       CREATE SEQUENCE,
       CREATE DATABASE LINK,
       RESOURCE,
       SELECT_CATALOG_ROLE
    to $USER;
    EOF 
    y=`expr $y + 1`
done

impdp sysem/manager DIRECTORY=DATA_PUMP DUMPFILE=imp.dp logfile=impdp.log SCHEMAS=AKHIL,SWATHI REMPA_SCHEMA=${UL[*]} 

In the last impdp command I need to get the original users in the dumps i.e AKHIL,SWATHI using the variables.

Yes, you can use the shell variables inside awk. There are a bunch of ways of doing it, but my favorite is to define a variable with the -v flag:

$ echo | awk -v my_var=4 '{print "My var is " my_var}'
My var is 4

Just pass the environment variable as a parameter to the -v flag. For example, if you have this variable:

$ VAR=3
$ echo $VAR
3

Use it this way:

$ echo | awk -v env_var="$VAR" '{print "The value of VAR is " env_var}'
The value of VAR is 3

Of course, you can give the same name, but the $ will not be necessary:

$ echo | awk -v VAR="$VAR" '{print "The value of VAR is " VAR}'
The value of VAR is 3

A note about the $ in awk: unlike bash, Perl, PHP etc., it is not part of the variable's name but instead an operator.

Awk and Gawk provide the ENVIRON associative array that holds all exported environment variables. So in your awk script you can use ENVIRON["VarName"] to get the value of VarName, provided that VarName has been exported before running awk.

Note ENVIRON is a predefined awk variable NOT a shell environment variable.

Since I don't have enough reputation to comment on the other answers I have to include them here!

The earlier answer showing $ENVIRON is incorrect - that syntax would be expanded by the shell, and probably result in expanding to nothing.

Further earlier comments about C not being able to access environment variable is wrong. Contrary to what is said above, C (and C++) can access environment variables using the getenv("VarName") function. Many other languages provide similar access (e.g., Java: System.getenv(), Python: os.environ, Haskell System.Environment, ...). Note in all cases access to environment variables is read-only, you cannot change an environment variable in a program and get that value back to the calling script.

There are two ways to pass variables to awk: one way is defining the variable in a command line argument:

$ echo ${UL[*]}|awk -F, -v NUSR=$NUSR '{print $NUSR}'
SWATHI:SWATHI_NEW

Another way is converting the shell variable to an environment variable using export, and reading the environment variable from the ENVIRON array:

$ export NUSR
$ echo ${UL[*]}|awk -F, '{print $ENVIRON["NUSR"]}'
SWATHI:SWATHI_NEW

Update 2016: The OP has comma-separated data and wants to extract an item given its index. The index is in the shell variable NUSR. The value of NUSR is passed to awk, and awk's dollar operator extracts the item.

Note that it would be simpler to declare UL as an array of more than one element, and do the extraction in bash, and take awk out of the equation completely. This however uses 0-based indexing.

UL=(AKHIL:AKHIL_NEW SWATHI:SWATHI_NEW)
NUSR=1
echo ${UL[NUSR]} # prints SWATHI:SWATHI_NEW

There is another way, but it could cause immense confusion:

$ VarName="howdy" ; echo | awk '{print "Just saying '$VarName'"}'
Just saying howdy
$

So you are temporarily exiting the single quote environment (which would normally prevent the shell from interpreting '$') to interpret the variable and then going back into it. It has the virtue of being relatively brief.

Not sure if i understand your question.

But lets say we got a variable number=3 and we want to use it istead of $3, in awk we can do that with the following code

results="100 Mbits/sec 110 Mbits/sec 90 Mbits/sec"
number=3    
speed=$(echo $results | awk '{print '"\$${number}"'}')

so the speed variable will get the value 110.

Hope this helps.

No. You can pass the value of a shell variable to an awk script just like you can pass the value of a shell variable to a C program but you cannot access a shell variable in an awk script any more than you could access a shell variable in a C program. Like C, awk is not shell. See question 24 in the comp.unix.shell FAQ at cfajohnson.com/shell/cus-faq-2.html#Q24.

One way to write your code would be:

UL="AKHIL:AKHIL_NEW,SWATHI:SWATHI_NEW"
NUSR=$(awk -F, -v ul="$UL" 'BEGIN{print gsub(FS,""); exit}')
echo "$NUSR"
echo "$UL" | awk -F, -v nusr="$NUSR" '{print $nusr}' # could have just done print $NF

but since your original starting point:

UL=(AKHIL:AKHIL_NEW,SWATHI:SWATHI_NEW)

was declaring UL as an array with just one entry, you might want to rethink whatever it is you're trying to do as you may have completely the wrong approach.