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!
LOAD DATA INFILE csv date format
Moderator: General Moderators
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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
?>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!
//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
Try this...
Lite...
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++) {
// separate each line into elements
$all_fieldsї$i] = explode(',', $all_linesї$i]);
// clean up each element
$date = trim($all_fieldsї$i] ї0]);
$first_name = trim($all_fieldsї$i] ї1]);
$email = trim($all_fieldsї$i] ї2]);
$contact = trim($all_fieldsї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;
}
// 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);
?>