Solved

MySQL  Load data infile problem

Posted on 2012-03-30
15
1,113 Views
Last Modified: 2012-08-22
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!
0
Comment
Question by:Bang-O-Matic
  • 7
  • 6
  • 2
15 Comments
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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
 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
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
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Ah yes I forgot the handler for AM/PM.
0
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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
 
LVL 21

Expert Comment

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

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
could you post your code?

Thanks
0
 

Author Comment

by:Bang-O-Matic
Comment Utility
Adding the @DateLastModified did the trick! Thank you very much for all your help.
0
 

Author Closing Comment

by:Bang-O-Matic
Comment Utility
Thanks again!
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now