#!/usr/bin/perl 
use DBI;
my $dbh = DBI->connect;
my $sth = $dbh->prepare("INSERT INTO mytable ( name, street, city ) VA
+LUES ( ?, ?, ? )");
$sth->execute;
while(defined ( $_ = <DATA>) ) {
  chomp;
  # ??, name, street, ?, city, ida, id, ??
  my @d = split /\|/, $_;
  $sth->execute($d[1], $d[2], $d[4]);
}

__DATA__
|John Smith|123 Any Street||Any City|KS|23568|
|Mary Smith|123 Any Avenue||Any Town|KS|32546|
[download]

There are many ways to accomplish this task. If it's a one-time deal, you can do something quick-n-dirty:

    #!/usr/bin/perl -nl
    chomp;
    @record = split /\|/, $_;
    print "INSERT INTO table (foo, bar, baz)"
        , " VALUES ('$record[0]', '$record[1]', '$record[2]')";
[download]

Then run it with something like:

And you'd end up with a .sql file you could run against the database.

Now, if you need something more reliable and reusable, you'll probably want to wrap your own I/O code rather than letting perl -n take care of it for you (for more on this, you can see PerlMonks' Basic Input and Output tutorial). You'd probably want to insert the data directly into the database with DBI (make sure you use placeholders, as documented in the DBI docs). The basic idea about splitting the lines, as my quick-n-dirty snippet demonstrates, would be the same.

Update: Oh yeah, also forgot to mention. For anything other than a one-off throwaway script, you should probably use strict and warnings, unless there's a good reason not to.

You can just loop through the lines, like the code below. See split on this too.

use Data::Dumper;

while(<DATA>) {
  @array = split('\|', $_);
  # printing to Dumper, just to show what @array looks like now
  print Dumper @array;
  # insert DBI stuff here ...
}
__DATA__
|John Smith|123 Any Street||Any City|KS|23568|
|Mary Smith|123 Any Avenue||Any Town|KS|32546|
[download]

What would be the best way to grab this information? How do you parse a file that is more than one line? Should I place this information into an array?

What have you tried and what has failed?

First off, you have to open the file, read each line in, split it on the pipes and then depending on the database in question use DBI or something similar to access the database with the appropriate SQL statements to insert the data into the proper table.

As I asked in my first paragraph, what have you tried and what has failed? The Perl Monks are a helpful lot but we kinda insist on others at least attemtpting helping themselves first.

Do you have the "insert it into a database" part designed yet? What you want to do with the data generally determines what structure to store it in. It's easy enough to put it into either an array or separate variables.

If there is some form of encoding for allowing | to be in the data of one of the fields, or allowing fields to be optionally quoted in some way, you want to look at one of the CSV modules. Otherwise, use perl's split function. (Remember that split takes a regular expression as the first argument; since | is a special character in regular expressions, you'd need to use /\|/).

Also, if you are using MySQL read up on the LOAD DATA INFILE command. This will also remove perl as a middle man.


-silent11