Mac OSX 10.3 - DBI:CSV - automatically updating the database handle's tables hash after opening a file

From: Thuan-Jin Kee (kee_at_wehi.EDU.AU)
Date: 11/29/04 

Date: Mon, 29 Nov 2004 15:31:25 +1100 (EST)
To: dbi-users@perl.org

Hi everyone,

I've got a problem updating the database handle's tables hash when going
to read an already existing table

I'm pretty new to dbi, and I'm working through the examples in the
perldocs for CSV.pm and the exercises at
http://www.perl.com/pub/a/1999/10/DBI.html

and
#http://ironbark.bendigo.latrobe.edu.au/subjects/int32we/

I've got this fragment of runnable code that works ok to create a file
called "customer"

#!/usr/bin/perl -Wall
#script for creating a table called customer as a csv file
use DBI;

my($dbh, $sth);
my($key);

$dbh = DBI->connect("DBI:CSV:f_dir=./")
            or die "Cannot connect: " . $DBI::errstr;

$sth = $dbh->prepare("CREATE TABLE customer
                         (custID INTEGER NOT NULL,
                          family CHAR(30) NOT NULL,
                          given CHAR(30))")
             or die "Cannot prepare: " . $dbh->errstr();

$sth->execute() or die "Cannot execute: " . $sth->errstr();

foreach $key (sort keys %{$dbh->{csv_tables}})
{
        print $key. "=> ". $dbh->{csv_tables}->{$key};
}
$dbh->disconnect() or die "Can't disconnect ". $dbh->errstr();

#end bit that works

and the file "customer" appears with its heading row and everything.

In the next script I try to write to it with an INSERT statement.

Unfortunately even after manually adding the customer table file name to
the $dbh->{csv_tables} hash I still get "bad table name" errors.

#!/usr/bin/perl -Wall
use DBI;

my($dbh, $sth);
my($key, $code);

$dbh = DBI->connect("DBI:CSV:f_dir=./")
            or die "Cannot connect: " . $DBI::errstr;

foreach $key (sort keys %{$dbh->{csv_tables}})
{
        print $key. "=> ". $dbh->{csv_tables}->{$key};
}

$dbh->{csv_tables}->{"customer"} = {file =>"./customer"};

$code = <<MARKER;
INSERT INTO customer
(custID, family, given)
VALUES
(1, " . $dbh->quote("adams") . ", " . $dbh->quote("mortica") . ");
MARKER

$dbh->do($code)
        or die "Cannot prepare: " . $dbh->errstr();

$dbh->disconnect() or die "Can't disconnect ". $dbh->errstr();

#end of script that doesn't work.

Please help! Without being able to reconnect to the csv file for a simple
insert, I'm at a loss as to how to connect to do SELECTs.

I can't figure out how to discover the tables, especially if there is more
than one table of formerly excel data to read in...

I'm sure there must be a well known and easy data conversion method I'm
missing, because ripping data out of excel generated csv files must be
being done all over the world almost every day, right?

Yours
Jin

Relevant Pages

  • Re: Array Functions from Alan Beban
    ... I have a csv file that is pulled from a database. ... I am trying to pull the customer number, ... invoice number, invoice date, and invoice amount from the CSV file to my ... >>Is there a way to pull a list of numbers from another worksheet. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Add Data To Pivot Table
    ... I run off the report as a .csv file, open it in Excel, then save ... I drag the customer name to the first block on the left, ... > the sales person to the top as a page separator, ... > Now, the resulting Pivot Table has a gray Sales Person at the top, ...
    (microsoft.public.excel)
  • Powershell help
    ... I have a customer who is ... I plan to read in the csv file, delete the ad contact, enable the users for ... exchange and even set the primary smtp address. ...
    (microsoft.public.exchange.admin)
  • Re: Array Functions from Alan Beban
    ... >Is there a way to pull a list of numbers from another worksheet. ... >need to do is pull all the customer numbers from a csv file and then ... >the remaining info from the csv file. ... example) and run Data> Filter> AutoFilter. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Using text files as database to interact with DBI
    ... (like csv file) ... Consider starting with SQLite. ... into the database. ... Or the DBI ad DBD::SQLite ...
    (perl.beginners)