473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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 =
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  2.  

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

and below is the TABLE01 structure =
Expand|Select|Wrap|Line Numbers
  1. FieldName, Type, Null, Primary
  2. ------------------------------------------------------
  3. MY_TABLEID, int(11), NO, PRIMARY KEY
  4. CNTS, int(11), NO, NOT PRIMARY KEY
  5. INSERT_DATE, datetime, NO, PRIMARY KEY
  6.  

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 =
Expand|Select|Wrap|Line Numbers
  1. 1,0,2007-07-31 00:25:12
  2. 2,0,2007-07-31 14:09:00
  3. 3,0,2007-07-31 00:00:00
  4. 4,0,2007-07-31 00:00:00
  5. 5,0,2007-07-31 00:00:00
  6.  
And i do load data infile again with same command =
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  2.  
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
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????
  2.  
Any help is appreciated.
Thank you.


Regards,
Lanes
Nov 7 '07 #1
0 2129

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Bruce A. Julseth | last post by:
When I execute this SQL statement in my PHP code, I get an error "File '.\Address.txt' not found (Errcode: 2)" $File = addslashes(".\Address.txt"); $SQL = "Load Data InFile \"" . $File . "\"...
3
by: hall | last post by:
I have a problem with my design of a templatized class. I'm trying to figure out how to load and save the data inside it, but can't. My class looks like this ------------------------------------...
0
by: Donald Tyler | last post by:
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to...
1
by: Ray in HK | last post by:
What are the differences between LOAD DATA INFILE and LOAD DATA LOCAL INFILE ? I found some web hosting company do not allow using LOAD DATA INFILE but allow LOAD DATA LOCAL INFILE. The reason...
4
by: Bruce A. Julseth | last post by:
I have three users with access to my test MySQL databases, root plus two others. The following PHP code only works when the user is root. Otherwise it fails. $File = addslashes(getcwd() ....
3
by: nsh | last post by:
mailing.database.mysql, comp.lang.php subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING! version info: my isp is running my web page on a linux box with php ver. 4.4.1 according to...
4
by: marora | last post by:
I have created class definition which contains a charater pointer as one of it's data memeber. The objective is to read some data from a file, and assign it to a data member; Size of data is...
4
by: CPD | last post by:
From PHP, doing a DROP TABLE and a CREATE TABLE is successful, but doing a LOAD FILE keeps failing, with the error "Access denied for user 'db_user'@'localhost' (using password: YES)". The...
5
by: Justin | last post by:
Hi, im facing a problem here. First of all here is my program requirement. I got a .csv file with thousands of records inside, i need to import them into my mysql database. So i tried using load...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.