Solved

Delimited Text Database Help

Posted on 2004-09-08
5
668 Views
Last Modified: 2013-12-25
Hi,

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.

Thanks

Cleavis
0
Comment
Question by:Cleavis
5 Comments
 
LVL 4

Expert Comment

by:Neil_Simpson
ID: 12010725
once you have opened a file try

while(<FILE>){
#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

}
0
 
LVL 48

Expert Comment

by:Tintin
ID: 12010860
#!/usr/bin/perl
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";
}
0
 
LVL 3

Expert Comment

by:rkosai
ID: 12013996
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:

#!/usr/bin/perl
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.
0
 
LVL 14

Accepted Solution

by:
tomaugerdotcom earned 500 total points
ID: 12048200
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
0
 

Author Comment

by:Cleavis
ID: 12048236
Thanks everybody.

Tom, that is an excellent answer.

I really appreciate the help.

Cleavis
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now