On Fri, 2 Feb 2001, TIBA, LIVIA wrote:
> ##########################
> # Read a CSV file with "," as the separator, as exported by
> # MS Excel.
> ##########################
You should also have 'use strict'.
> require DBI;
> my $dbh = DBI->connect("DBI:CSV:");
> $dbh->{'csv_tables'}->{'employees'} = {
> 'eol' => "\n",
> 'sep_char' => ",",
> 'quote_char' => undef,
> 'escape_char' => undef,
> 'file' => './employees',
> 'col_names' => ["lname", "fname", "uid", "pein", "email", "phone",
> "cellphone"]
> };
> $dbh->do("DROP TABLE employees");
> $sth = $dbh->prepare("CREATE TABLE employees (lname CHAR(10), fname
> CHAR(10),
> uid CHAR(10), pein CHAR(10), email CHAR(35), phone
> CHAR(15),
> cellphone CHAR(15))")
> or die "Cannot prepare: " . $dbh->errstr();
> $sth->execute() or die "Cannot execute: " . $sth->errstr();
> $sth->finish();
> $dbh->disconnect();
Why do you disconnect from the database here? Once you do that, you can't
do anymore work with it.
It's also not necessary to prepare a CREATE TABLE statement. Just do a
direct statement using the $dbh->do method.
> $sth = $dbh->prepare("INSERT INTO employees
> (lname,fname,uid,pein,email,phone,cellphone)
>
> VALUES (?,?,?,?,?,?,?)") || die $dbh->errstr;
> while(<CSV>) {
> chop;
> my ($lname, $fname, $uid, $pein, $email, $phone, $cellphone) = split
> /,/;
> $sth->execute( $lname, $fname, $uid, $pein, $email, $phone,
> $cellphone ) || die
Wait a minute... You are using CSV as a filehandle, but I see nowhere
that you have opened it. It almost looks as if you are trying to read
values from the CSV file and then insert those same values back to the
same file...???
What errors are you getting when you try to run this (make sure you have
'use strict' in your script -- it can tell you a lot)?
> If this table is create and populate will be recognize by Oracle, SQL
> as SQL table, or I have to use DBD::Oracle?
No, the table you have created is a CSV file, and can only be read as a
CSV file. You are not connecting to any database backend other than a
comma-separated datafile.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Don't talk to me about naval tradition. It's nothing but rum, sodomy and
the lash.
-- Winston Churchill
