Excel csv export into a php file with fgetcsv

Question

I'm using excel 2010 professional plus to create an excel file. Later on I'm trying to export it as a UTF-8 .csv file. I do this by saving it as CSV (symbol separated.....sry I know not the exact wording there but I don't have the english version and I fear it is translated differently than 1:1). There I click on tools->weboptions and select unicode (UTF-8) as encoding. The example .csv is as follows:

ID;englishName;germanName
1;Austria;Österreich

So far so good, but if I open the file now with my php code:

 header('Content-Type: text/html; charset=UTF-8');
 iconv_set_encoding("internal_encoding", "UTF-8");
 iconv_set_encoding("output_encoding", "UTF-8");
 setlocale(LC_ALL, 'de_DE.utf8');
 $fp=fopen($filePathName,'r');
 while (($dataRow= fgetcsv($fp,0,";",'"') )!==FALSE)
 {
     print_r($dataRow);
 }

• I get: �sterreich as a result on the screen (as that is the "error" I cut all other parts of the result).
• If I open the file with notedpad++ and look at the encoding I see "ANSI" instead of UTF-8.
• If I change the encoding in notepad++ to UTF8....the ö,ä,... are replaced by special chars, which I have to correct manually.

If I go another route and create a new UTF-8 file with notedpad++ and put in the same data as in the excel file I get shown "Österreich" on screen when I open it with the php file.

Now the question I have is, why does it not function with excel, thus am I doing something wrong here? Or am I overlooking something?

Edit: As the program will in the end be installed on windows servers provided by customers, a solution is needed where it is not necessary to install additional tools (php libraries,... are ok, but having to install a vm-ware or cygwin,... is not). Also there won't be a excel (or office) locally installed on the server as the customer will be able to upload the .csv file via a file upload dialog (the dialog itself is not part of the problem, as I know how to handle those and additionally the problem itself I stumbled over when I created an excel file and converted it to .csv on a testmachine where excel was locally installed).

Tnx

Solution 1

From PHP DOC

Locale setting is taken into account by this function. If LANG is e.g. en_US.UTF-8, files in one-byte encoding are read wrong by this function.

You can try

header('Content-Type: text/html; charset=UTF-8');
$fp = fopen("log.txt", "r");
echo "<pre>";
while ( ($dataRow = fgetcsv($fp, 1000, ";")) !== FALSE ) {
    $dataRow = array_map("utf8_encode", $dataRow);
    print_r($dataRow);
}

Output

Array
(
    [0] => ID
    [1] => englishName
    [2] => germanName
)
Array
(
    [0] => 1
    [1] => Austria
    [2] => Österreich
)

Solution 2

I don't know why Excel is generating a ANSI file instead of UTF-8 (as you can see in Notepad++), but if this is the case, you can convert the file using iconv:

iconv --from-code=ISO-8859-1 --to-code=UTF-8 my_csv_file.csv > my_csv_file_utf8.csv

Solution 3

And for the people from Czech republic:

function convert( $str ) {
    return iconv( "CP1250", "UTF-8", $str );
}
...
while (($data = fgetcsv($this->fhandle, 1000, ";")) !== FALSE) {
$data = array_map( "convert", $data );
...

Comments

[Thomas]

Is there another method to do that without relying on unix commands? (The application shall be able to run on quite a few different windows machines without the need to install additional tools, thus having to install a tool on those servers that allows me to use unix commands is not really something that is good).

[Thomas]

For excel. I checked the files with notepad++. Notepad++ told me they were still ANSI (even though exported as UTF8). And when I import UTF8 encoded files (created with notedpad++) into excel it is as if the wrong encoding was there (I get special chars instead of ö,ü,....).

[Thomas]

The array_map was the solution tnx. Didn't think that I would have to map it explecitely again as UTF-8 if I already did tell the program that it is UTF-8 what I'm reading. (interesting phenomenon.....in addition to excel exporting it still as ANSI ) Tnx again.

[Cimbali]

@ThomasE. Regardless, this solution should work. Plus it should be able to cope with non-UTF8 input. It is not that different from Baba's since it applies utf8_encode to imported strings from your problematic csv files.