awk taking file names instead of files in for loop

Question

Okay so I need to extract a certain column with awk from a certain file, put it in an array and then sort it, and afterwards I would need to look up some values within these extracted sorted columns with awk too, but for now I have some problems with my for loop:

for var in $1 $2
do
myarr=($(awk -v row=$3 -F';' '$row!="" {print $row}' $var))
sorted_array=( $( printf "%s\n" "${myarr[@]}" | sort -n ) )
echo "${sorted_array[@]} $var"
done

The output is:

 dbdump.csv
 dbdump2.csv

which are the names of the two csv files I want to extract the column from . If anyone could provide some kind of solution it would be much appreciated because i need this script to search stuff. Also if you can suggest an algorithmically faster approach please do, this was just me learning some bash scripting and trying to put together some code.

Input files contain records like this, and I have two of those files that don't have matching values in column 3(that's what my manager said):

1101590479;Frank Haemers;;20060310;1;RESI;;01;06;0007;0000000000;;CRM000;
1101590473;Van KetsmJan;;20060310;2;PROF;;01;08;;0000000000;75;CRM000;0686143950

The two files have around 5 million of those records. I have another file with a certain amount of patterns that must be looked up those two huge csv files, and if one of those patterns matches in either of files I need to output into another file something like:

echo "$pattern has been found in $file"

I need to do this for all the patterns found in my patterns text file

Answer 1

When writing a shell script, it is best to specify the verified variables first, and filenames last, so you can vary the number of files specified. In your case, you have the column number, a file with the patterns in it, and two (or perhaps more) file names to work on. So, start your Bash script with

#!/bin/bash
if [ $# -lt 2 ] || [ "$1" = "-h" ] || [ "$1" = "--help" ]; then
    echo ""
    echo "Usage: $0 [ -h | --help ]"
    echo "       $0 COLUMN PATTERNFILE [ FILE(s) ... ]"
    echo ""
    exit 0
fi

The if clause above uses old-style POSIX shell formatting, and will work in dash (and other POSIX shells) as well as in most old-style sh shells as well. The intent is that if the user does not specify any command line arguments, or just a -h or --help, the script just prints a short help text.

You should expand on the help text, by the way, because it makes it much easier to find out what it does in two or three months, after you've forgotten you wrote it. (Happens to me all the time, and I have lots of such scriptlets, so I've found this practice well worth the little effort.)

Next, extract the required parameters (only one, above), and shift them out, so that we can use "$@" to refer to all file names specified on the command line:

column=$1
patternfile="$2"
shift 2

Note that I like to put double quotes around stuff I want expanded in the shell, even when not explicitly necessary. This is because most real-life problems I encounter with shell scripts are due to forgetting quoting an expansion, when it would have been necessary. This practice is easy to remember, and other than having some know-it-all comment that "you don't actually need those double-quotes there" in an annoying nasal tone, they do no harm.

Lets then use awk to process the input files:

awk -v column=$column \
  'BEGIN {
       RS = "[\t\v\f ]*(\r\n|\n\r|\r|\n|)[\t\v\f ]*"
       FS = "[\t\v\f ]*;[\t\v\f ]*"
   }

The backslash at the end of the first line above just tells the shell that the command continues on the next line. Also note that there is no closing single quote ' , so the lines below are actually continuation to the command-line string parameter we are supplying to awk.

The BEGIN rule in awk is executed before the files are processed. The above RS sets the record separator to any newline convention, and includes any leading or trailing whitespace on each line. Similarly the field separator is a semicolon, but including any whitespace surrounding it. Thus, a ; b has two fields, the first being a and second b, neither having any whitespace.

I use the following idiom to keep track of which input file is being processed:

    FNR==1 { ++filenum }

If just means that for the very first record in each input file we process, we increment the filenum variable. Incrementing an uninitialized variable is the same as incrementing a zero, so we get 1 for the first input file, and so on.

We want to just remember the contents of each line in the first input file, our pattern file:

    filenum==1 { pattern[$0] }

Awk arrays are associative, so we can just use an associative array to hold the known patterns. Above, we use a funny awk feature to our advantage: If you try to access an associative array entry that does not exist yet, awk creates it!

