Solved

Delimited Text Database Help

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
copy-item script help 15 78
Configure Robocopy to excluding folders 6 61
Chocolatey under PowerShell is not working properly 3 68
BATCH to EXE Converter 2 32
If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
This tutorial will discuss the log-in process using WhizBase. In this article I assume you already know HTML. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you might look at some of my other articles abo…
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 fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

792 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