3

I want to export csv files from php. I decided to use \t = tab as separator to make it unique (use comma as separator is not a good choice because it change from countries to countries). But if i add sep="\t" to my csv it will not recognize the utf-8 bom and when i open the csv in excel, it shows up using ? character to replace °,à, ecc.. If i remove the string "sep=\t" it's decode perfectly in UTF-8 but it won't recognize the \t as separator. How could i use both "sep=\t" and utf-8 bom?

Here is my code:

header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="export.csv"');

/*UTF-8 BOM (Byte Order Mark)

\x = caratteri codifica esadecimale */
echo "\xEF\xBB\xBF";
$f = fopen('php://output', 'w');
 
fwrite($f, "sep=\t");

foreach ($array_to_csv as $line) {
  fputcsv($f, $line,"\t");
}
Mort
  • 3,379
  • 1
  • 25
  • 40
Mattia Bertoni
  • 81
  • 5
  • 2
    Not sure what you are expecting `sep=\t` to do, a CSV file is just a list of records which doesn't know anything about the structure of the content. – Nigel Ren Feb 12 '21 at 08:39
  • just want to tell to excel that the separator to use is not comma but \t – Mattia Bertoni Feb 12 '21 at 08:41
  • [this](https://stackoverflow.com/questions/26805959/fputcsv-with-tab-delimiter) might help – berend Feb 12 '21 at 08:44
  • Already tried it, but it doesn't work, it seem that i can add the bom for utf-8 or the string for separator. If i use both of them, excel recognize \t as separator but it don't decode in utf-8, if i don't add the "sep=\t" string" excel don't recognize \t as separator but it correctly format in utf-8 – Mattia Bertoni Feb 12 '21 at 09:05
  • 1
    Coul it be that `fopen('php://output', 'w');` overwrites your echoed BOM? Maybe try writing that to the out put too, instead of echoing: `fwrite($f, "\xEF\xBB\xBF")` – Hendrik Feb 12 '21 at 09:30
  • You've just suggested to do something like this,aren't you? $f = fopen('php://output', 'w'); fwrite($f, "\xEF\xBB\xBF"); fwrite($f, "sep=\t".PHP_EOL); – Mattia Bertoni Feb 12 '21 at 09:40
  • Quite frankly, I can’t get this to work, when I just create example files using NotePad++ either. Proper recognition of UTF-8 encoded data by Excel only works, when I leave out the `sep=\t` line (`\t` being an actual tab character here), once its in there after the UTF-8 BOM, Excel fails to recognize the encoding, and messes up umlauts. Can you explain where you got this `sep=…` “syntax” from, did you find that documented anywhere? – CBroe Feb 12 '21 at 09:40
  • @MattiaBertoni Exactly, that's what I meant. – Hendrik Feb 12 '21 at 09:41
  • @Hendrik thanks for suggestion, i've tried but it doesn't work – Mattia Bertoni Feb 12 '21 at 09:46
  • @CBroe hi, you've perfectly understood what my problem is :) I found the sep string on another stackoverflow question but i've nevere found any type of documentation related to that – Mattia Bertoni Feb 12 '21 at 09:50
  • Not sure if there is a solution. It looks to me like Excel expects this `sep=…` at the very beginning of the file. Not in the first _line_, but beginning of the file - if there is a BOM before it, it fails to work. I guess your best bet is to use a BOM, and semicolon as separator, but _without_ specifying it via `sep=;`, I think Excel recognizes that the best, regardless of any location-specific settings. – CBroe Feb 12 '21 at 10:18
  • The problem is that... if i use the ; as separator, in Italy for example it works fine, but if i change my pc location to USA and try to redownload the file the ; is not interpreted as separator, because in USA the csv default separator is comma not semicolon. That's why i need the tab as separator, beacuse i guess it's standard in every location – Mattia Bertoni Feb 12 '21 at 10:27
  • From the comments on [php.net](https://www.php.net/manual/en/function.fputcsv.php): the delimiter field only takes one character.Instead of `"\t"` use `chr(9)` – Michel Feb 12 '21 at 13:15
  • Nope @Michel it doesn't work, on the excel file i've no separation between data if i use `chr(9)` . Thanks anyway! – Mattia Bertoni Feb 12 '21 at 13:41
  • You know you are missing a `'` after `"export.csv"`? Can be a typo – Michel Feb 12 '21 at 13:44
  • Yeah sorry, i changed the file name i was using before uploading the answer, in my real code i confirm that's correct, i've don't miss it – Mattia Bertoni Feb 12 '21 at 13:52
  • Maybe this helps? https://stackoverflow.com/a/30813295/562359 – Hendrik Feb 15 '21 at 07:36
  • were you able to find any solution? – dhiraj suvarna May 27 '22 at 09:22

0 Answers0