Removing Special character when importing data from excel

Question

I'm importing data from excel to data table. It's working fine. But in some fields, data imported with unknown character '�'. But excel doesn't have such character. Here is my code.

I have tried in many ways. This is my final code.

$new_prod_desc = (ucfirst($data[0])." ".ucfirst($data[5])." ".ucfirst($data[1])." ".ucfirst($data[2])); $new_prod_desc = preg_replace("/&#?[a-z0-9]+;/i","",$new_prod_desc);

Thanks.

In order to get a proper fix, you'll have to find out what encodings are used in Excel and your database table. — Álvaro González, Feb 20 '13 at 15:38

score 2 · Answer 1 · answered Feb 20 '13 at 15:29

2

That is not a single byte character, but a UTF-8 sequence ef bf bd:

From here:

U+FFFD  �   ef bf bd    REPLACEMENT CHARACTER

Maybe it is replacing an even worse character?

If you just want it to vanish, you could search and replace the byte sequence.

answered Feb 20 '13 at 15:29

wallyk

56,922
16
83
148

when to replace these chars? Before utf8_decode or after utf8_decode? and also which one i want to replace? 'ef bf bd' or 'U+FFFD'? – Devpower Feb 20 '13 at 15:37
@Devpower: I would not use `utf8_decode()` at all, especially if you are not now and all you get is these troublesome characters. – wallyk Feb 20 '13 at 15:51
I understood. Pls tell me which one i want to replace? 'ef bf bd' or 'U+FFFD'? – Devpower Feb 20 '13 at 16:03
I found the character. that is 'Ù'. Any help? – Devpower Feb 20 '13 at 16:19

score 0 · Answer 2 · answered Feb 20 '13 at 15:26

0

First verify that the excel document is in UTF-8 code.

Try to add the function

utf8_decode

like this:

$new_prod_desc = utf8_decode($new_prod_desc);

answered Feb 20 '13 at 15:26

Alessandro Minoccheri

35,521
22
122
171

1

That function generates `ISO-8859-1`. It'll ruin data unless the database uses `ISO-8859-1`. – Álvaro González Feb 20 '13 at 15:37

Removing Special character when importing data from excel

2 Answers2