LOAD DATA INFILE csv date format

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kellis
Forum Newbie
Posts: 4
Joined: Fri Apr 16, 2004 3:36 am

LOAD DATA INFILE csv date format

Post 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!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

is the date in your CSV ddmmyyyy or does it have dashed between like dd-mm-yyyy?

Mark
kellis
Forum Newbie
Posts: 4
Joined: Fri Apr 16, 2004 3:36 am

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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

?>
kellis
Forum Newbie
Posts: 4
Joined: Fri Apr 16, 2004 3:36 am

Post 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!
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Post 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...
kellis
Forum Newbie
Posts: 4
Joined: Fri Apr 16, 2004 3:36 am

Post by kellis »

Thanks litebearer, it works!
Post Reply