I've this plain text file that I need to be automatically examined and results written in another plain text file. Note that I'll use plain text and CSV interchangeably, since CSVs are plain text with common separator between data (commas, tabs, etc).

Data examples


Process one - recognize the first two sections of the data (text-number) to repeat and count them in output CSV:


Process two - First part of the data gathered above has sort of a primary key assigned to them on a different file. I would like to get the primary keys mentioned in the newly generated files as well:

#primary key file

#newly generated file

So basically, what I'm asking her is if there's any software that can help me with this on either Windows or Linux-based operating systems. Bash can help me with simpler processing, but I don't think it can handle this much. Please advise.

  • Can the same data occur multiple times in the same file? (for example, "ABD-01A, ABD-01B, AL-25A, ABD-01A"...) – grawity Jan 25 '12 at 15:22
  • @grawity No, all the data are unique. – Oxwivi Jan 25 '12 at 16:27
  • In that case, you could use the example scripts I provided in my other comment. (Both do the same, but written in different languages so you can compare.) – grawity Jan 25 '12 at 17:34
  1. Choose your preferred language – Perl works well with text; Python, PHP and Ruby are okay, if a little slower.
  2. Read the key file first:
    • split each key,data line into key and data,
    • then store both in a dict/hash/array: keys[data] = key
    • also counts[data] = 0 if the language demands initialization
  3. Read all data files:
    1. use a regexp to find the "TEXT-NUMBER" data at the beginning of every line,
    2. increment counts[data] by one,
    3. and immediately output keys[data], the line, and counts[data].

I wrote an example script in both Perl and Python, so your choice:


#!/usr/bin/env perl
use strict;

sub usage {
    print "Usage: $0 <key_file> <data_file...>\n";
    return 2;

my $keys_file = shift(@ARGV) // exit(usage);
my @data_files = @ARGV;

my %keys;   # $data => $primarykey
my %counts; # $data => $count

# Read keys

open(my $fh, "<", $keys_file) or die "$!";
while (my $line = <$fh>) {
    my ($key, $datum) = split(/,/, $line, 2);
    if (!defined $datum) {
        warn "$keys_file: line $. not in KEY,DATA format: '$line'\n";

    $keys{$datum} = $key;
    $counts{$datum} = 0;

# Read and output data

my $ex = qr/^(\w+-\d+)\w*/;
for my $data_file (@data_files) {
    open(my $fh, "<", $data_file) or die "$!";
    while (my $line = <$fh>) {
        if ($line =~ /$ex/) {
            my $datum = $1;
            if (!defined $keys{$datum}) {
                warn "no primary key found for data '$datum'\n";

            # Increment count, then print processed line immediately
            print "$keys{$datum},$&,$counts{$datum}\n";
        else {
            warn "$data_file: line $. does not begin with TEXT-NUMBER: '$_'\n";


#!/usr/bin/env python
from __future__ import print_function
import sys
import re

def usage():
    print("Usage: %s <key_file> <data_file...>" % sys.argv[0])
    return 2

    keys_file = sys.argv[1]
    data_files = sys.argv[2:]
except IndexError:
except ValueError:

keys = {}
counts = {}

# Read keys

for line in open(keys_file, "r"):
        key, datum = line.strip().split(",", 1)
    except ValueError:
        print("%s: line not in KEY,DATA format: %r" \
            % (keys_file, line.strip()), file=sys.stderr)

    keys[datum] = key
    counts[datum] = 0

# Read and output data

ex = re.compile(r'^(\w+-\d+)\w*')
for data_file in data_files:
    for line in open(data_file, "r"):
        line = line.strip()
        m = re.match(ex, line)
        if m:
            datum = m.group(1)
            if datum not in keys:
                print("no primary key found for data %r" % datum,

            # Increment count, then print processed line immediately
            counts[datum] += 1
            print("%s,%s,%d" % (keys[datum], m.group(0), counts[datum]))
            print("%s: line does not begin with TEXT-NUMBER: %r" \
                % (data_file, line.strip()), file=sys.stderr)

I would do it in Python using regular expressions. Just type python in your shell to see if it is installed.

Otherwise you can use Perl. Just type perl in your shell to see if it is installed. Is has built-in support of regular expressions.

  • Can you provide some examples on how to use it? – Oxwivi Jan 25 '12 at 14:45
  • @Oxwivi: Here are a few, but a language cannot be learned from only "some examples"... "Learning Perl" and similar books might be a good start. – grawity Jan 25 '12 at 15:36
  • @grawity Sorry for getting back at you so late, I faced unexpected issues generating the data. I did this: perl process.pl */images products.csv > images - multiple data files, but no conflicts. However the results were without the keys - for example: ,ABD-47,2. The area reserved for the key value is empty. Also, if you will, please add an answer of your own, since it's likely I will choose yours. – Oxwivi Jan 27 '12 at 19:08
  • @grawity Only the first */images file encountered is processed. Referring directly to that file works as expected. – Oxwivi Jan 27 '12 at 19:11
  • @grawity What exactly is unmatched input supposed to mean? It found no match in the key file? – Oxwivi Jan 27 '12 at 19:23

Process 1

perl count.pl datafile …

where count.pl is something like

use strict;
use warnings;

my %headwordcount;

while (<>) {
  if (/^([A-Z]+-\d+)/) { $headwordcount{$1}++; }
  # else { warn "Bad data: $_"; } # uncomment line for data warnings

  foreach (sort keys %headwordcount) {
     print "$_,$headwordcount{$_}\n";

Untested, caveat emptor.

Process 2

Add something like

   my %key;

   BEGIN {
     my $keyfilename = 'primary.key';
     open my $fh, '<', $keyfilename or die "Can't read '$keyfilename' - $!\n";
     while (<$fh>) {
        my ($key,$headword) = split(/,/, $_, 2);
        $key{$headword} = $key;        
     close $fh;

and change the print line in END {} to

   print "$key{$_},$_,$headwordcount{$_}\n";

Again, untested.

You'll generate warnings if the data file contains headwords not in the key file. You can check for $key{$_} being undefined and if so print w/o key.

  • What is that script supposed to do? The first process I described? – Oxwivi Jan 25 '12 at 16:35
  • @Oxwivi: Yes. If you can't easily follow the code. It may be best to try a different answer. If you understand bash-scripting and/or awk, Perl should not be too difficult. – RedGrittyBrick Jan 25 '12 at 16:38
  • I did use basic bash scripts, though I can't say I followed them well (inline syntaxes, mainly). And do I add the new codes you edited into the answer indented? – Oxwivi Jan 25 '12 at 16:41
  • @Oxwivi, yes, add the new stuff immediately after my %headwordcount; (apart from the replacement print line of course, which replaces the one inside the ÈND … foreach loop). – RedGrittyBrick Jan 25 '12 at 16:56
  • Okay, thank you very much! One last thing - datafile is to be replaced with the input file, that is obvious, but is the ... following it some Perl specific things? – Oxwivi Jan 25 '12 at 17:05

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.