Update: When using CsvView to output CSV UTF-8 you need to tell Excel to open it in UTF-8 format by including the Byte Order Mark with 'bom' => true
$controller->viewBuilder()
->setClassName('CsvView.Csv')
->setOptions([
'serialize' => 'items',
'header' => $header,
'extract' => $extract,
'bom' => true
]);
I have a CakePHP 4 application which uses PHP 8.1 and ICU version 72.1
php -i | grep -i icu
ICU version => 72.1
ICU Data version => 72.1
ICU TZData version => 2022e
ICU Unicode version => 15.0
The dates output by this version of ICU have a nnbsp NARROW NO-BREAK SPACE between the time and the am/pm
When exporting a CSV file using CsvView and opening it in Excel the space between the time and the am/pm is displaying garbled as a number of characters: ` '
You can simply do a fined and replace of   and move on.
Or you can open as UTF-8 in Excel to display correctly
Click Data => From Text/CSV
The File Origin Should be UTF-8. Click the Load button
The date should now display correctly
To find what character it was
Open the CSV file in VSCode. Copy and paste the garbled character into a PHP script below and run it.
<?php
include '../vendor/autoload.php';
use Cake\I18n\FrozenTime;
$dt = "22/5/23, 6:09?am";
$dtString = FrozenTime::now()->i18nFormat(\IntlDateFormatter::SHORT, 'Australia/Melbourne');
var_dump($dtString);
// need to use mb_str_split NOT str_split
foreach (mb_str_split($dtString) as $letter) {
var_dump(['letter' => $letter, mb_ord($letter, 'UTF-8')]);
};
var_dump(mb_ord('?'));
var_dump(IntlChar::charName('?'));
var_dump('U+' . strtoupper(dechex(IntlChar::ord('?'))));
var_dump(dechex(IntlChar::ord('?')));
var_dump(
IntlChar::CHAR_CATEGORY_SPACE_SEPARATOR
===
IntlChar::charType(IntlChar::ord('?'))
);
Output
string(18) "30/7/23, 3:31?pm"
array(2) {
'letter' =>
string(1) "3"
[0] =>
int(51)
}
array(2) {
'letter' =>
string(1) "0"
[0] =>
int(48)
}
array(2) {
'letter' =>
string(1) "/"
[0] =>
int(47)
}
array(2) {
'letter' =>
string(1) "7"
[0] =>
int(55)
}
array(2) {
'letter' =>
string(1) "/"
[0] =>
int(47)
}
array(2) {
'letter' =>
string(1) "2"
[0] =>
int(50)
}
array(2) {
'letter' =>
string(1) "3"
[0] =>
int(51)
}
array(2) {
'letter' =>
string(1) ","
[0] =>
int(44)
}
array(2) {
'letter' =>
string(1) " "
[0] =>
int(32)
}
array(2) {
'letter' =>
string(1) "3"
[0] =>
int(51)
}
array(2) {
'letter' =>
string(1) ":"
[0] =>
int(58)
}
array(2) {
'letter' =>
string(1) "3"
[0] =>
int(51)
}
array(2) {
'letter' =>
string(1) "1"
[0] =>
int(49)
}
array(2) {
'letter' =>
string(3) "?"
[0] =>
int(8239)
}
array(2) {
'letter' =>
string(1) "p"
[0] =>
int(112)
}
array(2) {
'letter' =>
string(1) "m"
[0] =>
int(109)
}
int(8239)
string(21) "NARROW NO-BREAK SPACE"
string(6) "U+202F"
string(4) "202f"
bool(true)
0 Comments