For the rest of the files, we just check if the field $column (supplied to the awk scriptlet in awk variable column) matches (exactly) any of the patterns seen in the first file, and if so, we print the entire record:

    filenum > 1 && ($column in pattern) { printf "%s\n", $0 }

Above, $column has a different meaning compared to a shell script. Here, column is a variable, and $column expands to the value of the column'th field in the current record (zeroth column being the entire record, however). The foo in array syntax is awkism for checking if array contains a key foo. So, overall, for the second and further input files, if the column'th field value was listed in the first input file, the record is printed. to standard output.

We are still in the awk command-line parameter string, and need to close the single-quoted string. We also want to supply it with the file names:

    ' "$patternfile" "$@"

which concludes this awk scriptlet.

Answer 2

If you just want to take a list of patterns and a set of files and print out the names of all files that matched each pattern in a specific column, all you need is GNU awk (default on Linux):

awk -F';' '{
                if(NR==FNR){ 
                    p[$0]++; 
                    next
                } 
                if($3 in p){
                    printf "%s found in %s\n", $3,FILENAME; 
                    nextfile
                }
            }' patterns file1.csv file2.csv fileN.csv

Explanation

• awk -F';' : set the field separator to ;.
• if(NR==FNR){ p[$0]++;next} : NR is the current input line number and FNR is the current file's line number. The two are equal only while the first file is being processed. This will therefore save each line of the patterns file (the 1st file) in the array p and go to the next line. It will only be run for the patterns file.
• if($3 in p){printf "%s found in %s\n", $3,FILENAME; nextfile: Now we're looking at the csv files. if the 3rd field is one of the elements in the array p (if it was in the patterns file), print the 3rd field (the pattern) and the filename it was found in. Then, skip to the next file. The FILENAME variable holds the path of the file currentloy being processed. The nextfile is a gawk feature and does what it says on the tin: it skips to the next file to be processed.

For example, given these files:

$ cat patterns 
foo
bar
baz

$ cat file1.csv 
blah;blah;foo;blah
blah;blah;foo;blah
blah;blah;foo;blah

$ cat file2.csv 
blah;blah;bar;blah

$ cat file3.csv 
blah;blah;baz;blah

You will get this output:

$ awk -F';' '{if(NR==FNR){p[$0]++; next} if($3 in p){printf "%s found in %s\n", $3,FILENAME; nextfile}}' patterns file*csv 
foo found in file1.csv
bar found in file2.csv
baz found in file3.csv

If you can have each pattern present in multiple files, you can use a slightly different approach:

awk -F';' '{
            if(NR==FNR){ 
                p[$0]++; 
                next
            } 
            if($3 in p && !seen[FILENAME][$3]){
                printf "%s found in %s\n", $3,FILENAME; 
                seen[FILENAME][$3]++
            }
        }' patterns file1.csv file2.csv fileN.csv

This time, there's no nextfile since we need to process the entire file and there's a counter that is incremented each time a pattern has been found in a given file so we don't report the same pattern multiple times.

So, changing the file1.csv above to:

$ cat file1.csv 
blah;blah;foo;blah
blah;blah;baz;blah
blah;blah;bar;blah
blah;blah;foo;blah

We get:

$ awk -F';' '{if(NR==FNR){p[$0]++; next} if($3 in p && !seen[FILENAME][$3]){printf "%s found in %s\n", $3,FILENAME; seen[FILENAME][$3]++}}' patterns file*csv 
foo found in file1.csv
baz found in file1.csv
bar found in file1.csv
bar found in file2.csv
baz found in file3.csv

If this is too slow, as it might be for huge files, you can modify it so that it stops reading a file if all patterns have already been found in it:

awk -F';' '{
            if(NR==FNR){ 
                p[$0]++; 
                next
            } 
            if($3 in p && !seen[FILENAME][$3]){
                printf "%s found in %s\n", $3,FILENAME; 
                seen[FILENAME][$3]++
            }
            if( length(seen[FILENAME]) == length(p) ){
                nextfile
            }
           }' patterns file1.csv file2.csv fileN.csv