Solved

MySQL  Load data infile problem

Posted on 2012-03-30
15
1,203 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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