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!
Bang-O-MaticAsked:
Who is Participating?
 
johanntagleCommented:
mysql> LOAD DATA local INFILE '/tmp/sample.txt' INTO TABLE import_photolinks FIELDS TERMINATED BY '\t'  lines terminated by '\r\n' ignore 1 lines (PhotoLink, MLSNumber, PhotoLabel, @DateLastModified, PhotoOrder, DisplayAsPortrait) set DateLastModified = str_to_date(@DateLastModified,'%c/%e/%Y %l:%i:%s %p');
Query OK, 15 rows affected (0.00 sec)
Records: 15  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from import_photolinks;
+--------------------------------------------------------------------------------------------+-----------+----------------+---------------------+------------+-------------------+
| PhotoLink                                                                                  | MLSNumber | PhotoLabel     | DateLastModified    | PhotoOrder | DisplayAsPortrait |
+--------------------------------------------------------------------------------------------+-----------+----------------+---------------------+------------+-------------------+
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6493707CAF6D88ACB8B0648170815E89566D8E     |     45731 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6493727FAF6D88ACB8B0648170815E89566D8E     |     45754 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6592727FAF6D88ACB8B0648170815E89566D8E     |     46654 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F66917684AF6D88ACB8B0648170815E89566D8E     |     47599 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E767BAF6D88ACB8B0648170815E89566D8E     |     48290 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E7680AF6D88ACB8B0648170815E89566D8E     |     48295 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E7682AF6D88ACB8B0648170815E89566D8E     |     48297 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F688F6D84AF6D88ACB8B0648170815E89566D8E     |     49309 | Main View      | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B889C9270B2B9908C6E835A8B586C8D66   |    117308 | Master Bedroom | 2012-03-30 16:24:37 | 8          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B889C9370B2B9908C6E835A8B586C8D66   |    117308 | View 2 Master  | 2012-03-30 16:24:37 | 9          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8A70B2B9908C6E835A8B586C8D66   |    117309 | Main View      | 2012-03-30 16:38:39 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B70B2B9908C6E835A8B586C8D66   |    117309 |                | 2012-03-30 16:38:29 | 1          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7276B399B879815C875A6E8C656C |    117309 |                | 2012-03-30 16:45:11 | 9          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7376B399B879815C875A6E8C656C |    117309 |                | 2012-03-30 16:45:11 | 10         | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7476B399B879815C875A6E8C656C |    117309 |                | 2012-03-30 16:45:12 | 11         | False             |
+--------------------------------------------------------------------------------------------+-----------+----------------+---------------------+------------+-------------------+
15 rows in set (0.00 sec)

Open in new window


I added an "ignore 1 lines", removed the "optionally enclosed by 't'", added @ to DateLastModified to "(PhotoLink, MLSNumber, PhotoLabel, @DateLastModified, PhotoOrder, DisplayAsPortrait)" and changed "%P" to "%p" in the date formatters.

If you have "optionally enclosed by '\t' it causes problems as mysql gets somewhat "confused" for the rows 12-15 where you don't have values for photo labels.:
mysql> LOAD DATA local INFILE '/tmp/sample.txt' INTO TABLE import_photolinks             FIELDS TERMINATED BY '\t'  optionally enclosed by '\t' lines terminated by '\r\n' ignore 1 lines         (PhotoLink, MLSNumber, PhotoLabel, @DateLastModified, PhotoOrder, DisplayAsPortrait) set DateLastModified = str_to_date(@DateLastModified,'%c/%e/%Y %l:%i:%s %p');
Query OK, 15 rows affected, 8 warnings (0.00 sec)
Records: 15  Deleted: 0  Skipped: 0  Warnings: 8

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1261 | Row 12 doesn't contain data for all columns |
| Warning | 1261 | Row 12 doesn't contain data for all columns |
| Warning | 1261 | Row 13 doesn't contain data for all columns |
| Warning | 1261 | Row 13 doesn't contain data for all columns |
| Warning | 1261 | Row 14 doesn't contain data for all columns |
| Warning | 1261 | Row 14 doesn't contain data for all columns |
| Warning | 1261 | Row 15 doesn't contain data for all columns |
| Warning | 1261 | Row 15 doesn't contain data for all columns |
+---------+------+---------------------------------------------+

