4

I have a while loop which reads data from a file with city names:

city_name:

COIMBATORE
MADURAI
PONDICHERRY
SALEM
TRIPUR
TIRUCHI
TIRUNELVELI
TUTUCORIN
VELLORE

and using an awk command within the while loop and trying to read the variable defined in while loop body again in the awk statement however, this is not working and giving me errors. I understand that the awk requires the variables to be defined for its body separately to enable awk to understand the variable usage and could read its value.

My while loop is like the following:

while read city
do
        awk -F, '{
                        if ( $1 == "ACTIVE" ) &&
                        ( $2 == "$city" )
                        print $1
                }' siteDBName >> count

        SUM=`awk '{ sum += $1 } END { print sum }' count`

done < city_name

where the siteDBName file contains the multiple columns with data like the following:

siteDBName:

ACTIVE,COIMBATORE,MGT500,1,5,7....
INACTIVE,MADURAI,GT500,5,6,7...
ACTIVE,SALEM,TR600,6,4,6...
ACTIVE,COIMBATORE,GT500,4,5,6...
..
..

Here, i tried using the awk -v c=$city along with rest of the statements but this too gave me errors.

How can i use the variable used and initialized in the while loop inside the awk statement inside this while loop?

Ankit Vashistha
  • 3,745
  • 1
    Don't use $city, with awk -v c=$city, you must use variable c inside awk instead. Your best choice should be passing through environment variable c=$city awk ... then inside awk, use ENVIRON["c"] – cuonglm Jul 28 '15 at 06:21
  • 2
    What kind of error do you get when using awk -v c=$city? as cuonglm states you must use c inside awk to referer to the while loop's city. Have in mind that c inside awk must be referenced without the $ (it's not bash, it's awk) just c (for instance {print c}) – YoMismo Jul 28 '15 at 06:36

4 Answers4

11

You have two basic choices: i) use -v to pass the variable to awk or ii) close the ' around the awk script, use the shell variable and continue the ' again.

  1. Use -v

    while read city
do
    awk -v city="$city" -F, '{
                              if ( $1 == "ACTIVE"  && $2 == city ){print $1}
                             }' siteDBName >> count
done < city_name

  2. Close the quote

    while read city
do
    awk  -F, '{
                 if ( $1 == "ACTIVE"  && $2 == "'$city'" ){print $1}
              }' siteDBName >> count
done < city_name

I removed the SUM= line since it wasn't doing anything useful given that $1 is ACTIVE so adding it makes no sense. See my alternate approach below for a way to do this correctly.

Also, note that with this approach, you need to read the siteDBName file multiple times. A more efficient version would be:

$ awk -F, '{
             if(NR==FNR){cities[$1]++;}
             else if($1=="ACTIVE" && $2 in cities ){sum++}
            } END{print sum,"active cities"}' city_name siteDBName 
3 active cities
jesse_b
  • 37,005
terdon
  • 242,166
  • Hi @terdon, thank you for the answer! However, could you explain how "'$city'" works here? – Ch'en Meng Nov 21 '16 at 09:09
  • @Ch'enMeng in the while loop? When you want to iterate over a file, you do while read variable; do command $variable; done < file or, to iterate over a list of files, say, you can do for variable in *; do command $variable; done. See, for example https://bash.cyberciti.biz/guide/While_loop. Or just search for "shell loop tutorial". – terdon Nov 21 '16 at 11:40
  • sorry for my unclear question. Indeed, I know the use of while loop. Actually, I wonder to know how the shell variable $variable works in awk. You know, using $variable directly in awk is not going to work, but why $variable with a pair of single quotes and a pair of double quotes does work? – Ch'en Meng Nov 21 '16 at 14:56
  • @Ch'enMeng I am passing the variable to awk using its -v option – terdon Nov 21 '16 at 15:06
  • 1
    @glennjackman uhm. Good question. In my defense, it was a while ago. I removed that line, thanks. – terdon Jan 25 '18 at 19:09
  • Thank you for the answer ! – Apurv Sep 19 '20 at 18:41
2

Just as @cuonglm and @YoMismo stated, you are using the wrong variable and the wrong way to reference it. It should be something like:

while read city
do
        awk -v c="$city" -F, '{
                        if ( $1 == "ACTIVE"  && $2 == c )
                        print $1
                }' siteDBName >> count
        SUM=`awk '{ sum += $1 } END { print sum }' count`

done < city_name
terdon
  • 242,166
Isaac
  • 392
  • You can use the "edited" link, that takes you to the edit history. And you can leave a comment to anyone who edited your question by adding a @ before their name: @terdon. – terdon Jul 28 '15 at 07:44
1

If you interested just in SUM

grep -cf <(sed s/^/\^ACTIVE,/ city_name) siteDBName

or for big files

grep -f city_name siteDBName | grep -c ^ACTIVE
Costas
  • 14,916
-2

Try below

awk '/1CORP/ && /'$Param1'/ && /REGN/ && /'$Param2'/{print NR":"$0}' $lInputFile

The above command will take $Param1 as first parameter and $Param2 as second parameter and will do a search for below criteria

'LCORP%%REGN% in the file passed in the variable $lInputFile and will print the line which matches entire string with the line number.

jesse_b
  • 37,005
Renjith Thattakath
  • 1