CODEWITHSUNDEEP
phpmysqlcharacter-encoding
Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Mysql insert text data truncated by weird character encodings

I'm importing data from a CSV file that comes from excel, but i can't seem to insert my data correctly. This data contains french accented characters and if i open the CSV with OpenOffice (i don't use excel) i just select UTF-8 and the data gets converted and shown fine.

If i try to read that into php memory, i can see they are UTF-8 encoded strings if i use MB_DETECT_ENCODING. I connect to a database and specify all UTF-8 charsets using:

mysql_query('SET character_set_results = "utf8", character_set_client = "utf8", character_set_connection = "utf8", character_set_database = "utf8", character_set_server = "utf8"');

And i can certify that my database contains UTF-8 only fields and tables.

What happens is that my content gets truncated at the first accented character. But that happens only in my php script it seems. I output all my data to the browser and if i copy the INSERT statement, it inserts the whole data.

There might be something going on between php and the browser output but i can certify that it's not in the programming of the script... Thus far, i was able to circumvent this issue by HTMLENTITY'ing all my data, but the problem is that my search engine is going coo-coo-crazy because of that...

Any reason or way you can spare would be really appreciated...

EDIT #1:

I searched for the default excel encoding of CSV data and found out it was CP1252. I tried using ICONV('CP1252', 'UTF-8//TRANSLIT', $data) and now, the accented characters seem to fit. I'm going to try it everywhere in my script to see if all my accented character issues are fix and post the solution if so...

Upvotes: 2

Views: 1274

Answers (4)

Angus Walker
Angus Walker

Reputation: 398

I have just had a similar problem and although I tested the $value using MB_DETECT_ENCODING and it said it was UTF-8, it still truncated the data.

Not knowing what to convert from, I couldn't use the iconv function mentioned above.

However I forced it to UTF-8 using utf8_encode($value) and everything works fine now.

Upvotes: 1

Florian Margaine
Florian Margaine

Reputation: 60767

I also had to work on such a project, and, seriously, PHPExcel was my savior to avoid any brainfuck.

P.S. : also, there is this link to help you getting started (in french).

Upvotes: 1

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

After countless tries, i was able to fix all my encoding problems but some of them i still don't know why they happen. I hope this will give some help to someone else later:

function fixEncoding($data){

    //Replace
    return iconv('CP1252', 'UTF-8//TRANSLIT', $data);

}

I used this function now to recode my strings correctly. It seems that excel saves data as CP1252 and NOT utf-8.

Further more, it seems there is a bug with accented characters at the start of a string in a CSV if you use fgetcsv, so i had to forego usage of fgetcsv and create an alternate method cause i'm not in PHP 5.3, maybe str_getcsv could have fixed my issue i'm not sure but in the current case it couldn't cause i don't have the function. I even tried looking for ports and nothing seems to exist and work correctly.

This is my solution, although very ugly, it works for me:

function fgetcsv2($filepointer, $maxlen, $sep, $enc){

    $data = fgets($filepointer, $maxlen);
    if($data === false){
        return false;
    }
    $data = explode($sep, $data);
    return $data;

}

Good luck to all who get similar problems

Upvotes: 1

abcde123483
abcde123483

Reputation: 3905

Which encoding are you using for your tables?

MB_DETECT_ENCODING is not 100% correct all the time and no encoding detecter can ever be that.

Upvotes: 0

Related Questions