Hi everyone
I'm importing data from a CSV file that contains dates in the format: 01-Jan-1970
I need to convert them to Y-m-d but strtotime doesn't recognise the date format.
$date = "30-Jun-1980";
echo (date("Y-m-d", strtotime($date)));
// produces 1970-01-01I can't find a simple solution for this but it might be because I'm not sure how to describe the existing date format!
Can anyone help, either with search terminology or an approach to do the conversion please?
Thank you
Try using [man]strptime[/man] that way you can specify the format the date is in. Then it should be able to process it no problem.
Works for me:
echo date('Y-m-d', strtotime('30-Jun-1980'));outputs
1980-06-30(See [man]datetime.formats.date[/man] for acceptable formats.)
If it's something to do with your PHP version being too old (the oldest I can check against is 5.2.4), it's pretty straightforward to [man]explode[/man] the parts of the input date and reassembled them in another order, maybe converting the month abbreviation to a number with an array as well.
Thank you, both.
I'm using PHP version 5.3.14.
I made the mistake of assuming the echo line I quoted did the same as my actual code, which is operating slightly differently. When I actually tried that code as per Weedpacket's test, it works fine.
Could the problem be with my parsing of the CSV file?
Here's what I'm actually doing (with apologies for not simply quoting this in the first place).
$uploadfile = '/path/to/file.csv';
$file_handle = fopen($uploadfile, "r");
while (!feof($file_handle) ) {
$row = fgetcsv($file_handle, 1024);
if ($row[0] != ''){ // only proceed if first column not empty
if ($row[3] != ''){ // if this column is empty, there will not be dates to parse in the next 2 columns
$from = date('Y-m-d', strtotime($row[4]));
$to = date('Y-m-d', strtotime($row[5]));
print " source: $row[4] - $row[5] result: $from - $to<br />";
}
} // done checking if first column is empty
} // done all rows
fclose($file_handle);This outputs:
source: 02-Jan-2012 - 13-Jul-2012 result: 1970-01-01 - 1970-01-01
source: 09-Jan-2012 - 03-Jun-2012 result: 1970-01-01 - 1970-01-01
source: 13-Apr-2012 - 31-May-2012 result: 1970-01-01 - 1970-01-01
source: 29-Feb-2012 - 24-Jun-2012 result: 1970-01-01 - 1970-01-01
source: 31-Oct-2011 - 22-Jun-2012 result: 1970-01-01 - 1970-01-01
I will of look into strptime too, never heard of that before, thank you Derokorian. But if I can get strtotime to work, that'll be simpler.
Thanks again
I also inspected the CSV file and found leading spaces ahead of the dates. However, trimming the field doesn't make any difference.
$from = trim($row[4]);
$to = trim($row[5]);
$from = date('Y-m-d', strtotime($from));
$to = date('Y-m-d', strtotime($to));Same output results as before.
Can you do a [man]var_dump/man on one (or both) of the dates just before you use the date/strtotime() functions and show us what the output is?
Thanks brad, the results look like this:
string(12) " 09-Jan-2012"
string(12) " 03-Jun-2012"
This is AFTER I've done the trim:
$from = trim($row[3]);
$to = trim($row[4]);
?>
<pre>
<?php
var_dump($from);
var_dump($to);
?>
</pre>
<?php
$from = date('Y-m-d', strtotime($from));
$to = date('Y-m-d', strtotime($to));Ignore the fact that the column numbers have changed slightly, the import file I've been testing has changed slightly so the dates are in a different location in the CSV, but everything else is the same.
phpSimon;11008147 wrote:Thanks brad, the results look like this:
string(12) " 09-Jan-2012"
string(12) " 03-Jun-2012"This is AFTER I've done the trim
That would suggest that this "leading space" is not actually a normal space (i.e. one that trim() would have removed).
So, let's see what that character actually is. Try doing something like:
printf('First char is: 0x%X', ord($from));and let us know what the output is.
Thank you for your assistance. Here's the current code:
$file_handle = fopen($uploadfile, "r");
while (!feof($file_handle) ) {
$row = fgetcsv($file_handle, 1024);
if ($row[0] != ''){ // only proceed if first column not empty
if ($row[2] != ''){ // if this column is empty, there will not be dates to parse in the next 2 columns
$from = str_replace(" ", "", $row[3]);
$to = str_replace(" ", "", $row[4]);
printf('First char of from is: 0x%X', ord($from));
print "<br />";
printf('First char of to is: 0x%X', ord($to));
print ("<br />");
$from = date('Y-m-d', strtotime($from));
$to = date('Y-m-d', strtotime($to));
print " source: $row[3] - $row[4]<br /> result: $from - $to<br /><br />";
}
} // done checking if first column is empty
} // done all rows
fclose($file_handle);The result is:
First char of from is: 0xA0
First char of to is: 0xA0
source: 02-Jan-2012 - 13-Jul-2012
result: 1970-01-01 - 1970-01-01
First char of from is: 0xA0
First char of to is: 0xA0
source: 09-Jan-2012 - 03-Jun-2012
result: 1970-01-01 - 1970-01-01
And there's the mystery solved; 0xA0 corresponds to a special type of space called a "nonbreaking space" (it's the same as the HTML entity " " when the latter is decoded).
So, you can either figure out why your data has this character in it, or you can simply strip it off with [man]trim/man (manually specifying it in the second parameter, e.g. "\xa0").
Perfect. This now works exactly as expected:
$file_handle = fopen($uploadfile, "r");
while (!feof($file_handle) ) {
$row = fgetcsv($file_handle, 1024);
if ($row[0] != ''){ // only proceed if first column not empty
if ($row[2] != ''){ // if this column is empty, there will not be dates to parse in the next 2 columns
$from = trim($row[3], "\xa0");
$to = trim($row[4], "\xa0");
$from = date('Y-m-d', strtotime($from));
$to = date('Y-m-d', strtotime($to));
print " source: $row[3] - $row[4]<br /> result: $from - $to<br /><br />";
}
} // done checking if first column is empty
} // done all rows
fclose($file_handle);How did the printf work? It looks as if it prints out:
First char is: 0x
... then replaces %X with the results of ord($from). Is that correct?
Thanks again, for all your time and expertise.
phpSimon;11008159 wrote:How did the printf work?
See the manual page for [man]sprintf/man. In the format string (the first parameter) for the *printf() functions, a percent sign (%) is used as a placeholder. All placeholders get replaced with the 2nd through nth parameters passed to the function call, with the data being inserted in a certain format based on what character(s) follow that percent sign.
Thank you, again. Excellent information.