Print non-ASCII characters into a CSV file

Question

I'm trying to create a CSV file using php. How can I print non ascii characters?

Answer 1

It is possible to use unicode characters in CSV-files, just make sure you use the right HTTP-headers. This works great in OpenOffice, but if I remember right Excel has some problems displaying CSV-files with unicode characters.

Furthermore you should try to use fputcsv, it makes things easier. When you are creating files on the fly, you can use the php output stream.

So something like this:

$handle = fopen("php://output", "w");

header("Content-Type: text/csv; charset=UTF-8");
fputcsv($handle, $fields, ';', '"');

fclose($handle);

EDIT
After reading your comments it seems you have problems converting htmlentities like é. To convert these entities you have to make sure every field is decoded. You can do this with html_entity_decode like this:

$decoded_string = html_entity_decode($string, ENT_QUOTES, 'UTF-8');

Btw, most of the time it's not a good idea to store text with htmlentities in a database, because when you don't want to output html (like in this case) you have to convert them back to real characters. It's easier to just store the text as unicode.

Answer 2

fputcsv should handle utf-8.

Answer 3

Here is what I use, I am sure it could use a little refining for your situation but overall very generic and very useful for many situations.

You just feed the function your sql and it will spit out a csv with a header line of the column names.

<?php
function exportMysqlToCsv($csvsql,$filename = 'export.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = $csvsql;

    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);


    $schema_insert = '';

    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for

    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;

    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {

                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $meta = mysql_fetch_field($result, $j);
                    if($meta->type == "int" || $meta->type == "real")
                    {
                      $schema_insert .= $row[$j];
                    } else {
                      $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                    }
                }
            } else
            {
                $schema_insert .= '';
            }

            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for

        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while

    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;

} 
?>

Answer 4

The best example which I found is this one.

    function str_to_csv($row) {
        if ($row == '') {
            return array();
        }
        $a = array();
        $src = explode(',', $row);
        do {
            $p = array_shift($src);
            while (mb_substr_count($p, '"') % 2 != 0) {
                if (count($src) == 0) {
                    return false;
                }
                $p .= ',' . array_shift($src);
            }
            $match = null;
            if (preg_match('/^"(.+)"[\r\n]*$/', $p, $match)) {
                $p = $match[1];
            }
            $a[] = str_replace('""', '"', $p);
        } while (count($src) > 0);
        return $a;
    }

    function file_getcsv($f) {
        $line = fgets($f);
        while (($a = str_to_csv($line)) === false) {
            if (feof($f)) {
                return false;
            }
            $line .= "\n" . fgets($f);
        }
        return $a;
    }

    function file_to_csv($filename) {
        ini_set("auto_detect_line_endings", true);
        $a = array();
        $f = fopen($filename, 'r');
        while (!feof($f)) {
            $rec = file_getcsv($f);
            if ($rec === false) {
                return false;
            }
            if (!empty($rec)) {
                $a[] = $rec;
            }
        }
        fclose($f);
        return $a;
    }

    $data = file_to_csv('base2.csv');

    echo '<pre>';
    print_r($data);

Answer 5

As you say, they are generated on-the-fly (i.e. you're using echo etc to directly output them), then following will help:

1) Add this header at very beginning of your PHP code:

 header ('Content-type: text/csv; charset=utf-8');

2) Add this meta in the HTML code:

 <meta http-equiv="Content-Type" content="text/html;charset=UTF-8">

3) Save your PHP code file as UTF-8 without BOM.

Answer 6

When you select the page info for the web page, check what is the file encoding. Its should be UTF-8. If not, the data that you are outputting is non UTF.
Also please what Character encoding your browser has set. In Firefox its in menu->view->character encoding.

Answer 7

actually shamittomar's answer is very nice but it miss one thing

your problem is related to your encoding
you should convert your text encoding to UTF-8 since php using ascii internally

example :

$str = mb_convert_encoding($str , "UTF-8") ; 

consult the php.net for more information

Answer 8

Simply, use utf8_encode() function, where you want to print or get non ASCII Character.