Delimited Text Database Help

Posted on 2004-09-08
Last Modified: 2013-12-25

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.


Question by:Cleavis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12010725
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

LVL 48

Expert Comment

ID: 12010860
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";

Expert Comment

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:

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.
LVL 14

Accepted Solution

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

Author Comment

ID: 12048236
Thanks everybody.

Tom, that is an excellent answer.

I really appreciate the help.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

729 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