MySQL Forums
Forum List  »  Perl

Make data load infile more tolerant. Works in SQLyog
Posted by: Paul Beerkens
Date: April 03, 2007 07:31AM

I have a file that is not entirely corrected formatted. The first line is empty and the second line contains header names.

I know that this is easely solved by IGNORE 2 LINES but I am also having issues witht a double field not being correctly formatted even though I can see nothing wrong with it.

Now the thing is that SQLyog does load this file fine. It copes with all the bad data and populates all the good fields ok. It even handles the price field with the badly formated doubles fine and I end up with a correctly populated table.

When I copy paste the load data infile statement from SQLyog to either my Perl application or a mysql session I get errors like the ones below.

mysql> LOAD DATA INFILE 'c:\\pnl\\trades\\trades.out' INTO TABLE temp_trade IGNORE 2 LINES;
ERROR 1265 (01000): Data truncated for column 'price' at row 1
mysql>

Is there any way I can make my Perl session behave as tolerant as the SQLyog session when doing the load data infile?

This works in SQLyog:
DROP TABLE IF EXISTS temp_trade;
CREATE TABLE temp_trade
(
date date,
action varchar (20),
qty int,
symbol varchar (20),
description varchar (100),
price double,
amount varchar (20),
fees double
);
LOAD DATA INFILE 'c:\\pnl\\trades\\trades.out' INTO TABLE temp_trade;


This does not work in perl:
# make connection to database
my $dbh = DBI->connect($connectionInfo,$userid,$passwd);

#create the temp table
$dbh->do ("DROP TABLE IF EXISTS temp_trade");
$dbh->do ("CREATE TABLE temp_trade
(
date date,
action varchar (20),
qty int,
symbol varchar (20),
description varchar (100),
price double,
amount varchar (20),
fees double
)");

#bcp the data in
my $sql="LOAD DATA INFILE \'c:\\\\pnl\\\\trades\\\\trades.out\' INTO TABLE temp_trade IGNORE 2 LINES";

Options: ReplyQuote


Subject
Written By
Posted
Make data load infile more tolerant. Works in SQLyog
April 03, 2007 07:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.