splitting cvs file and insert in mysql via DBI

[Xaver Biton]

Hi,

I'm running activestate 5.8 on winmaschine and I'm not very familiar
with perl but I like it very much.

Please be patient.

I've to split a csv file and insert it in a mysql db. I've done some
tests with other file and it worked wonderfull now in the real work
doesn't work, I' ve many errors.

The first question ist: why does perl complain when it find a "@" a "."
or a number in csv file. Perl says that it find an a number or a dot
.... where opeator is espected. how can I get rid of this?

The second question which I can't anderstand it at all is, when I
execute the script and give the csv file as parameter I recieve this
error message:

Can't locate object method "Ilzasname" via package "Asan" (perhaps you
forgot to load "Asan"?) at customers.csv line 1.
*** Process "C:\Perl\bin\perl.exe" terminated. Return code=255

the csv file is like this:

100742;ASAN;Herr;Ilzasname Asan;Steingoetter-Greiff-StrDOT
;69168;Wiesloch;;;20020425;
10074;ASAN;Herr;Ilzasname Asan;Steingoetter-Greiff-StrDOT
;69168;Wiesloch;;;20020425;hansgkoenigetaolDOTcom;
100744;ASAN;Herr;Ilzasname Asan;Steingoetter-Greiff-StrDOT
;69168;Wiesloch;;;20020425;

hier is the perl script:

use DBI;
#use strict;

# Declare and initialize variables
my $host = 'localhost';
my $db = 'dbname';
my $db_user = 'username';
my $db_password = 'password';



$dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password",
{RaiseError => 0, PrintError => 0} );

$sth = $dbh->prepare("INSERT INTO test(vo_nr, interne_customer_id,
nikname, salutation, name, forename, address, zip, city, tel, telefax,
customer_since, mail) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");

while(<>){
chomp;
my ($interne_customer_id, $nikname, $salutation, $name, $address,
$zip, $city, $tel, $telefax, $customer_since, $mail) = split /;/;
($name,$forename) = split(/\s+/,$name);
my $vo_nr = '012345';
$sth->execute($vo_nr, $interne_customer_id, $nikname, $salutation,
$name, $forename, $address, $zip, $city, $tel, $telefax,
$customer_since, $mail);
}

Thks. for help.

Xaver

 

[Paul Lalli]

The first question ist: why does perl complain when it find a "@" a "."
or a number in csv file. Perl says that it find an a number or a dot
... where opeator is espected. how can I get rid of this?

The second question which I can't anderstand it at all is, when I
execute the script and give the csv file as parameter I recieve this
error message:

Can't locate object method "Ilzasname" via package "Asan" (perhaps you
forgot to load "Asan"?) at customers.csv line 1.
*** Process "C:\Perl\bin\perl.exe" terminated. Return code=255

How, *exactly*, are you calling this script? That error message would
seem to indicate you are telling perl to execute the CSV file as though it
were a perl script. You should be calling it similar to this:

perl myfile.pl customers.csv

Are you forgetting the name of the script in the above?

use DBI;
#use strict;

Don't comment this out. It's important. If you're getting errors while
using strict, the correct action to take is to correct the errors, not to
disable strict.

Additionally, you forgot
use warnings;
Asking thousands of people to do the work that the interpreter itself can
do by typing that one line is rather rude.

Once you've run the script again with those two lines enabled, if you
can't figure out the problem(s), post again...


Paul Lalli

 

[gnari]

[snip problem supposedly involving CSV and DBI]

try to simplify your problem until it goes away.
not many on this group will want to create a mysql table
to try your script. so take away the DBI stuff.

it is boring to have to cut and paste your data into a separate
file to run a test script on, so unless the problem is a file
reading problem, simplify that away.

of course in your case, it probably IS some sort of file reading
problem, because you do not show us how you execute the script.
my guess is the you are not even running your script, but doing
the equivalent of
perl customers.csv

gnari

 

[Tad McClellan]

#use strict;

You lose the benefit of that if you comment it out. Do you cut
the seatbelts out of your car too?

You should enable warnings as well:

use warnings;

$dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password",

^ ^ ^ ^
^ ^ ^ ^
Useless uses of quotes.

