php - LOAD DATA LOCAL INFILE date format from csv

338

I have been trying to pull a file into a table and the only issue I'm having is importing the dates (currently in'MM/DD/YY' format). No matter what I try they end up looking like'0008-12-14'. That date is8/12/14 in the .csv file. I tried

SET ShipDate = STR_TO_DATE(@ShipDate,'%Y%m%d')

but then it imported them asNULL. The table field is set asDATE. Here's the code:

$query = <<<eof
    LOAD DATA LOCAL INFILE 'file.csv'
     INTO TABLE HS_import
     FIELDS TERMINATED BY ','
     LINES TERMINATED BY '\n'
     IGNORE 1 LINES
    (AmountPaid,
ShipDate)
SET ShipDate = STR_TO_DATE(@ShipDate,'%Y%m%d')
eof;

776

Answer

Solution:

Yor format for the STR_TO_DATE is wrong. It should specify the INPUT format not the OUTPUT format:

SET ShipDate = STR_TO_DATE(@ShipDate,'%d%m%y')

So the whole thing should look like

  LOAD DATA LOCAL INFILE 'file.csv'
  INTO TABLE HS_import
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (AmountPaid, @ShipDate)
  SET ShipDate = STR_TO_DATE(@ShipDate,'%d%m%y')

People are also looking for solutions to the problem: Is it possible to prevent php session from destroying when you close browser?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question