1

i am using load data local infile function to insert data from csv to database. Everything works fine. But the word CLOUTÉ is replacing with CLOUTÉ. I have no idea, how to prevent it.

1

I fixed it by using CHARACTER SET UTF8 option in my query.

$loadmeth = "'".'"'."'"; 
LOAD DATA LOCAL INFILE   '$current_date/dummycsv.csv' 
       INTO TABLE tablename
       CHARACTER SET UTF8
       FIELDS TERMINATED BY ','
        ENCLOSED BY $loadmeth
       LINES TERMINATED BY '\r\n' 
       IGNORE 1 LINES;
0

This is probably a character set problem. To verify, you'll have to find the character set of the column, or if none defined, then that of the table, or if none defined, then that of the database or or if none defined, then your MySQL default.

To see the character set and collation of your columns and table, you can do e.g.:

SHOW CREATE TABLE name_of_table;

This should show a character set for the table. If any collation is defined for the column or table, it will also be displayed in the output from that command. To see the character set and collation for your database, you can do:

SHOW CREATE DATABASE name_of_database;

You can also do this to see the server's default character set and collation:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

You'll need to use something like utf8 or latin2 for the character set to display letters like "É". At the same time you may also want to set the collation to something similar, e.g. utf8_general_ci or latin2_general_ci (although these are the default collations for their respective character sets).

You can set this like below in your my.cnf (in the appropriate sections), then restart the MySQL instance:

character-set-server=latin2_general_ci

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.