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

Question

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??

Ebonyeboracum

Read and follow UTF-8 Everywhere as well as UTF-8 all the way through. – Tiki

See "truncation" in #38364066 – Bethsaida

Answer 1

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.

Ebonyeboracum

In other words, Notepad messed up the data. – Bethsaida

that works for small pieces of data, but it'd be challenging to do for large data sets – Anal

Answer 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.

Constringent

Answer 3

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

Familial

This is not a solution... it's like closing your eyes not to see – Enchondroma