Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Read SQL files in UTF8

askari's profile image askari posted 3 years ago in Running SQL scripts

When I open a utf8 file (with BOM) and :

  • leave encoding "auto-detection", the application raise error "stream read error".
  • I specified encoding "utf8", the application raise error "stream read error".
  • I specified encoding "unicode", the application freeze.

When I open a utf8 file (WITHOUT BOM) and :

  • leave encoding "auto-detection", work but not "decode" accent/specials characters (Result : "Bol en céramique fleurs" expected : "Bol en céramique fleurs");
  • I specified encoding "utf8", the application raise error "stream read error";
  • I specified encoding "unicode", the application freeze.

N.B. (1) The sql file is an export from mysqldump. N.B. (2) When I import with phpMyAdmin (and specified utf-8 encoding in html form), BOTH (with and without BOM) imports work perfectly. Note : I like so much HeidiSQL that phpMyAdmin except for this probem.

========================================

And for suggestion, an option for execute directly the file (not just the big files).

thagoob's profile image thagoob posted 3 years ago

Same problem here.

I've got an UTF-8 encoded SQL file to be imported to MySQL database.

On load, it says "stream error" if "auto-detection" or "UTF-8" is chosen. If "ANSI" is chosen on import, the file loads but is definetely scrambled, e.g. German umlauts read "Anschlüße" instead of "Anschlüsse".

I'm using latest version available: 9.3.0.5083

archfrog's profile image archfrog posted 3 years ago

You might want to try "Load SQL file" AND THEN "Run Query" instead of "Run SQL file". It seems to me that there is an issue with "Run SQL file" so it does not properly handle all supported file formats whereas the other method of loading and then running seems to work beautifully for me at least. If nothing else works, you can open the SQL file in your favorite UTF-8 enabled editor, copy it to the clipboard, and then paste it as a SQL query in HeidiSQL. This method also seems to work for me at least.



Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
ansgar's profile image ansgar posted 3 years ago

When loading a file, you should specify the encoding if you know it, as the auto-detection produced some wrong results in the past. I have already increased the examined text portion to 1MB but that does not always seem to do it. So, prefer UTF-8 or whatever over Auto-detect (may fail)

nikkk's profile image nikkk posted 6 months ago

Hi,

There has been many reports about problems with UTF-8.

I tried with version 9.5 and it was still not possible to import SQL file with UTF8 BOM (0xEF,0xBB,0xBF). When opening the SQL file into Query window, or menu File->Open SQL File (Ctrl-O) then Heidi seems not to recognize the BOM and Query window shows an empty space on the very first character position (like if file starts with Space character). If then try to run the SQL script (F9) Heidi produces an error with popup Message box and procesing is cancelled.

You can reproduce the problem like this:

  1. Open Windows Notepad and type whenever SQL you want; for example, "-- This is SQL comment".
  2. Save file as test.sql when choosing UTF-8 on the Encoding combobox on the botton-right corner of SaveAs window. -> now you have SQL file with UTF-8 BOM;
  3. Back on HeidiSQL, press Ctrl+O and open the sql file. It is no matter what encoding you chose in Heidi's Open window.
  4. The Query window shows space before comment
  5. Press F9 -> SQL Error (102): Incorrect syntax near ''.

I found no workaround to this issue. Regards

1 attachment(s):
  • heidiutf8
ansgar's profile image ansgar posted 6 months ago

The UTF-8 encoding in the file-open dialog refers to "UTF-8 without BOM". You should just store your files without BOM.

Please login to leave a reply, or register at first.