CODEWITHSUNDEEP
mysqlcsvencodingimport
user1907514
user1907514

Reputation: 301

mysql table import wizard fails to import a csv file

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

Upvotes: 19

Views: 55143

Answers (9)

siliconrockstar
siliconrockstar

Reputation: 3664

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.

Upvotes: 0

theking2
theking2

Reputation: 2793

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.

Upvotes: 0

Trang Nguyen
Trang Nguyen

Reputation: 21

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.

Upvotes: 0

AlexisC
AlexisC

Reputation: 1

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.

Upvotes: 0

Setink
Setink

Reputation: 31

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.

Upvotes: 2

Jossy
Jossy

Reputation: 989

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

enter image description here

Upvotes: 3

Dinesh Ravi
Dinesh Ravi

Reputation: 1225

There are 3 csv

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.

Upvotes: 25

Biết Tuốt
Biết Tuốt

Reputation: 21

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

Sử dụng công cụ import trên cửa sổ câu lệnh SQL.

Upvotes: 2

Pt. Raman Sharma
Pt. Raman Sharma

Reputation: 309

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.

Upvotes: 0

Related Questions