• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

Delimited Text Database Help


I currently use text files as my database. I don't know how to use a delimited text file.

I am currently using the cut command to pull out my fields but this is a pain.

Here is an example of how i would populate my variable $foo with information.

system ("cut -c16-24 $file1 > $file2");

open (FILE, $file2);
$foo = <FILE>;
close (FILE);

Would somebody please help me get started using a delimited database. I understand the concept i just don't know how to pull the information out.


1 Solution
once you have opened a file try

#for each line of the file perform an action
@fields = split(/\t/, $_); #where the file is a tab deliminated

# other opertaions on file contents here

use strict;

my $file = 'file.txt';
open FILE, $file or die "Can not open $file $!\n";

while (<FILE>) {
  my $foo = substr($_,15,9);
  print "F $foo\n";
To move your data from text files to a database, you'll want to acquire the data from each of the files.  To do this, you should use the glob() function to get a list of files in the directory.

Next, you'll want to select a database system.  MySQL is a commonly used database, free for non-commercial use, and an excellent choice for a wide variety of needs.  To interface this with Perl, you can use the DBI module.  Documentation is available here: http://search.cpan.org/~timb/DBI-1.43/DBI.pm

Like Tintin said above, you can use substr() to perform the same operation as the UNIX cut command.  To add the data to a database, use code similar to the following:

use strict;
use DBI; #use the DBI module
my $dsn = "DBI:mysql:database=db_directconnect;host=localhost;port=$portnumber";
my $dbh = DBI->connect($dsn, 'username', 'passwd'); #connect to the database

my $file = 'file.txt';
open (FILE, $file) or die "Can not open $file $!\n";
while (<FILE>) {
  my $foo = substr($_,15,9); #get the data from the file
  $dbh->do("insert into tbl_tablename (column_name) values ('$foo')"); #execute a SQL command
$dbh->disconnect(); #disconnect from the database

Hope this helps.
One last note, just to clarify what the others have written:

To write out the delimited database file, first select your delimiter. This should be a character that is not every going to appear in your data. A lot of people like the triple-pipe delimiter "|||" though personally I think that's overkill. I tend to use a single pipe "|" as my delimited.

So, let's say your fields are name, phone, email.

# To APPEND one line to your database (adding a new record)
open MYDB, "+>my_database_file.txt" || die ("Couldn't write!");
print <MYDB> join ('|', ($name, $phone, $email)) . "\n"; # make sure the line is terminated!
close MYDB;

Now, depending on the size of your database, because you're not working with a relational DB like MySQL, you typically have to read in the whole database into a variable in Perl, and then you can work with it (you can sort it, print it out, etc...)

Probably the cleanest way to do this is to read the whole darned thing into an array of hashes. If you haven't studied "references" in Perl, I suggest you do so, but it really is easy. Watch:

my @database_array;
open MYDB, "my_database_file.txt" || die ("Couldn't read!");
while (<MYDB>) {
  # let's do this step-by-step to make it easier to follow
  my %record_hash; # define the hash that will contain your record
  # now, take the pipe-delimited record and split it
  # put each field into the appropriate key of your hash
  ($record_hash{'name'}, $record_hash{'phone'}, $record_hash{'email'}) = split '|', $_;
  # and store a refernce to the hash into your database array
  push @database_array, \%record_hash;
close MYDB;

# --- done

OK, cool. Now we've got the whole database read into memory. So how do you get to the data? Easy!

To access a record, say, the first record, we get the record out of the array and dereference it like this:

my %record_one = %{$database_array[0]};
my $name = $record_one{'name'};

# but that's a little cumbersome, so just get used to working with a reference directly:

my $name = $database_array[0]->{'name'};

# in fact, why assign it to a variable at all?

print "Name: $database_array[0]->{'name'};

# so to print ALL the names in the database:
foreach my $row (@database_array) {
  print "Name: $row->{'name'}<br />";

I hope this helps get you started on the road to DBs. I started with delimited flatfiles too. Then years later I discovered the miracle that is MySQL and haven't looked back since.

Tom Auger
CleavisAuthor Commented:
Thanks everybody.

Tom, that is an excellent answer.

I really appreciate the help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now