load data infile - fails to load my db2 del (ascii) file

Mailing List

Home

Forum Home

MySQL General - General MySQL discussion

MySQL++ - Programming with the C++ API to MySQL

MaxDB - Everything about MaxDB, formerly known as SAP DB

ODBC - ODBC with the MySQL Connector/ODBC driver

MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP

Java Help - Mostly related to the MySQL Connector/J driver

Perl - Perl support for MySQL with DBI and DBD::mysql

GUI - MySQL GUI Tools

Announcement

Subjects

•	Subject: mysql openssl Question
•	ERROR 1045: Access denied for user: 'root@localhost ' (Using password: NO)
•	Update one field with more fields from another table
•	Subject: Getting Identity after INSERT
•	ERROR 2002: Can 't connect to local MySQL server through socket
•	mysql test 4 1 fails with the gis test
•	Subject: MySQL Cluster Software
•	Downgrade Mysql from 4 to 3 23
•	Mysql 4 0 Oracle Stored Procedure Trigger Conversion
•	Can 't access mysql after kernel upgrade
•	Executing MySQL Commands From Within C Program
•	Comparing and writing out BLOBS
•	Subject: Re: Preventing Duplicate Entries
•	FULLTEXT query format question
•	Strange behavior, Table Level Permission
•	Does the binary log enabling affect the MySQL performances?
•	mysql:it 's a db not a dbms how it 's possible?!
•	mysql have same function mthod as Oracle decode()

load data infile - fails to load my db2 del (ascii) file

load data infile - fails to load my db2 del (ascii) file

2007-11-06 - By lanes

Back

hi all,

right now i'm trying to migrate from db2 running under linux to mysql v5.1.

i manage to export out the db2 structure & data into a del (ascii) file.
but when i try to load the data from the del file to mysql table, it
generate an error.

below is the load data infile syntax i use =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

and below is the sample layout of del file (in the above case is
TABLE01.del) =
1,0,"2007-07-31 00:25:12"
2,0,"2007-07-31 14:09:00"
3,0,"2007-07-31 00:00:00"
4,0,"2007-07-31 00:00:00"
5,0,"2007-07-31 00:00:00"

and below is the TABLE01 structure =
FieldName, Type, Null, Primary
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
MY_TABLEID, int(11), NO, PRIMARY KEY
CNTS, int(11), NO, NOT PRIMARY KEY
INSERT_DATE, datetime, NO, PRIMARY KEY

the error i encounter is =
ERROR 1292 (22007): Incorrect datetime value: '"2007-07-31 00:25:12".....

i do some trick by trying to remove the qoute sign from "2007-07-31
00:25:12" so the del file become =
1,0,2007-07-31 00:25:12
2,0,2007-07-31 14:09:00
3,0,2007-07-31 00:00:00
4,0,2007-07-31 00:00:00
5,0,2007-07-31 00:00:00

And i do load data infile again with same command =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
and it was successful.

to me it is very strange, since i can do a sql insert using "2007-07-31
00:25:12" for example: insert into TABLE01 values (11, 22, "2007-07-31
00:25:12") successfully without any error!!!
in fact, when i use a MySQL Administrator tools, and i export out the data
from TABLE01 into CSV, then i found that the structure is exactly the same
with del file generated by db2.

do i make any mistake??

or do i need to initialize something before i call
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
...?????

Any help is appreciated.
Thank you.

Regards,
Lanes
--
View this message in context: http://www.nabble.com/load-data-infile---fails-to
-load-my-db2-del-%28ascii%29-file-tf4762331.html#a13620281
Sent from the MySQL - General mailing list archive at Nabble.com.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)