I have a MySQL (utf8) table on the web which I wanted to replicate on
my desktop in EDB(u).

I ran MySQL Export to csv and specified utf16.  
But no matter how I imported into EDB I had incomprehensible errors.
I even tried exporting to SQL INSERT statements but they generated an
error complaining that the values clause was terminated by a ')'.
Eventually I opened the .csv file in a Hex editor and found that ..
... MySQL had inserted a BOM at the start of *every* line of the csv
file!

How weird is that!
What a pain.  <grumble>

Malcom,

1) Have you tried to get MySQL to export to UTF-8? I believe only XE5 uses UTF-16. Prior versions of Delphi use UTF-8. You may have to create a MySQL UTF-8 database DB8 and insert the rows from the UTF-16 table in database DB16 using:
   create table DB8.MyTableUTF8 like DB16.MyTableUTF16;
   insert into DB8.MyTableUTF8 select * from DB16.MyTableUTF16;

Now export DB8.MyTableUTF8 to a CSV file.

2) If the above code doesn't work, you will need to write a small Delphi program to convert from UTF-16 to UTF-8. This should work if you are NOT using XE5 (since Delphi 2009 to XE4 uses UTF-8 and XE5 uses UTF-16 for Strings).
See http://stackoverflow.com/questions/259836/what-is-the-best-way-to-convert-tbytes-utf-16-to-a-string

Barry

On 12/5/2013 10:42 AM, Barry wrote:
> 1) Have you tried to get MySQL to export to UTF-8? I believe only XE5 uses UTF-16. Prior versions of Delphi use UTF-8.

I don't think that is correct.

None of the Delphi versions use UTF8 natively - UTF16 is the native
delphi unicode type (widestring basically since that's what Windows API
uses also so it makes things easy - most APIcalls all you need to do is
cast to PChar etc).  There are utility functions to work with UTF8.

The change in XE5 NextGen compiler was the removal of UTF8 support (and
AnsiString and WawByteString).

AFAIK (not using XE5 yet) you can still use the UTF8 with XE5 - just not
for nextgen (iOS, Android) projects.

Raul

Hi Barry, Raul

I was just having a moan!
Once my Hex Editor stripped out the extra BOMs, the import ran as
expected.
I used EDBManager to do the import, it was a one-off.

My EDB databases are all Unicode (multi-lingual, even Chinese), but I
use MySQL on my website and it is utf-8 as is all my php, etc, there.

I was just gobsmacked to discover that MySQL Export would write a BOM
to each line of the CSV file.
I can see no setting to control that, or warning about it.
No wonder Tim choked on the file.
I have no plans to ask him to cater for such an insanity.  
But I have added this to my KBase in case it bites me in the future.

Malcolm

Raul,

You're right. Delphi 2009 (and later) defaults to UTF-16, not UTF-8. I don't know what I was thinking.

Barry

Malcolm,

<< I was just gobsmacked to discover that MySQL Export would write a BOM to
each line of the CSV file. >>

That, indeed, is a weird one.  I wonder what the rationale is for that
design, or if it's a straight-up bug.  My guess is the latter....

Tim Young
Elevate Software
www.elevatesoft.com