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

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.

Answer 2

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

Answer 3

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):

Answer 4

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.

Answer 5

In case somebody finds this like me and needs a solution:

https://help.surveygizmo.com/help/encode-an-excel-file-to-utf-8-or-utf-16

This explains how to save the file with the correct encoding.

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.

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.

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.

Answer 9

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.