CODEWITHSUNDEEP
phpmysqlexcelexportmysqli
Andrew Morris
Andrew Morris

Reputation: 1652

MySQL to Excel charset issue

I have a database set up which accepts user registrations and their details etc. I'm looking to export the database to an excel file using php.

The problem I am having is that some of the entrants have entered foreign characters in, such as Turkish, which has been written into the database 'incorrectly' - as far as I have ascertained, the charset was likely set up incorrectly when it was first made.

I have made my code to export the database into excel (below) but I cannot get the Excel document to show correctly regardless of how I try to encode the data

<?php 

require_once('../php/db.php');

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=Download.xls");
header("Pragma: no-cache");
header("Expires: 0");

$query =    "SELECT * FROM users";

$result = mysqli_query($link, $query);
if($result) {
    $count = mysqli_num_rows($result);
    for($i=0; $i<$count; $i++) {
        $field = mysqli_fetch_field($result);
        $header .= $field->name."\t";
        while($row = mysqli_fetch_row($result)) {
            $line = '';
            foreach($row as $value) {
                if((!isset($value)) OR ($value == "")) {
                    $value = "\t";
                } else {
                    $value = str_replace('"', '""', $value);
                    $value = '"'.$value.'"'."\t";
                }
                $line .= $value;
            }
            $data .= trim($line)."\n";
        }
        $data = str_replace("\r", "", $data);
        if($data == "") {
            $data = "\n(0) Records Found!\n";
        }
    }
    print mb_convert_encoding("$header\n$data", 'UTF-16LE', 'UTF-8');
} else die(mysqli_error());
?>

When I do this it comes up with an error when opening it up saying that Excel doesn't recognise the file type, it opens the document but its drawn boxes around all the Turkish characters its tried to write.

I'm no PHP expert this is just information I've kind of pieced together.

Can anyone give me a hand?

Much appreciated

Moz

Upvotes: 0

Views: 1727

Answers (1)

eggyal
eggyal

Reputation: 125855

  1. First of all, you appear to be creating a tab-delimited text file and then returning it to the browser with the MIME-type application/octet-stream and the file extension .xls. Excel might work out that's tab-delimited (but it sounds from your error as though it doesn't), but in any case you really should use the text/tab-separated-values MIME type and .txt file extension so that everything knows exactly what the data is.

  2. Secondly, to create tab-delimited files, you'd be very wise to export the data directly from MySQL (using SELECT ... INTO OUTFILE), as all manner of pain can arise with escaping delimiters and such when you try to cook it yourself. For example:

     SELECT * FROM users INTO OUFILE '/tmp/users.txt' FIELDS TERMINATED BY '\t'

Then you would merely need to read the contents of that file to the browser using readfile().

If you absolutely must create the delimited file from within PHP, consider using its fputscsv() function (you can still specify that you wish to use a tab-delimiter).

  1. Always use the .txt file extension rather than .csv even if your file is comma-separated as some versions of Excel assume that all files with the .csv extension are encoded using Windows-1252.

  2. As far as character encodings go, you will need to inspect the contents of your database to determine whether data is stored correctly or not: the best way to do this is to SELECT HEX(column) ... in order that you can inspect the underlying bytes. Once that has been determined, you can UPDATE the records if conversions are required.

Upvotes: 2

Related Questions