How to remove special accented characters while doing select from mysql into excel file

Question

I am trying to dump some data from mysql into an excel sheet . But the exported excel sheet contains some special accented characters(like ï¿½ï¿ Â) which is not present in the database .

My database is in "latin1" encoding and the centOS has "utf-8" code page . I tried to convert the field while selecting as below , but still few of the fields contain those accented characters . _

select convert(cat.name USING latin1)as category from category_table cat 
into outfile 'course_details.csv'
fields terminated by '|' lines terminated by '\n';

Also, converting it to utf8 also doesn't help . I have also tried double conversions .

Thanks for the help in advance !

It's unfortunate that you use CSV as a data transfer format. (Could you first create an Excel workbook and put the SQL in a Power Query query?) Anyway, CSV is a type of text file. All text files have a character encoding. The reader must use the same encoding as the writer. If you use Excel's Text Import (instead of Power Query), the wizard will ask you the encoding (or "origin"). How would you answer? If you are giving the CSV file to someone, tell them, too. — Tom Blodget
– Tom Blodget, Commented Apr 27, 2018 at 23:15
Do you have another example of the Mojibake characters? I have failed to reverse engineer ï¿½ï¿ Â. — Rick James
– Rick James, Commented Apr 28, 2018 at 0:54
@Tom : Thanks for the pointer , but we are working on restricted environment where no GUI is present and we just have centos prompt , so no external tools can be installed and neither we can connect it via external windows machine and use Power Query ; and hence the complications . — Abhishek kumar
– Abhishek kumar, Commented May 7, 2018 at 8:19

Abhishek kumar · Accepted Answer · 2018-05-21 08:48:00Z

0

looks like conversion to latin 1 solved the problem . The other accented characters were already seeded into the db via some invalid means .

answered May 21, 2018 at 8:48

Abhishek kumar

139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to remove special accented characters while doing select from mysql into excel file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related