$dbh = DBI->connect("dbi:mysql:$db:$host", $db_user, $db_password,


Please see this Perl FAQ:

perldoc -q vars

What's wrong with always quoting "$vars"?

($name,$forename) = split(/\s+/,$name);

What will that do with names like these?

Thorton Billy Bob

De Larenta Oscar

Try it and see...

 

[Xaver Biton]

I've to split a csv file and insert it in a mysql db. I've done some
tests with other file and it worked wonderfull now in the real work
doesn't work, I' ve many errors.

The first question ist: why does perl complain when it find a "@" a "."
or a number in csv file. Perl says that it find an a number or a dot
... where opeator is espected. how can I get rid of this?

Hi,

thks. everyone for the help.

Ok I've enabled strict and warnings again but didn't chanche much. I've
a similar error:

## error message##

Can't call method "execute" on an undefined value at customers.pl line
26, <> line 1.

##end##

this is line 26: $sth->execute($vo_nr, $interne_customer_id, $nikname,
$salutation, $name,
$forename, $address, $zip, $city, $tel, $telefax, $customer_since, $mail);

I run the script like this on a cygwin bash:

$perl customers.pl kunden.csv

How I said before I've done some tests with a more simple table and
everythings work well.

It could be that some fields now in the csv file are empty? And I didn't
declare it into the execute statement? Or more easy to say, must be
exists a variable in the execute statement for every value (also the
empty ones) in the csv file? I mean if the csv file look like
value1;value2; ;value4; my I declare like this:
$sth->execute($value1,$value2,value4); omitting actually the value3?

The other problem is that I can't find anything also in perldoc how to
say to perl that ".", "@", and number in csv are charachters and not
operators.

What will that do with names like these?

Thorton Billy Bob

De Larenta Oscar

Try it and see...

You are right, butI haven't find a better solution yet, and I tought in
the worst case I will change the few names by hand. Of course would be
nice if you would suggest something, but I've the fear asking to much now.


thks.

Xaver

 

[gnari]

Xaver Biton wrote:

Ok I've enabled strict and warnings again but didn't chanche much. I've
a similar error:

## error message##

Can't call method "execute" on an undefined value at customers.pl line
26, <> line 1.

##end##

this is line 26: $sth->execute($vo_nr, $interne_customer_id, $nikname,
$salutation, $name,
$forename, $address, $zip, $city, $tel, $telefax, $customer_since, $mail);

this is not a 'similar' error at all.

this tells us that $sth is undefined at the moment of the call,
probably because the prepare() failed. you should test the results
of the DBI connect() and prepare() functions, especially when disabling
DBI's RaiseError.

I run the script like this on a cygwin bash:

$perl customers.pl kunden.csv

we asked about that because the error you previously reported:
Can't locate object method "Ilzasname" via package "Asan"
(perhaps you forgot to load "Asan"?) at customers.csv line 1.
suggested you had done:
perl customers.csv


gnari

 

[Xaver Biton]

this is not a 'similar' error at all.

this tells us that $sth is undefined at the moment of the call,
probably because the prepare() failed. you should test the results
of the DBI connect() and prepare() functions, especially when disabling
DBI's RaiseError.

Hi,

this script make me crazy, I've checked everything, and everything is
ok. But was is surprising is that if I use a csv file with only 3 fields
it work! As soon I add onother field it doesn't work anymore.

Please help

Xaver

 

[Xaver Biton]

HI,

oK, finally I know where's the problem.
If I use strict then I ricieve the folowing error:

Can't call method "execute" on an undefined value at cinque.pl line
22, <> line 1.

if I disable use strict everything work ok, hwy this?

hier an example:

use DBI;
use strict;

# Declare and initialize variables
my $host = '192.168.0.1';
my $db = 'proeweb';
my $db_user = 'web884';
my $db_password = 'xaverio';

# Connect to the requested server

my $dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password",
{RaiseError => 0, PrintError => 0} );

my $sth = $dbh->prepare("INSERT INTO
test(erste,zweite,dritte,cinque,sei,sette) VALUES(?,?,?,?,?,?)");

while(<>){
chomp;
my ($erste,$zweite,$dritte,$cinque,$sei,$sette) = split /;/;
my $sth->execute($erste,$zweite,$dritte,$cinque,$sei,$sette);
}

 

[Ben Morrow]

HI,

oK, finally I know where's the problem.
If I use strict then I ricieve the folowing error:

Can't call method "execute" on an undefined value at cinque.pl line
22, <> line 1.

if I disable use strict everything work ok, hwy this?

hier an example:

use DBI;
use strict;

use warnings;

# Declare and initialize variables
my $host = '192.168.0.1';
my $db = 'proeweb';
my $db_user = 'web884';
my $db_password = 'xaverio';

# Connect to the requested server

my $dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password",
{RaiseError => 0, PrintError => 0} );

my $dbh = ... or die "connect failed: $DBI::errstr";

Or set RaiseError to 1.

