Solved

MySQL  Load data infile problem

Posted on 2012-03-30
15
1,220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37788007
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
ID: 37788565
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
ID: 37789477
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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 21

Expert Comment

by:theGhost_k8
ID: 37790646
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
ID: 37790802
Ah yes I forgot the handler for AM/PM.
0
 

Author Comment

by:Bang-O-Matic
ID: 37793177
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
ID: 37793316
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 37794279
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
ID: 37794742
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
ID: 37796046
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
ID: 37796250
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
ID: 37796344
could you post your code?

Thanks
0
 

Author Comment

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

Author Closing Comment

by:Bang-O-Matic
ID: 37796573
Thanks again!
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37799830
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Memory Keeps Increasing 4 97
MySQL Query Using Up Memory 6 61
How to fix Datetime in MySQL? 4 76
Import MySQL data into MS Access using VB.Net interface 5 69
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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