MySQL - Table Data Import Wizard error in MacOS "Unhandled exception: 'ascii' codec can't decode byte 0xef in position 0: ordinal not in range(128)"

user_13101642 0
Viewed 835

I am unable to load any CSV file into MySQL. Using the Table Data Import Wizard, this error pops up every time I get to the 'Configure Import Settings' step:

"Unhandled exception: 'ascii' codec can't decode byte 0xef in position 0: ordinal not in range(128)"

... even though the CSV is encoded as UTF-8 and that seems to be the default encoding setting for MySQL Workbench. Granted, I am not very skilled with computers, I have only a few weeks' exposure to MySQL. This has not always happened to me. I had no issues with this a couple of months ago while I was in a database management course.

But, I think this is where my problem lies: at one point I tried to uninstall MySQL Workbench and Community Server and re-installed, and ever since, this error happens every time I try to load data. I am even using a very basic test file that still won't load (all column types are set to 'Text' in Excel and saved as UTF-8 CSV:

Test data in Excel

Screenshot of error in MySQL Workbench

I am using MySQL 8.0.28 on MacOS 11.5.2 (Big Sur)

mysql-workbench utf-8 csv macos mysql
1 Answers

Case 1, you wanted ï ("LATIN SMALL LETTER I WITH DIAERESIS"):

Character set ASCII is not adequate for the accented letters you have. You probably need latin1

Case 2, the first 3 bytes of the file are (hex) EF BB BF:

That is "BOM", which is a marker at the beginning of the file that indicates that it is encoded in UTF-8. But, apparently, the program reading it dos not handle such.

In some situations, you can remove the 3 bytes and proceed; in other situations, you need to read it using some UTF-8 setting.

Since you say "Text' in Excel and saved as UTF-8 CSV", I suspect that it is case 2. But that only addresses the source (Excel), over which you may not have enough control to get rid of the BOM.

I don't know what app has "Table Data Import Wizard", I cannot address the destination side of the problem. Maybe the wizard has a setting of UTF-8 or utf8mb4 or utf8; any of those might work instead of "ascii".

Sorry, I don't have the full explanation, but maybe the clues "BOM" or "EFBBBF" will help you find a solution either in Excel or in the Wizard.

user_17668310
Related