Handling special symbols in csv file in PHP

PHP provides the fgetcsv function to automatically import values from a csv file. However it does not consistently work with cases where the starting of a field value has an extended character like the pound sign or a foreign character. In such cases, fgetcsv simply omits that first character and gets the remaining data of the column.

Eg.”Buyagift.co.uk”,”273″,”xmas1075″,”£10 off when you spend over £75 on all products excluding our amazing special offers”

will be imported as

“Buyagift.co.uk”,”273″,”xmas1075″,”10 off when you spend over £75 on all products excluding our amazing special offers”

One officially recommended way of handling this is to set the locale before running fgetcsv . But somehow this does not always work. After trying various approaches we have come up with our own solution which works in all cases.

We do our own preprocessing of the csv file before passing it to fgetcsv(). Our approach is to detect any character following a comma which is beyond the ascii printable range of codes from 32 to 126. If it detects any such character it precedes it with a slash. The complete  data thus updated is written back to the file and then passed to fgetcsv().

So “Buyagift.co.uk”,”273″,”xmas1075″,”£10 off when you spend over £75 on all products excluding our amazing special offers”

will become “Buyagift.co.uk”,”273″,”xmas1075″,”£10 off when you spend over £75 on all products excluding our amazing special offers”

At a later stage the once the data array has been obtained from fgetcsv, we remove the preceding slash by running stripcslashes() to the value.

The code fragment is given below:

$f = fopen(“myfile.csv”, “r”);
if ($f) {
$data = fread($f, filesize(“myfile.csv”));
fclose($f);
$arr = explode(“n”, $data);
$newData = “”;
for($i = 0; $i < count($arr); $i++) {
$item = $arr[$i];
if (strlen($item) > 2) {
$x = explode(“,”,$item);
for($j = 0; $j < count($x); $j++)
{
if (ord($x[$j]) > 126)
{
$x[$j] = “\”.$x[$j];
}
}
$arr[$i] = implode(“,”,$x);
$item = $arr[$i];
$newData .= $item . “rn”;
}

}
}

// rewrite processed data back to file
$f = fopen(“myfile.csv”, “w”);
if ($f) {
fwrite($f, $newData);
fclose($f);
}
else
echo(“Unable to create file:”);

Handling The Euro Symbol

The above approach does not work for the Euro symbol since it does not get correctly interpreted even if a file is open in utf-8 mode. For this, the solution to open the file and simply replace the euro symbol with its html entity equiv: & html ; (remove the spaces in between).

Eg. $data = str_replace(“€”,”&euro;”,$data);

3 Comments

  1. Thanks a lot!
    setlocale() did also not work for me and after hours of research I could finally fix this problem with help of your code. Works great…

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.