No decimals imported ??? Help..

[pookie62]

Hi,
I'm importing score values into a Score table.
Data is inserted, but the decimal values are all zero.

This is the data from the csv file:
Id InschrijvingId SerieNr Tijd Punten
-1342470134 -2066231603 1 10,53 100
1642315376 -2036312949 1 14,63 100
1752376856 -2000188461 1 9,61 100

This is how i't's shown in the table:
Id |InschrijvingId SerieNr Tijd Punten
-932280388 |529280601 1 14.00 100
-1847839509 | -2128794105 1 19.00 100
1783236609 |-2105805680 1 19.00 100


This is the script I use:

<?php
mysql_select_db($database_ASN, $ASN);
if (!file_exists('Score.csv'))
{
die ("Er is geen Score bestand aangetroffen, voer eerst de Upload uit vanaf het Website menu !");
}
$fp = fopen('Score.csv', 'r');
 
// first line has column names 
$data = fgetcsv($fp, 2048, ';'); 
$columns = array(); 
foreach($data as $column) 
$columns[] = trim($column, '"'); 

$sql = 'INSERT INTO testscore ('; 
$sql .= implode($columns, ', '); 
$sql .= ') VALUES ('; 

// next lines have values 
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE) 
{ 
  $checksql = "SELECT Id FROM testscore WHERE Id='".$data[0]."'"; 
  $result = mysql_query($checksql) or die(mysql_error()); 
  $row = mysql_fetch_row ($result); 
  if ($row[0]) 
  { 
     $sql2 = "UPDATE testscore SET "; 
     $sql_clause=array(); 
     foreach ($data as $key=>$column) 
     {    
        if ($key != 0) 
        { 
             $sql2 .= $columns[$key]."='".mysql_real_escape_string($column)."',"; 
        } 
     } 
     $sql2 = rtrim($sql2, ","); 
     $sql2 .= " WHERE ".$columns[0]." = '".mysql_real_escape_string($data[0])."'"; 

  } else 
  { 
     $sql2 = $sql; 
     foreach($data as $column) 
     { 
       $column = mysql_real_escape_string($column); 
       $sql2 .= "'{$column}', "; 
     } 
     $sql2 = rtrim($sql2, ', '); 
     $sql2 .= ')'; 
    // echo 'Executing: ' . $sql2 . '</br>'; 
  } 
  mysql_query($sql2) or print(mysql_error() . '<br>'); 
  } 
fclose($fp);

Can anyone help me please to correct this ???
Thanks a lot !

 

[sleipnir214]

It would be of great help if the input dataset and the output dataset in your example were from the same lines of the file.

Given the input data you've specified, I'm surprised that it works at all. This line:

while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)

Tells PHP that the columns in the data are separated by the ';' character. This does not match your example data.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[pookie62]

Hi sleipnir214,
Thanks for your reply.
The values are seperated by the semicolon, you don't see them in the example because I opened the csv file in Excel and copied/pasted it from there. (Sorry..)

 

[pookie62]

Anyone a briljant idea ??
Please...

 

[kenrbnsn]

Please post the original input you used to get the output you posted.

 

[sleipnir214]

pookie62:

When I, in my earlier post, wrote

It would be of great help if the input dataset and the output dataset in your example were from the same lines of the file.

I was gently hinting that we needed to be able to compare lines from the input file to corresponding lines of output from your script. That was my mistake -- I shouldn't have hinted. Let me rephrase:



Insufficient data for a meaningful answer.

Unless you give us both of [ul][li]lines of input from the input file, as they exactly exist in the input file[/li][li]lines of output from your script that correspond to those verbatim input lines, exactly as they are output by your script[/li][/ul]It will be impossible to give you more than wild guesses as to what is wrong.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[pookie62]

Oops..sorry, It didn't seem that important to me that you needed the exact same data..
Well, here it is:

Lines in bold are from the csv file.
The data right under it, comes out of the table it's inserted to.
Table is called Score with this structure:
Id bigint(20)
InschrijvingId bigint(20)
SeieNr smallint(6)
Tijd decimal(10,2)
Punten smallint(6)

"Id";"InschrijvingId";"SerieNr";"Tijd";"Punten"
-1847839509;-2128794105;1;19,47;100
-1014054371;-2095218158;1;14,65;100

-1847839509 -2128794105 1 19.00 100
-1014054371 -2095218158 1 14.00 100

Hth..

 

[skiflyer]

I don't have a local instance of mysql anymore ... but does it understand 19,47 as 19.47? Especially given that you're quoting it on the way in?

I'd start with a simple

str_replace(',','.',$column);

in your loop.

 

[sleipnir214]

I think skiflyer has it right. I don't at my current location have a copy of MySQL available, but the MySQL documentation, when talking about the DECIMAL columntype, reads:

An unpacked fixed-point number. Behaves like a CHAR column; “unpacked” means the number is stored as a string, using one character for each digit of the value. M is the total number of digits and D is the number of digits after the decimal point. The decimal point and (for negative numbers) the ‘-’ sign are not counted in M, although space for them is reserved. If D is 0, values have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is omitted, the default is 0. If M is omitted, the default is 10.

It doesn't mention a "decimal character", but repeatedly a "decimal point". Being in the U.S., all my floating-point numbers use "." to denote decimals.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[pookie62]

Thanks guys, got it worked out..