Solved

MySQL  Load data infile problem

Posted on 2012-03-30
15
1,192 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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Select Query and Populate a Table 3 51
Mysql sync between 3-4 mysql db 4 42
MS Access - need to reduce row size 25 60
Combining Queries 7 27
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 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