Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I use SequelPro for MySQL on a Mac OS X. I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message:

File Read Error: An error occurred when reading the file, as it could not be read using the encoding you selected (Auto-detect - Unicode (UTF-8)). Only 23,000 rows were imported.

When I hit "Ok," everything else seems to work relatively fine, I'm just missing about 107,000 rows.

Any idea as to what it could be? Maybe I should use something other than auto-detect during the import? I thought that it might have been some extra commas floating around in the actual .csv file, which there were, but I got rid of those and the same thing happened.

Out of 130,000 rows, there's definitely the possibility for some non-English characters. Which ones doesn't MySQL accept and how would I find and replace them?

This is what I'm getting when I run the character set query:

show variables like 'character_set%';
Variable_name               Value
character_set_client        latin1
character_set_connection    latin1
character_set_database      latin1
character_set_filesystem    binary
character_set_results       latin1
character_set_server        latin1
character_set_system        utf8
character_sets_dir          /usr/local/mysql-5.6.10-osx10.7-x86_64/share/charsets/
share|improve this question
up vote 1 down vote accepted

This may depend on where you generated the CSV file. If the CSV file was generated on a Windows machine, there could be some character set issues


See the following URLs as SequelPro's character set problems are not new

If the CSV file was generated on another Mac OSx server, you should not be having this issue.

You may have to resort to setting the default character set to match that CSV file. Sounds weird to here it goes:

Please run this query and you will see something like this:

mysql> show variables like 'character_set%';
| Variable_name            | Value                      |
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
8 rows in set (0.00 sec)


You can also see the character set of the database

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)


Perhaps you should load another table that has the matching character set:

CREATE TABLE anothertable LIKE mytable;

Change the whole table's character set

ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

or change a column's character set

ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8;

Then, have SequalPro load anothertable.

I guess to be less aggressive, just change the column's character set.

share|improve this answer
Thanks! You're probably exactly right as I'm using a Mac and this was exported on Windows. However, while reading through a few of the links you posted, I don't see a direct answer on how I'd go about fixing it if I have no other option rather than to export from a Windows to a PC. Is there one? Is it just that I have to find/replace certain non-english characters? – Ryan Apr 22 '13 at 18:52
Using ALTER TABLE seems to make sense, but it would be me changing the "Latin" sets to the "UTF8" sets? Also, how would I know what column has bad character sets? – Ryan Apr 22 '13 at 19:09
I am afraid that would be trial-and-error for all your CHAR/VARCHAR/TEXT fields. – RolandoMySQLDBA Apr 22 '13 at 21:22

I just came cross the same problem, and I found a easy way to solve it.

When you use mysqldump just add --hex-blob, this solve my problem.

share|improve this answer

Community Wiki answer generated from an edit to the question by its author, Ryan

I also don't know if this is an option for everybody, but since the problem stems from exporting from a Windows machine to a Mac then opening it in Excel for Mac, the problem seems to go away if I just use OpenOffice instead.

share|improve this answer

I had the same issue with a large data base. I switched the encoding to western latin (iso-1) and it resolved the issue.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.