sorting flat file databases

lisaw has asked for the wisdom of the Perl Monks concerning the following question:

I'm pulling my hair out with this db problem that I can't figure out. Using a flat db file I'm trying to list out entries in alphabetical order using the second entry

This being my $database file data:
ENTRY_A|ENTRY_B|ENTRY_C
[download]

I know how to sort the DB using the first entry...Here's what I get to work with:

open (ORGDB,"<$database");
@ODB=<ORGDB>;
close (ORGDB);

@ODB = sort(@ODB);

foreach $rec (@ODB){
    chomp($rec);
    ($ENTRY_A,$ENTRY_B,$ENTRY_C)=split(/\|/,$rec);

print "
$ENTRY_A <BR>
$ENTRY_B <BR> Sorted by ENTRY_B
$ENTRY_C <BR>

\n";
[download]

Is there an easy way to sort through the database to list the second entry in alphabetical order? ENTRY_B Any answers? Anyone have the phone number to Dr. Kavorkian? :) Thanks

update (broquaint): title change (was Pulling My Hair Out...)

Comment on sorting flat file databases Select or Download Code

Replies are listed 'Best First'.
Re: Pulling My Hair Out... by fruiture (Curate) on Nov 22, 2002 at 18:19 UTC
Well, create a data structure and THEN sort it: `# fully qualified open call: open my $orgdb , '<' , $database or die "$database open error: $!"; my @odb = map { chomp; [ split /\\|/,$_ ] } <$orgdb>; close $orgdb; for( sort { $a->[1] cmp $b->[1] } @odb ){ printf "A: %s B(sorted): %s C: %s\n",@$_ }` [download] See `perldoc perllol` and of course `perldoc -f ?`, where ? is first 'map', later 'chomp' and finally 'open'. -- http://fruiture.de	[reply] [d/l]
Re: Pulling My Hair Out... by rdfield (Priest) on Nov 22, 2002 at 18:21 UTC
1. Read the file in one line at a time 2. Split each line 3. Push a reference to the resulting array onto `@ODB` 4. sort the array... `print "$_->[0]<BR>$_->[1]<BR>$_->[2]<BR>" foreach (sort {$a->[1] cmp $b->[1]} @ODB);` [download] rdfield	[reply] [d/l] [select]
Re: Pulling My Hair Out... by dreadpiratepeter (Priest) on Nov 22, 2002 at 18:29 UTC
Look at DBD::CSV. It allows you to treat a flat file as a database table and use SQL on it. Read the table in then "select * from table order by entry_b" I'm in the middle of something now. But, if I have time later I will update with an example. Hope this helps, -pete "Worry is like a rocking chair. It gives you something to do, but it doesn't get you anywhere."	[reply]
Re: Pulling My Hair Out... by strider corinth (Friar) on Nov 22, 2002 at 20:33 UTC
A really good resource for this is File::Sort. You can use it to sort your file, separating your columns with anything you want, and it does most of its work very efficiently in terms of memory consumption. -- Love justice; desire mercy.	[reply]
Re: Pulling My Hair Out... by rbc (Curate) on Nov 22, 2002 at 19:57 UTC
`open( ORGDB, "\|sort -t '\|' -k2 $database");` [download]	[reply] [d/l]
Re: Pulling My Hair Out... by Fletch (Bishop) on Nov 22, 2002 at 20:29 UTC
See also `perldoc -q sort`.	[reply] [d/l]