my $sth = $dbh->prepare("INSERT INTO
test(erste,zweite,dritte,cinque,sei,sette) VALUES(?,?,?,?,?,?)");

my $sth = ... or die "prepare failed: $DBI::errstr";

while(<>){
chomp;
my ($erste,$zweite,$dritte,$cinque,$sei,$sette) = split /;/;

You really want to use an array here:

my @values = split /;/;

my $sth->execute($erste,$zweite,$dritte,$cinque,$sei,$sette);

What's that 'my' doing?

$sth->execute(@values) or die "execute failed: $DBI::errstr";

}

Ben

 

[gnari]

this script make me crazy, I've checked everything, and everything is
ok. But was is surprising is that if I use a csv file with only 3 fields
it work! As soon I add onother field it doesn't work anymore.

fine. take that, and chuck out the csv file reading part.
replace the whole while() loop with a single
$sth->execute('012346','foo','bar','hello');

does it work?
if yes, then it is not a DBI problem. try again with your
real data instead of foo and bar etc
if no, then take a look again at your prepare()

if you still cannot solve it, show us again a minimal script
with strictures and warnings, that exhibits your problem.
while you are at it, show us a working 3 field version of that
minimal script.

gnari

 

[A. Sinan Unur]

HI,

oK, finally I know where's the problem.
If I use strict then I ricieve the folowing error:

Can't call method "execute" on an undefined value at cinque.pl line
22, <> line 1.

if I disable use strict everything work ok, hwy this?

You have a serious problem: You blindly ignore possible error conditions
even when they are screaming at you. Fix your attitude please.

Very obviously, $sth is undefined. The fact that you choose not to be
informed of this does not make it go away.

hier an example:

use DBI;
use strict;

use warnings;

# Declare and initialize variables
my $host = '192.168.0.1';
my $db = 'proeweb';
my $db_user = 'web884';
my $db_password = 'xaverio';

Fix your indentation. This is giving the impression that there is a new
scope where there is none.

my $dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user",
"$db_password", {RaiseError => 0, PrintError => 0} );

No point in all those extra quotation marks. But the thing that baffles me
is you again _CHOOSE_ not to be informed of errors. How can we guess what
went wrong with your database connection when you won't check it yourself?

As a first step, change the code above to:

my $dbh = DBI->connect(
"dbi:mysql:$db:$host", $db_user, $db_password,
{RaiseError => 1, PrintError => 1}
);

my $sth = $dbh->prepare("INSERT INTO
test(erste,zweite,dritte,cinque,sei,sette) VALUES(?,?,?,?,?,?)");

Again, no error checking ...

while(<>){
chomp;
my ($erste,$zweite,$dritte,$cinque,$sei,$sette) = split /;/;
my $sth->execute($erste,$zweite,$dritte,$cinque,$sei,$sette);
}

If you had been checking for errors, you would have been told why the
prepare call above did not succeed (and hence why $sth is undefined). You
choose not to. How can we know what went wrong?

 

[gnari]

HI,

oK, finally I know where's the problem.
If I use strict then I ricieve the folowing error:

Can't call method "execute" on an undefined value at cinque.pl line
22, <> line 1.

if I disable use strict everything work ok, hwy this?

what do you mean 'everything work ok' ?

my $sth->execute($erste,$zweite,$dritte,$cinque,$sei,$sette);

this 'my' will undefine $sth, whether you use strict or not

gnari

 

[Tad McClellan]

Quoth Xaver Biton <[email protected]>:


You really want to use an array here:

my @values = split /;/;

I wouldn't want that.

I don't want to have to remember "0 is erste, 1 is zweite ... 5 is sette"
every time I want to get a particular one from @values.

But if their scope was more than 8 or 10 lines, then I'd use a hash
instead of an array (or individual scalars) so I have to remember
less (I don't have a lot of brain to spare, so I have to conserve).


my %fields;
@fields{ qw/ erste zweite dritte cinque sei sette / } = split /;/;

 

[Xaver Biton]

HI everyone,

Now works ok. I really thanks everyone.

The examples how to handle DBI error were very clear, thks again.

Xaver

 

[Ben Morrow]

Quoth (e-mail address removed):

I wouldn't want that.

I don't want to have to remember "0 is erste, 1 is zweite ... 5 is sette"
every time I want to get a particular one from @values.

But if their scope was more than 8 or 10 lines, then I'd use a hash
instead of an array (or individual scalars) so I have to remember
less (I don't have a lot of brain to spare, so I have to conserve).

Given that 'erste', 'zweite', &c are presumably 'first', 'second', &c I
wouldn't imagine that would be too much of a problem for the OP .

(I have to say I'm having trouble understanding where 4 has gone, or
what 'cinq' is doing in with all that German, but hey...)

Ben

 

[gnari]

If you had been checking for errors, you would have been told why the
prepare call above did not succeed (and hence why $sth is undefined). You
choose not to. How can we know what went wrong?

actually, the main reason for the undefinedness here is the 'my'.
but checking the result of the prepare() is good, specially when
*debugging*

gnari

 

[A. Sinan Unur]

actually, the main reason for the undefinedness here is the 'my'.
but checking the result of the prepare() is good, specially when
*debugging*

I was so hung up on the OP blatantly disabling all sorts of error checking
that I missed the obvious.