On 9/16/07, Ruprecht Helms <[EMAIL PROTECTED]> wrote:
> Hi,
>
> how can I parse a csv-file where the entries are seperated with | .
> The scripts later should put them into a mysql-database using dbi.
>
> Especially for me is interessting how to parse the content of the file
> and store them into different variables for later processing.
> A scriptexample would be nice.
snip
Okay, lets assume we have a four column file that needs to be put into
a DB. The four columns are id, name, create_date, and message.
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect(
'dbi:SQLite:dbname=test.db',
'',
'',
{
ChopBlanks => 1,
AutoCommit => 1,
RaiseError => 1,
PrintError => 0,
FetchHashKeyName => 'NAME_lc'
}
);
if ($dbh->tables('%', '%', 'testtable', "TABLE")) {
print "deleting testtable\n";
my $sth = $dbh->do("delete from testtable");
} else {
print "creating testtable\n";
my $sth = $dbh->do("
CREATE TABLE testtable (
id INT,
name CHAR(50),
create_date DATE,
message CHAR(255)
)
");
}
my $insert = $dbh->prepare(
"INSERT INTO testtable (id, name, create_date, message)
VALUES (?, ?, ?, ?)"
);
while (<DATA>) {
chomp;
$insert->execute(split /\|/);
}
my $sth = $dbh->prepare("select * from testtable where name = ?");
$sth->execute("bar");
while (my $row = $sth->fetchrow_hashref) {
print "$row->{name}: $row->{message}\n";
}
__DATA__
1|foo|2007-01-01|foo is here
2|bar|2007-01-01|bar is here
3|baz|2007-01-01|baz is here
4|bar|2007-01-02|bar has left
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
