Page 1 of 1

LOAD DATA INFILE csv date format

Posted: Fri Apr 16, 2004 3:36 am
by kellis
hi all

i have problem when i load a csv file with data for ddmmyyyy.
when its loaded to the database, it is read wrongly. as mysql date format is yyyymmdd.
this there any way to resolve this?

thanks in advance!

Posted: Fri Apr 16, 2004 4:32 am
by JayBird
is the date in your CSV ddmmyyyy or does it have dashed between like dd-mm-yyyy?

Mark

Posted: Tue Apr 20, 2004 4:30 am
by kellis
thanks for your reply. csv date is in ddmmyyyy format, no separator.
i understand that mysql read in date as yyyymmdd, how do i load it so that the date is read correctly?

thanks again

Posted: Tue Apr 20, 2004 6:19 am
by magicrobotmonkey
look up substr at php.net you can pull it apart and put it back together like:

Code: Select all

<?php

$bad_date = "ddmmyyyy"

$day = substr($bad_date, 0, 2);
$month = substr($bad_date, 2, 2);
$year = substr($bad_date, 4, 4);

$good_date = $year.$month.$day;
echo $good_date; //yyyymmdd

?>

Posted: Tue Apr 20, 2004 8:44 pm
by kellis
i need to load from data.csv to mysql using load data infile. how to use substr to just change the data format, keeping the rest of the data.

//sample of data.csv file
date,name,email,contact
29032004,joh,joh@as.com,234456
30042004,bob,bob@da.com,43243
30052004,amy,amy@da.com,88243
//

thanks in advance!

Posted: Tue Apr 20, 2004 11:06 pm
by litebearer
Try this...

Code: Select all

<?PHP

// put the entire csv file into an array
$file_name = "data.csv";

$all_lines = file($file_name); 

// count the number of lines in the file 
// and store it in a variable

$how_many_lines = count($all_lines);

// take each line and 
//  1. separate each line into its individual elements 
//      (aka fields aka pieces of information) using 
//      the EXPLODE () function.
//  2. grab the value(s) you are going to change
//      and place them in a variable(s). Remember,
//      the count for the elements starts at 0 (zero). 
//      Also be careful that your variables don't 
//      contain extraneous parts (like extra spaces, 
//      newlines and tabs). Use the TRIM() function to
//      clean up.
//  3. do your change. 


// create a holding variable for the new contents
$contents = "";

for ($i = 0; $i < $how_many_lines; $i++) &#123; 

// separate each line into elements
  $all_fields&#1111;$i] = explode(',', $all_lines&#1111;$i]); 

// clean up each element
  $date = trim($all_fields&#1111;$i] &#1111;0]);
  $first_name = trim($all_fields&#1111;$i] &#1111;1]);
  $email = trim($all_fields&#1111;$i] &#1111;2]);
  $contact = trim($all_fields&#1111;3]);

// get the parts of the date
  $w_year = substr($date,4,4);
  $w_month = substr($date,2,2);
  $w_day = substr($date,0,2);

// put the date back together in 
// the yyyymmdd format
  $date = $w_year . $w_month . $w_day;

// reassemble the line
  $line = $date . "," . $first_name . "," . $email . "," . $contact . "\n";

// add the line to the contents
  $contents = $contents . $line;
  &#125;

// open the csv file in the write mode
$fp = fopen($file_name, "w");

// write the contents to the file
fwrite($fp, $contents);

//close the file
fclose($fp);

?>
Lite...

Posted: Thu Apr 22, 2004 5:31 am
by kellis
Thanks litebearer, it works!