asked on

MySQL Load data infile problem

I have a .txt file with a datetime field that i want to import into my MySQl 5.1 table using LOAD DATA INFILE. the datetime in the txt file is formatted like 12/2/2010 8:38:17 AM and when trying to import to the table it throws the error:
Data truncation: Incorrect datetime value: '12/2/2010 8:38:17 AM' for column 'DateLastModified' at row 1

I think that MySQL won't accept a datetime in that format??, it needs to be formatted to
2010/12/02 8:38:17 AM. So my question is how can I do this?

Hers my current code:
LOAD DATA INFILE '#replaceNoCase(ExpandPath( '/ftp/txt/photos_links.txt' ),"\","/","all")#' INTO TABLE nav.import_photolinks
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\t' ESCAPED BY '\\' lines terminated by '\r\n';

Any help appreciated!

johanntagle

sample on how to deal with differently-formatted dates:

load data infile '/tmp/xxx.dat'
into table xxx
fields terminated by '|'
lines terminated by '\n'
(col1,
col2,
@col3,
@col4,
col5)
set
col3 = str_to_date(@col3, '%m/%d/%Y'),
col4 = str_to_date(@col4, '%d/%m/%Y')
;

So you need to specify the columns, use @columnname for the date columns, then use set clause with str_to_date. I'll update this comment in a few minutes for the formatting option you should use.

UPDATE: for '12/2/2010 8:38:17 AM' you need '%m/%e/%Y %l:%i:%s' See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Dusty

ASKER

Hi, I updated my code but I'm still getting the same error.
Error Executing Database Query.
Data truncation: Truncated incorrect datetime value: '12/2/2010 8:38:17 AM'

LOAD DATA INFILE '#replaceNoCase(ExpandPath( '/ftp/txt/photos_links.txt' ),"\","/","all")#' INTO TABLE navica.photolinks
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\t' ESCAPED BY '\\' lines terminated by '\r\n'
(PhotoLink, MLSNumber, PhotoLabel,@DateLastModified, PhotoOrder, DisplayAsPortrait) set DateLastModified = str_to_date(@DateLastModified, '%d/%m/%Y %I:%i:%s');

johanntagle

is the date feb 12, not dec 2? Please note that there are different formatting options when you allow a single digit month/day/hour vs 01, 02, etc.. That's why I used lowercase L for hour, not capital I. for month it's %c (not %m as I did above) and for day it's %e.

theGhost_k8

The problem is with 12 / 24 HR format.
Check this:
select str_to_date("12/2/2010 8:38:17 PM", "%d/%m/%Y %I:%i:%s %p");

Change:: str_to_date(@DateLastModified, '%d/%m/%Y %I:%i:%s %p');

johanntagle

Ah yes I forgot the handler for AM/PM.

Dusty

ASKER

I had noticed we didn't have the AM/PM handler, and added it myself

str_to_date(@DateLastModified, "%c/%e/%Y %l:%i:%s %p")

still doesnt work.

heres another datetime out of my txt file: 3/29/2012 3:27:56 PM, so its m/d/yyyy

that should match up with my code above but it is still throwing the same error?

I have the field type for DateLastModified set to datetime, that is correct right?

thanks for the help it is much appreciated!

Dusty

ASKER

In doing some more testing I actually updated my text file so that the datetime on the first row was 2010/12/02 8:38:17 AM and it still failed with the same error. If I set the field type to varchar the data will import without errors.

johanntagle

Created a /tmp/test that contained this:

2/12/2010 8:38:17 AM
3/29/2012 3:27:56 PM

Open in new window

Then did the following:

mysql> create table bang (a datetime);
Query OK, 0 rows affected (0.37 sec)

mysql> load data local infile '/tmp/test' into table bang (@a) set a=str_to_date(@a, "%m/%d/%Y %l:%i:%s %p");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from bang;
+---------------------+
| a                   |
+---------------------+
| 2010-02-12 08:38:17 |
| 2012-03-29 15:27:56 |
+---------------------+
2 rows in set (0.00 sec)

Open in new window

One thing I found odd with your load data command is you have "FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\t'" - not sure if having the same character to terminate and enclose works. Anyway if you still need help I would like to ask for a dump of the table structure (or output of "show create table tablename") and a few lilnes of the input data.

theGhost_k8

I'd be easy to answer if you can provide more info. meanwhile here are some examples to easeup the Load Data task.

Dusty

ASKER

More info....I attached a sample of my txt file, sql dump file from my table, and here is my failing code:

LOAD DATA INFILE '#replaceNoCase(ExpandPath( '/ftp/txt/photos_links.txt' ),"\","/","all")#' INTO TABLE navica.import_photolinks
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\t' lines terminated by '\r\n'
(PhotoLink, MLSNumber, PhotoLabel, DateLastModified, PhotoOrder, DisplayAsPortrait) set DateLastModified = str_to_date(@DateLastModified,'%c/%e/%Y %l:%i:%s %P');

Thanks for the help!
import-photolinks.sql
sample.txt

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dusty

ASKER

could you post your code?

Thanks

Dusty

ASKER

Adding the @DateLastModified did the trick! Thank you very much for all your help.