Utf8 issue with ° on mysql insert

Question

i have a project that running on Laravel.

My function get files ( UTF8 files ) in storage then parse/insert datas into mysql table ( Mysql is in utf8mb4_unicode_ci ) .

I'm stucked because of this error on some files on insert : General error: 1366 Incorrect string value: '\xC2'

Concerned insert text fail : "La clairière d'Eole n°5"

The issue seems to be the "°" charactere. When i remove it, the insert work. No problem with the accent, juste the ° and i dont know why.

I tried some preg_replace, iconv but without success.

i can insert `La clairière d'Eole n°5` without problem on `utf8mb4_unicode_ci` or `utf8mb4_bin` , and mysql default-character-set=utf8mb4 — kenken9999, Sep 04 '18 at 15:46
it work if i do an utf8_encode but the text become "Lotissement La clairiÃ¨re d'Eole nÂ °5 " — Kévin Leval, Sep 04 '18 at 21:34

score 0 · Answer 1 · answered Sep 04 '18 at 20:12

0

Depending on the software you are using to read your CSV you may need to adjust the CSV document outputting script.

On Windows, MS Excel, expects an UTF-8 encoded CSV with its corresponding BOM character. To fullfill this requirement, you simply need to add the UTF-8 BOM character.

On a MacOS system, MS Excel requires a CSV encoded in UTF-16 LE using the tab character as delimiter.

You can use this library for parse csv file. They already include this stuff into library. https://csv.thephpleague.com/9.0/interoperability/encoding/

answered Sep 04 '18 at 20:12

Andrey Svyrydov

146
7

I see what you mean but i read flat text file with delimited position, no CSV here. – Kévin Leval Sep 04 '18 at 21:10
Maybe this answer can help https://stackoverflow.com/questions/34480910/unable-to-insert-degree-symbol-in-mysql – Andrey Svyrydov Sep 04 '18 at 21:21

score 0 · Answer 2 · answered Oct 01 '18 at 22:39

It sounds like somethings is set to ascii, not utf8, nor even latin1.

° is not available in ascii, hence the likely cause of the error. In latin1, it is hex B0. In utf8, it is C2B0. Since there error message mentions only C2 I am guessing the target character set is ascii, not latin1.

Please provide SHOW CREATE TABLE and the technique you are using for reading the files -- more than "read flat text file".

Utf8 issue with ° on mysql insert

2 Answers2