mysql> select * from import_photolinks;
+--------------------------------------------------------------------------------------------+-----------+-------------------------------+---------------------+------------+-------------------+
| PhotoLink                                                                                  | MLSNumber | PhotoLabel                    | DateLastModified    | PhotoOrder | DisplayAsPortrait |
+--------------------------------------------------------------------------------------------+-----------+-------------------------------+---------------------+------------+-------------------+
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6493707CAF6D88ACB8B0648170815E89566D8E     |     45731 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6493727FAF6D88ACB8B0648170815E89566D8E     |     45754 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F6592727FAF6D88ACB8B0648170815E89566D8E     |     46654 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F66917684AF6D88ACB8B0648170815E89566D8E     |     47599 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E767BAF6D88ACB8B0648170815E89566D8E     |     48290 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E7680AF6D88ACB8B0648170815E89566D8E     |     48295 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F678E7682AF6D88ACB8B0648170815E89566D8E     |     48297 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988F688F6D84AF6D88ACB8B0648170815E89566D8E     |     49309 | Main View                     | 2010-12-02 08:38:17 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B889C9270B2B9908C6E835A8B586C8D66   |    117308 | Master Bedroom                | 2012-03-30 16:24:37 | 8          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B889C9370B2B9908C6E835A8B586C8D66   |    117308 | View 2 Master                 | 2012-03-30 16:24:37 | 9          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8A70B2B9908C6E835A8B586C8D66   |    117309 | Main View                     | 2012-03-30 16:38:39 | 0          | False             |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B70B2B9908C6E835A8B586C8D66   |    117309 | 3/30/2012 4:38:29 PM	1	False  | NULL                | NULL       | NULL              |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7276B399B879815C875A6E8C656C |    117309 | 3/30/2012 4:45:11 PM	9	False  | NULL                | NULL       | NULL              |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7376B399B879815C875A6E8C656C |    117309 | 3/30/2012 4:45:11 PM	10	False | NULL                | NULL       | NULL              |
| http://tp.usamls.net/getMLPhoto.asp?l=8D565A988C6093707B899C8B7476B399B879815C875A6E8C656C |    117309 | 3/30/2012 4:45:12 PM	11	False | NULL                | NULL       | NULL              |
+--------------------------------------------------------------------------------------------+-----------+-------------------------------+---------------------+------------+-------------------+
15 rows in set (0.00 sec)

Open in new window


Bottomline, you shouldn't use the same character to enclose and terminate fields.
0
 
johanntagleCommented:
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
0
 
Bang-O-MaticAuthor Commented:
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');
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johanntagleCommented:
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.
0
 
theGhost_k8Database ConsultantCommented:
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');
0
 
johanntagleCommented:
Ah yes I forgot the handler for AM/PM.
0
 
Bang-O-MaticAuthor Commented:
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!
0
 
Bang-O-MaticAuthor Commented:
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.
0
 
johanntagleCommented:
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.
0
 
theGhost_k8Database ConsultantCommented:
I'd be easy to answer if you can provide more info. meanwhile here are some examples to easeup the Load Data task.
0
 
Bang-O-MaticAuthor Commented:
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
0
 
Bang-O-MaticAuthor Commented:
could you post your code?

Thanks
0
 
Bang-O-MaticAuthor Commented:
Adding the @DateLastModified did the trick! Thank you very much for all your help.
0
 
Bang-O-MaticAuthor Commented:
Thanks again!
0
 
johanntagleCommented:
You're welcome.  Btw if you haven't noticed it yet my modified version of your LOAD DATA command is on the first line of my post above (http://www.experts-exchange.com/Database/MySQL/Q_27655955.html#a37796250). Just scroll to the right.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.