mysql table import wizard fails to import a csv file

Question

I am trying to import a csv file (Window 10), created by notepad++, using semicolons as delimiters, extension .csv.

I use Mysql Workbench 6.3, import wizard. Encode in notepad++ is UTF-8, and the mysql table I am trying to load is utf-8 default collation

Import wizard fails to import and shows two messages: Table data Import: Can't analyze the file, please try to change encoding type. If that doesn't help, maybe the file is no: csv, or the file is empty

Unhandled exception: 'ascii' codec can't encode character u'\xfa' in position 1: ordinal not in range (128)

How can I do to trace this error. I tried several encodings for the file, but the error persists.

thanks

Cookson

Are you expecting ú? That is what fa is in latin1. And unicode 00FA. Something is saying (or defaulting to) latin1 instead of utf8. – Costate

Answer 1

While you are saving the excel data using "Save as" option select msdos .csv format. Note that there are 3 csv format out of it select only MS-DOS .csv as highlighted in image.

Cauvery

Answer 2

I tried every CSV format there was with no luck. In the end I found that selecting this option worked with a file saved as CSV (MS-DOS) (*.csv):

Nanice

Answer 3

Using import Button tool in the SQL result. See the image

Kirkcudbright

Answer 4

I found this solution and it works for me

The problem is UTF-8 encoding

In excel sometimes it usually fails when saving, what can be used is Google Drive.

1.- Create a Drive Spreadsheet

2.- Import your .csv document

3.- Go to File-> Download as-> csv

And that's it, it should work because it was encoded back to utf-8.

Hollander

Even though this is not a great process and does not really determine the issue, this was the only method that worked in my situation. – Shum

Answer 5

Try libreoffice calc or ms-office excel for creating the csv files no matter what delimeter is used. Make sure to set your encoding to UTF-8. IT will work for you.

Paxon

thanks, I used excel to export a csv utf-8 file. Import process keeps failing – Cookson

"failing" = aborting? truncating? garbage characters? question marks? nothing? – Costate

Answer 6

In my case, I have some accent mark in my table, and that was why it gave me that error. I solve this replacing that characters with notepad (because I believe it's not a good practice to have accents), and then, I could import the table with no problem.

Rugg

Answer 7

I was working with Hibernate and had similar issue. In my case, I set the wrong type of an variable. And then I converted that the raw excel file to csv and picked utf-8 => 0 import.

Thanks to other's suggestions, I changed the type of that variable and then saved the raw excel file as CSV(MS-Dos) and chose cp1250(windows-1250). It worked perfectly.

Haver

Answer 8

You can find the error log is in %appdata%\Mysql\Workbench\log\wb.log

In my finding Workbench contains a couple of bugs:

1. It cannot handle a BOM (byte order mark) in a text file. As you write you are using Notepad++ you are in luck as it allows for many ways to encode a file in- or excluding a BOM
2. As it does not understand BOM it has no way to figure out if your file is utf-8 or ansi, therefor I assume WB under Windows expects a ANSI file. Again when preparing the data with Notepad++ you are lucky as you can convert you file to ANSI first.

Dictum

Answer 9

I encountered this error when I tried to import a field with double-quotes and line breaks in it. The double quotes were correctly escaped per the RFC with another double quote. The line breaks did not break import, the escaped double quotes did not break import, but combining the two did.

As far as I can tell this is a bug in MySQL Workbench.

Afebrile