Hi,

I am importing data from a csv file into mysql.

Everything works fine except that the 'order_date' column in CSV is in the following format mm/dd/yy.

Upon import I would like for that format to be changed to yyyy-mm-dd

I am not sure how to modify the following code to be able to do so.

Any help would be appreciated.

<?
include "connect.php";

if(isset($_POST['submit']))

   {

 $filename=$_POST['filename'];

 $handle = fopen("$filename", "r");

 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

 {


$date = date("Y-m-d");


$import="INSERT into table
(order_id,
order_status,
order_date,
todays_date)
values
('$data[0]',
'$data[1]',
'$data[2]',
'$date')";
       mysql_query($import) or die(mysql_error());

 }

 fclose($handle);

 print "Import done";



   }

   else

   {



  print "<form action='importnew.php' method='post'>";

  print "Type file name to import:<br>";

  print "<input type='text' name='filename' size='20'><br>";

  print "<input type='submit' name='submit' value='submit'></form>";

   }
?>

    Use PHP to do it.

    $csvDate = strtotime($csvDate);
    $newCSVDate = date("Y-m-d",$csvDate);

    That will change it to that format.

      Personally, I would make two changes: First, to answer your question, do the conversion right in the SQL query using MySQL's STR_TO_DATE() function.

      Second, there's no need to use PHP to process the CSV file at all - MySQL can do that for you via the LOAD DATA INFILE query (and much more efficiently, too).

        8 days later

        Hi Brad,

        Can you elaborate with example how I go about implementing your suggestion?

        I would like to make a correction in my initial post though.

        The 'order_date' column in CSV is in the m/dd/yyyy and NOT mm/dd/yy as I mistakenly stated in my initial post.

        Upon insert I would like for that format to be changed from m/dd/yyyy to yyyy-mm-dd.

        Any help would be appreciated

          Did you look at the manual page for STR_TO_DATE() I linked to above? It's pretty self explanatory, including example SQL queries.

            Write a Reply...