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
– Jasper, Commented Nov 26, 2013 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
– Amal, Commented Nov 26, 2013 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
– Jasper, Commented Nov 26, 2013 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
– Paulo Bueno, Commented Nov 27, 2013 at 11:30

Ingus · Accepted Answer · 2016-01-12 18:47:40Z

3

+100

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.

answered Jan 12, 2016 at 18:47

Ingus

1664 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paulo Bueno Over a year ago

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.

Alpesh Panchal · Accepted Answer · 2016-01-08 17:00:08Z

1

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);

?>

answered Jan 8, 2016 at 17:00

Alpesh Panchal

1,71313 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related