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
1 2 3 4 5 6 7 8 | $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
1 2 3 4 5 | php -i | grep -i icuICU version => 72.1ICU Data version => 72.1ICU TZData version => 2022eICU 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <?phpinclude '../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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | 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