3

I was trying to import csv data file using command prompt in MySQL Workbench (mysql Ver 8.0.30 for Win64 on x86_64 (MySQL Community Server - GPL)).

Using following steps:

mysql> load data local infile 'F:/Data Analysis/Data-Analysis-Project/Global_SuperShop_Project/Global_Store.csv'

-> into table global_store
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> ignore 1 rows;

ERROR 1300 (HY000): Invalid utf8mb4 character string: 'Paysand'

P.S: I can get rid of this error if i manually replace the value of "Paysandú" with "Paysandu" but then it shows other similar errors in that table column.

How can I convert utf8mb4 to utf-8 so mysql workbench can import the data or is there any way I can convert this in Ms-Excel??

Emon Rahman
  • 71
  • 1
  • 5
  • Read and follow [UTF-8 Everywhere](https://utf8everywhere.org/) as well as [UTF-8 all the way through](https://stackoverflow.com/questions/279170/). – JosefZ Aug 25 '22 at 14:43
  • See "truncation" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Aug 26 '22 at 04:12

3 Answers3

4

I found the solution to this problem of mine. Actually, my CSV file was encoded in ANSI not in a UTF-8 format so I need to convert it first.

To convert your ANSI encoded CSV file to UTF-8 you have to open your CSV file in notepad and then click the save as option. In the popped-up window, you can see the encoding type option beside the save button. Change it to your desired format and save it.

Emon Rahman
  • 71
  • 1
  • 5
2

You should specify the character set of the file you are loading, unless it has the "default" character set of your MySQL server:

mysql> load data 
  local infile 'F:/Data Analysis/Data-Analysis-Project/Global_SuperShop_Project/Global_Store.csv'
  into table global_store
  character set latin1
  . . .

But you need to know what character set your file is in, of course. If latin1 does not work, try other sets, check the full list at https://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html.

Oleg Muravskiy
  • 705
  • 5
  • 8
0

I had the same problem. I've managed to fix it by setting the raise_on_warnings to False in mysql connection configuration.

enter code here

def mysql_connection():
    connection = mysql.connector.connect(
        user='root',      
        password='xxxxxxxxxx',
        host='127.0.0.1',
        database='xxxxxxxxxx',
        raise_on_warnings=False)
    return connection
Ahmed Khyrat
  • 1