PHP Date column does not populate from csv file

Question

I'm trying to import a csv file into a table. When I run the query the date_altered column does not populate properly.

Insert query:

xtc_db_query("
        LOAD DATA INFILE '".$bpmand."'
        INTO TABLE bpmand_update
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\r\n'
        (@skip, customers_id, @skip, @skip, date_altered, @skip)
        ");

Other info -

Table creation:

$createtable = '    
        CREATE TABLE bpmand_update
        (
        orders_id int(11),
        customers_id int(11),
        bonuspoints_received int(11),
        bonuspoints_spent int(11),
        current_bonuspoints int(11),
        date_altered datetime,
        comment varchar(50));';
    xtc_db_query($createtable);

The csv file looks like:

"1260","13-11-2013 11:45:45"
"5222","09-01-2014 11:45:45"

Edit: If I got to phpmyadmin and run

INSERT INTO bpmand_update (customers_id, date_altered) VALUES ("1260","13-11-2013 11:45:45")

Everything is fine

Ambiguous date format: is 09-01-2014 the 1st of September, or the 9th of January? 2014-01-09 format eliminates ambiguity, it's the 9th of January — Mark Baker
– Mark Baker, Commented Apr 4, 2014 at 11:03
@user986959 The date format clearly isn't fine, otherwise you wouldn't have this problem — Mark Baker
– Mark Baker, Commented Apr 4, 2014 at 11:05

Community · Accepted Answer · 2017-05-23 12:11:11Z

2

SQL needs the date to be in the format YYYY-MM-DD HH:MM:SS, you have it as DD-MM-YYYY HH:MM:SS. So you need to modify your SQL statement anlong the lines of this StackOverflow answer as follows:

    LOAD DATA INFILE '".$bpmand."'
    INTO TABLE bpmand_update
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\r\n'
    (@skip, customers_id, @skip, @skip, @date, @skip)
    SET date_altered = STR_TO_DATE(@date,'%d-%m-%Y %H-%i-%s')

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Apr 4, 2014 at 11:06

akirk

6,8972 gold badges37 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Baker Over a year ago

+1 for actually providing a simple-to-implement solution rather than simply identifying the problem

Krish R · Accepted Answer · 2014-04-04 11:11:55Z

1

In your CSV file datetime value should be in the format of YYYY-MM-DD HH:MM:SS instead of 13-11-2013 11:45:45. So you need convert the date value as MySQL accepted format

DATETIME values in YYYY-MM-DD HH:MM:SS format.

Ref: https://dev.mysql.com/doc/refman/5.0/en/datetime.html

edited Apr 4, 2014 at 11:11

answered Apr 4, 2014 at 11:05

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

Collectives™ on Stack Overflow

PHP Date column does not populate from csv file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related