php fgetcsv and integer conversion (UTF8 x UCS-2 Little Endian)

Question

UPDATE 4:

I've found (as suspected) that the files enconding is responsible for this. I converted the csv file from UCS-2 Little Endian to UTF-8 and everything works fine. Anyway I need to find some way to make PHP understands the input file correctly. Tried already sellocale without success.

--- original question ---

I have some data stored on a csv file in 'table' format and I want to store this data as (tag, tagtimestamp, tagvalue) on a database. But can't find a way to cast values stored on $v array to integer values:

$p=0;
$tag=array();
$dt=array();
$v=array();

$handle=fopen("sga/2013.10.054.vol2.csv", "r");

while(($row=fgetcsv($handle, 0, "\t"))!==FALSE){
    $num=count($row);
    $num--; // only even collumns
    if($p==0){
        for($c=1; $c<$num; $c+=2){
            array_push($tag, $row[$c]);
        }
    }else{
        array_push($dt, $row[0]);
        for($c=1; $c<$num;$c+=2){
            array_push($v, $row[$c]); // <<< here is my problem
        }
    }
    $p++;
}
fclose($handle);

echo "<pre>";
print_r($v);
echo "</pre>";

echoes:

Array(
[0] => 8701
[1] => 5281
[2] => 4341
[3] => 4360
[4] => 8701
[5] => 8239
[6] => 4631
[7] => 4115
[8] => 4123
[9] => 8239
[10] => 8409
[11] => 3978
[12] => 4192
[13] => 4216
[14] => 8409
[15] => 8916
[16] => 3325
[17] => 4444
[18] => 4472
[19] => 8916
[20] => 9550
[21] => 3286
[22] => 4763
[23] => 4789

...

tried to use

(int)$row[$c], 
intval($row[$c]), 
($row[$c]+0), 
settype($row[$c], "integer")
preg_replace('!\s+!', $row[$c]) // to clean up any messy char...

, etc...

I do interpolate values using:

$nv=0;
$sqls="";
foreach ($tag as $kt=>$t){
    foreach($dt as $kd=>$d){
        $f=($kd*5)+$kt;
        $de=explode(" ", $d);
        $d=implode("-", array_reverse(explode("/", $de[0])))." ".$de[1];
        $nv=$v[$f]; // <<<<< here the values must become integers but only returns zeroes
        $sqls.="('$t', '$d', $nv), ";
    }
}

// the final query is setted here

    $sql="";
$sql="INSERT IGNORE INTO aducao (tag, tagtime, tagvalue) VALUES "; 
$sqls=rtrim($sqls, ", ");
$sql.=$sqls;
echo $sql;

My file is UTF.8 without BOM formated.

and CSV file is tab (\t) separated.

Anyway if I left the "values" as it comes from csv my mysql database throws an error, and if I try to convert the values becomes zeroes.

UPDATE:

here's the mysql error if values is not converted. I guess that because if I copy and paste the sql string directly on mysql query browser the error does not occur, so some type of 'dirt' is into these values...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

UPDATE 2:

var_dump($v) shows:

array(3725) { [0]=> string(9) "8701" [1]=> string(9) "5281" [2]=> string(9) "4341" [3]=> string(9) "4360" [4]=> string(9) "8701" [5]=> string(9) "8239" [6]=> string(9) "4631" [7]=> string(9) "4115" [8]=> string(9) "4123" [9]=> string(9) "8239" [10]=> string(9) "8409" [11]=> string(9) "3978" [12]=> string(9) "4192" [13]=> string(9) "4216" [14]=> string(9) "8409" [15]=> string(9) "8916" [16]=> string(9) "3325" [17]=> string(9) "4444" [18]=> string(9) "4472" [19]=> string(9) "8916" [20]=> string(9) "9550" [21]=> string(9) "3286" [22]=> string(9) "4763" [23]=> string(9) "4789" [24]=> string(9) "9550" [25]=> string(11) "11506" [26]=> string(9) "3448" [27]=> string(9) "5738" [28]=> string(9) "5769" [29]=> string(11) "11506" [30]=> string(11) "11287" [31]=> string(9) "3690" [32]=> string(9) "5630" [33]=> string(9) "5656" [34]=> string(11) "11287" [35]=> string(11) "10639" [36]=> string(9) "4329" [37]=> string(9) "5312" [38]=> string(9) "5327" [39]=> string(11) "10639" [40]=> string(11) "11427" [41]=> string(9) "4973" [42]=> string(9) "5704" [43]=> string(9) "5721" [44]=> string(11) "11427" [45]=> string(11) "11688" [46]=> string(9) "5605" [47]=> string(9) "5841" [48]=> string(9) "5847"

UPDATE 3:

part of $sql output

INSERT IGNORE INTO aducao (tag, tagtime, tagvalue) VALUES ('E054_VOL', '2013-10-01 00:00:00', 8701), ('E054_VOL', '2013-10-01 01:00:00', 8239), ('E054_VOL', '2013-10-01 02:00:00', 8409), ('E054_VOL', '2013-10-01 03:00:00', 8916), ('E054_VOL', '2013-10-01 04:00:00', 9550), ('E054_VOL', '2013-10-01 05:00:00', 11506), ('E054_VOL', '2013-10-01 06:00:00', 11287), ('E054_VOL', '2013-10-01 07:00:00', 10639), ('E054_VOL', '2013-10-01 08:00:00', 11427), ('E054_VOL', '2013-10-01 09:00:00', 11688), ('E054_VOL', '2013-10-01 10:00:00', 7973), ('E054_VOL', '2013-10-01 11:00:00', 2067), ('E054_VOL', '2013-10-01 12:00:00', 0), ('E054_VOL', '2013-10-01 13:00:00', 0), ('E054_VOL', '2013-10-01 14:00:00', 0), ('E054_VOL', '2013-10-01 15:00:00', 0), ('E054_VOL', '2013-10-01 16:00:00', 0), ('E054_VOL', '2013-10-01 17:00:00', 137), ('E054_VOL', '2013-10-01 18:00:00', 142), ('E054_VOL', '2013-10-01 19:00:00', 140), ('E054_VOL', '2013-10-01 20:00:00', 169), ('E054_VOL', '2013-10-01 21:00:00', 0), ('E054_VOL', '2013-10-01 22:00:00', 161), ('E054_VOL', '2013-10-01 23:00:00', 9275), ('E054_VOL', '2013-10-02 00:00:00', 7679), ('E054_VOL', '2013-10-02 01:00:00', 8996), ('E054_VOL', '2013-10-02 02:00:00', 9135), ('E054_VOL', '2013-10-02 03:00:00', 9569), ('E054_VOL', '2013-10-02 04:00:00', 10114), ('E054_VOL', '2013-10-02 05:00:00', 10612), ('E054_VOL', '2013-10-02 06:00:00', 10438), ('E054_VOL', '2013-10-02 07:00:00', 10945), ('E054_VOL', '2013-10-02 08:00:00', 11784), ('E054_VOL', '2013-10-02 09:00:00', 12355), ('E054_VOL', '2013-10-02 10:00:00', 12500), ('E054_VOL', '2013-10-02 11:00:00', 12045), ('E054_VOL', '2013-10-02 12:00:00', 12089), ('E054_VOL', '2013-10-02 13:00:00', 12413), ('E054_VOL', '2013-10-02 14:00:00', 13024), ('E054_VOL', '2013-10-02 15:00:00', 13444), ('E054_VOL', '2013-10-02 16:00:00', 12244), ('E054_VOL', '2013-10-02 17:00:00', 10139), ('E054_VOL', '2013-10-02 18:00:00', 8586), ('E054_VOL', '2013-10-02 19:00:00', 8146), ('E054_VOL', '2013-10-02 20:00:00', 8418), ('E054_VOL', '2013-10-02 21:00:00', 8857), ('E054_VOL', '2013-10-02 22:00:00', 9142), ('E054_VOL', '2013-10-02 23:00:00', 9296), ('E054_VOL', '2013-10-03 00:00:00', 10015), ('E054_VOL', '2013-10-03 01:00:00', 9256), ('E054_VOL', '2013-10-03 02:00:00', 9452), ('E054_VOL', '2013-10-03 03:00:00', 11136), ('E054_VOL', '2013-10-03 04:00:00', 110)

That SQL error doesn't help much without the actual query. You didn't tag the question as SQL but if the error is occurring in a query then that's probably where you should be casting. That's the more secure place to do it too. — Jasper, Nov 26 '13 at 19:16
In response to your updated question: What's your query look like? What does `var_dump($sqls);` output (outside the loop)? — Amal Murali, Nov 26 '13 at 19:17
What does `echo $sql` output? That's what I was looking for, the exact query string sent to the MySQL server. — Jasper, Nov 26 '13 at 19:22
@Jasper in fact if I paste the sql output on mysql query browser the sql is executed without error. So the problem occurs only when executed inside php using mysql_query($sql). — Paulo Bueno, Nov 27 '13 at 11:30
@mcuadros indeed I tried that one. Doing that returns 0 for all values. — Paulo Bueno, Nov 27 '13 at 11:31
@PauloBueno I'm not familiar with the `IGNORE` flag(?) you're using. What's its purpose? — Jasper, Nov 27 '13 at 16:00
@Jasper -IGNORE- means that mysql will silent fail in cases where the primary key rule is broken. For the purpose of this question it has no influence thought. — Paulo Bueno, Nov 27 '13 at 16:53
@PauloBueno My last piece of advice is to remove the IGNORE flag and see what you get. — Jasper, Nov 27 '13 at 18:04
As you found out, `fgetcsv()` does indeed rely on the system's locale. Is the locale you are trying to switch to available on your system? (type `locale -a` in your console) — RandomSeed, Jan 11 '16 at 11:33

score 3 · Answer 1 · answered Jan 12 '16 at 18:47

Looking at your var_dump output it looks like PHP has read some unprintable/invisible characters. That is why you see string(9) "8409", where you can clearly count that there are only 4 digits and not 9.

What I would do in your situation:

1) understand whether the values are correct.

2) use some hex viewer/editor and open the file to see what characters are actually there.

3) one of the solutions is probably just to remove all the unneeded (invisible) characters by a simple preg_replace('/[^0-9]+/', '', $value); and then insert them in a SQL query.

Hi @Ingus, I've awarded your answer, but unfornately I didn't tested it already. ASAP I'll accept your answer if it work. ty. — Paulo Bueno, Jan 14 '16 at 20:06

score 1 · Answer 2 · answered Jan 08 '16 at 17:00

Here is how you convert all values of $v to numeric

<?php

//call array function to walk on each element of $v array and convert it to numeric value
array_walk($v, 'convert_to_num');

function convert_to_num(&$value, $key) {

    $value = $value * 1;


}

var_dump($v);

?>

php fgetcsv and integer conversion (UTF8 x UCS-2 Little Endian)

2 Answers2