Create CSV file containing UTF-8 characters in PHP and Javascript | Illegal Character \ufeff Problem

If you create or read a CSV file and the file contains some characters such as ü or ş, then you will find the characters are not displayed correctly in Excel. This is because the default coding of Excel is not UTF-8. To force Excel to use UTF-8, we need to add BOM (Byte Order Mark) in the beginning of the file.

<?php // When reading csv file using PHP // BOM as a string for comparison. $bom = "\xef\xbb\xbf"; // Read file from beginning. $fp = fopen($path, 'r'); // Progress file pointer and get first 3 characters to compare to the BOM string. if (fgets($fp, 4) !== $bom) { // BOM not found - rewind pointer to start of file. rewind($fp); } // Read CSV into an array. $lines = array(); while(!feof($fp) && ($line = fgetcsv($fp)) !== false) { $lines[] = $line; }

// When writting to csv file $fp = fopen($myFile, 'w'); fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); fputcsv($fp, $otherdata);

// Javascript var csvFormattedDataTable = ''; csvFormattedDataTable += "\uFEFF"; csvFormattedDataTable += "other stuff"; var encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvFormattedDataTable); $(buttonName).attr("href", encodedUri); $(buttonName).attr("download", 'table-data.csv'); $(buttonName).attr("target", '_blank');

Illegal mix of collations for operation 'UNION'

I don't know for how many reason this error occurred but I found it when going to create a view using two tables using UNION. 

There are different ways to solve this problem. You can solve it by compress the value and then decompress as UNCOMPRESS(COMPRESS(x.name)) AS name.

You have another way to fix this problem. You can use first hex the value and then unhex as UNHEX(HEX(x.name)) AS name.

And finally, to fix this, you need to replace some column references in the SELECT list (in one or more of the queries) with an expression, something like CONVERT(name USING UTF8) AS name.

Some more convert functions are listed below:
CONVERT('2014-02-28', DATE)
CONVERT('2014-02-28 08:14:57', DATETIME)
CONVERT('08:14:57', TIME)
CONVERT(125, CHAR)
CONVERT(4-6, SIGNED)
CONVERT(4-6, UNSIGNED)
CONVERT('4', BINARY)
CONVERT('Some String' USING UTF8)
CONVERT('Some String' USING ASCII)
CONVERT('Some String' USING LATIN1)
CONVERT(x.price, DECIMAL)
CONVERT(x.price, DECIMAL(10,2))