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.
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;
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
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.
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: