1

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

  • 2
    This is what is known as an XY problem. You seem to be trying to use a language that is great for text processing (awk) only as a tool to extract data and do the actual processing in a language that is absolutely crap for text processing (shell). I suggest you edit your question, show us an example of your input files and your final desired output and ask for help on doing the actual processing rather than this, almost certainly unnecessary, preprocessing step. – terdon Dec 6 '16 at 10:16
  • Thanks for the edit. Need a bit more though. We also need an example of the patterns you are looking for. You mentioned sorting, is this also a requirement? What about the 3rd column? Are you only interested in matches on the third column? Should we assume columns are defined by ;? By the way, are you just looking for grep -FLf patterns.txt file1.csv file2.csv? – terdon Dec 6 '16 at 10:49
  • sorting is not required, and columns are defined by a ; and I am interested to match a column that I provide in the positional parameters and I am looking to be able to provide more than two files if necessary. – Cristian Baciu Dec 6 '16 at 11:36
2

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.

  • This would be much easier to understand if you also included the final script and not only its parts. I think you're making this more complex than necessary but can't really tell without seeing the entire script. For one thing, though, you can use FILENAME instead of fiddling with an incrementing counter. – terdon Dec 6 '16 at 11:14
  • Wow thanks, so yeah I have been fiddling with awkward stuff for this entire morning trying to resolve this, but you provided a comprehensive explanation on how would you do it and for that I thank you. Do you have any guidelines on what should I pick up first in text processing because I fathom I might have a lot of these coming. Thanks again! – Cristian Baciu Dec 6 '16 at 11:29
  • @CristianBaciu first rule: never, ever attempt to do text processing in the shell. It's just not built for it, it is very slow and will make everything harder. look into tools like sed, awk and perl instead. – terdon Dec 6 '16 at 11:42
  • @terdon: You do need to copy and paste the code together to see the final script. It was a deliberate choice, intended to ensure that anybody really interested in writing a shell-and-awk scriptlets also reads the accompanying text, rather than just take the code and use it without understanding how it works, and why it was constructed the way it is. I personally dislike the FILENAME==ARGV[n] idiom (being true if the current input file is the nth input file specified), but sure, that'd be a viable alternative to the argument numbering. – Nominal Animal Dec 6 '16 at 11:46
  • @CristianBaciu: Start with having a problem you want to solve, similar to the issue at hand. Then, take the time to browse through the GNU awk and sed manuals (introduction, notes, examples -- no need to start reading from front to back), and start working. There's probably a lot of good tutorials on them on the web, too; maybe pick one or two that suit your needs and current skill level? – Nominal Animal Dec 6 '16 at 11:51
0

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
  • Thank you both you just solved a lot of my future problems :D – Cristian Baciu Dec 6 '16 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.