Correct csv export to utf-8 with BOM

Question

PHP

You need to export data from the database to open it in excel.
I create csv where separator is the \t sign.
The encoding in the base is utf-8, in php it is also utf-8, when exporting I use the following headers:

header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=".$dt.".xls");
header("Pragma: no-cache");

English letters are fine, Russian excel does not see. If you open the csv file locally and save with BOM, everything works.
If you add BOM before displaying content - excel understands Russian letters, but does not see the separator "\t" - i.e. is not split by columns, all columns are merged in each row.
Tried other separators - excel does not understand them by default.
How to be? how to send csv so that excel opens correctly?

Answer 1

I have everything correctly Excel determines. I use a semicolon separator and insert BOM before writing to .csv:

$csv = fopen('file.csv', 'w');
fprintf($csv, chr(0xEF).chr(0xBB).chr(0xBF));
...

Answer 2

When opening (importing) a CSV file in Excel, you can specify a bunch of options, incl. and delimited encoding and even cell formatting method.
In LibreOffice, by default, it asks for these options; in MS, you need to call the import wizard from text files.