Solved

Delimited Text Database Help

Posted on 2004-09-08
5
662 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
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 if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn how to count occurrences of each item in an array.

707 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

12 Experts available now in Live!

Get 1:1 Help Now