STR_TO_DATE and LOAD DATA INFILE formatting issue

Question

Well, thanks everybody for their help. Looks like it was an un-escaped character in the date formatting: '%%d/%%m/%%Y %%H:%%i' is what I needed. Since this is being run from a batch file, I didn't take that into account.

I've got a batch file that runs an SQL command and I thought I understood what was required, but can't seem to make it all come together. When I run the code below, I get everything to import properly, but either my STR_TO_DATE formatting must be off or the way I'm targeting the second column must be off, as I just get "(NULL)" or all zeros in my DATETIME column when I run the script below.

Here's a screenshot of what I'm seeing from the command prompt window. The suspicious part is highlighted in green: https://i.stack.imgur.com/3mlsi.png

Any ideas?

CREATE TABLE `serials` (
    `Serial` INT(10) UNSIGNED NULL DEFAULT '0',
    `Stamp` DATETIME NULL DEFAULT NULL,
    `Data1` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data2` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data3` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data4` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data5` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data6` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data7` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    `Data8` MEDIUMINT(8) UNSIGNED NULL DEFAULT '0',
    INDEX `StampINX` (`Stamp`),
    INDEX `SerialINX` (`Serial`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM;



echo on
setlocal enabledelayedexpansion
FOR %%f IN ("*.csv") DO (
set old=%%~dpnxf
set new=!old:\=\\!
mysql -e "LOAD DATA local INFILE '"!new!"' IGNORE into table test.serials  COLUMNS TERMINATED BY ','       
(Serial,@Stamp,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8) SET   
Stamp=STR_TO_DATE(@Stamp,'%d/%m/%Y %H:%i')" -u root -ppassword
  echo %%~nxf DONE
)

and below is a sample of the file I'm accessing with the LOAD DATA INFILE function

Serial,Stamp,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8
50183,01/01/2012 00:15,0,77,73,84,0,0,3,62
50183,01/01/2012 00:30,0,100,45,77,0,0,3,67
50183,01/01/2012 00:45,0,96,62,73,0,0,2,61    
50183,01/01/2012 01:00,0,81,79,85,0,0,3,56

score 0 · Answer 1 · answered Jun 19 '15 at 16:03

0

Your string format for STR_TO_DATE should be:

%d/%m/%Y %H:%i

You were missing the slashes.

answered Jun 19 '15 at 16:03

Mike Brant

70,514
10
99
103

See that's what I thought at one point too, but I still get the same results. The documentation on this seemed pretty clear from what I can tell. Am I just targeting the second column improperly? – JubJub24 Jun 19 '15 at 16:17

Ike Walker · Answer 2 · 2015-06-19T16:40:52.033

As Mike Brant said, you are missing the slashes.

Also, 4-digit year is %Y, not %y.

Sometimes with this type of project it is useful to load the data into a string column in MySQL first in a test table, then you can test the functions out to make sure they are transforming the values as desired.

You can also easily test a single value to make sure the date format is correct:

mysql > \W
Show warnings enabled.
mysql > select str_to_date('01/01/2012 01:00','%d%m%y %H:%i');
+------------------------------------------------+
| str_to_date('01/01/2012 01:00','%d%m%y %H:%i') |
+------------------------------------------------+
| NULL                                           |
+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '01/01/2012 01:00' for function str_to_date
mysql > select str_to_date('01/01/2012 01:00','%d/%m/%y %H:%i');
+--------------------------------------------------+
| str_to_date('01/01/2012 01:00','%d/%m/%y %H:%i') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '01/01/2012 01:00' for function str_to_date
mysql > select str_to_date('01/01/2012 01:00','%d/%m/%Y %H:%i');
+--------------------------------------------------+
| str_to_date('01/01/2012 01:00','%d/%m/%Y %H:%i') |
+--------------------------------------------------+
| 2012-01-01 01:00:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

This should work for you:

SET Stamp=STR_TO_DATE(@Stamp,'%d/%m/%Y %H:%i')

Added the slashes again too, and even swapped the %y for %Y but am still in the same boat. I will update my original question to include the create table code. As well as try your other suggestion. Appreciate the help. — JubJub24, Jun 19 '15 at 16:43
I've posted a screenshot of what the command prompt is showing when I run the script. I had to blur some things, but I've also highlighted in green the part that I find suspicious: http://i.imgur.com/TztR31p.png?1 — JubJub24, Jun 19 '15 at 17:09

STR_TO_DATE and LOAD DATA INFILE formatting issue

2 Answers2