CSV encoding - black diamonds!

[tomhoad]

I'm importing a CSV using the fgetcsv() function, which is working all good.

 

However, when I take a look at the data in the database, I see black diamonds with question marks. This isn't too much of an issue when echoing the data back out again, as they don't appear, but when I want to use one of the CSV fields as a MySQL date, it isn't recognised as a date and is stored as 0000-00-00.

 

e.g.

 

 

I think this issue is something to do with encoding of the CSV? Can anyone offer any advice?

 

If it helps here is my import script, and the encode type is ASCII according to mb_detect_encoding

 

<?php
include 'config.php';
include 'opendb.php';
ini_set("auto_detect_line_endings", true);

$row = 0;
$tmpName = $_FILES['csv']['tmp_name'];

if (($handle = fopen($tmpName, "r")) !== FALSE) {
    $num = count($data);

     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
        $noQuotes = str_replace("\"", '', $data);
        $originalDate = $noQuotes[1];

        //$delivery_date = date('Y-m-d', strtotime($originalDate));

        $parts = explode('/', $originalDate);
        $delivery_date = $parts[2] . '-' . $parts[1] . '-' . $parts[0];

        $row++;

        $import="INSERT into dispatch (delivery_note_number, delivery_date, dispatch_date, customer_delivery_date, delivery_line, produce, variety, quantity, pallets, count, depot, customer, grower, haulier, status) 
        values ('$noQuotes[0]', '$delivery_date', '$noQuotes[2]', '$noQuotes[3]', '$noQuotes[4]', '$noQuotes[5]', '$noQuotes[6]', '$noQuotes[7]', '$noQuotes[8]', '$noQuotes[9]', '$noQuotes[10]', '$noQuotes[11]', '$noQuotes[12]', '$noQuotes[13]', '$noQuotes[14]')";

        echo $import; 
        mysql_query($import) or die(mysql_error());
     }

        //header("location:list_dispatch.php?st=recordsadded");


    fclose($handle);
}

?>

[silkfire]

I think those are NUL characters (ASCII code 0x00), replace them like this:

 

$string = str_replace(chr(0), '', $string);

[xyph]

It looks like a multi-byte encoding being converted to single-byte encoding.

 

There's some form of padding being applied to characters that have single-byte values.

 

mb_detect_encoding and mb_convert_encoding will probably help.

[tomhoad]

silkfire - your solution worked. Many thanks!