my csv import is not formatting the dates

Question

I cannot seem to get the csv file to format the dates to UTC time on import to mysql. Here is my query and if i take out the sql variables and the set statement it imports the information fine but i need the dates to be formatted in utc time.

$query = "
        LOAD DATA LOCAL INFILE '".$file."' INTO TABLE instructions
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '\r\n'
        (route_name,stop_sequence_number,stop_location_id,stop_action,@stop_arrival_time,@stop_departure_time,time_zone);
        SET stop_arrival_time = STR_TO_DATE(@stop_arrival_time, '%m/%e/%Y %r');
        SET stop_departure_time = STR_TO_DATE(@stop_departure_time, '%m/%e/%Y %r');
        ";

Am I not using the STR_TO_DATE format correctly? i looked at the information on the format and it said that %m for numbered month, %e for single digit day, %y for four length year and the %r for AM - PM time.

It wont import it at all i get this error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET stop_arrival_time = STR_TO_DATE(@stop_arrival_time, '%m/%e/%Y'); SET stop' at line 5. When i get rid of the formating part it imports everything but the dates and im not sure why. it just inserts all 0's — Alex Beyer, Aug 10 '15 at 00:42

score 1 · Answer 1 · edited May 23 '17 at 12:14

You do not have to convert the string to a date in order to import the date. As you have noticed it will except the statement after you have removed the STR_TO_DATE part of the import statement. However, you will instead get a bunch of columns looking like 0000-00-00 because mysql does not understand dates in mm/dd/yyyy format, etc... Mysql only excepts dates in YYYY-MM-DD format.

You can change this in Excel before doing your import.

In order to change the date format in excel: right click on the top cell. Choose format cells from the drop down list. change the local to something like 'Afrikans'. Choose the format that looks like 2001-03-14. Use the top cell to fill down. Then save the document.

Just a quick note: Excel sometimes tries to do too much and will revert this column back to a the English(U.S) default time zone. So, if you plan on doing more editing make sure that the column has not reverted back.

Here is a link to more string literals on dev.mysql.

This stored procedure and post might help you as well: Error code 1292

Edit:

Alex, your code has 3 semi-colons in it. Have you tried your original code as shown by you without the first two but keeping the last? As such it would be consistent with this Answer.

The only problem with that is that it is a text file not on excel. I cannot change the column itself in the file its an upload to set delivery times there are hundred of rows. I understand why its not uploading into the tables now. but how can i get it to format the columns before the insert? — Alex Beyer, Aug 10 '15 at 00:52
if the text file is formatted like a csv file type you can open it in excel...if you do not want to go that route then import as a VARCHAR into a raw table and use the stored procedure as an example to write one that will conform to what you need or even php if you prefer... — BK435, Aug 10 '15 at 00:57

my csv import is not formatting the dates

1 Answers1

Edit: