Page 1 of 2

I need those decimals!! Please help..

Posted: Tue Sep 06, 2005 3:08 pm
by 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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

<?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 !

Posted: Tue Sep 06, 2005 4:45 pm
by pickle
The first problem I found is that your decimal point is a comma. That's going to cause lots of problems when you've got a comma delimited file. Either make it tab (or some other character) delimited, or change your decimal point. I'd bet that'll fix your problem.

Posted: Tue Sep 06, 2005 4:57 pm
by s.dot
I believe his file is semicolon delimited

Code: Select all

while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
To add my two cents in, having those queries inside of the while loop, along with foreach()'s inside of the while loop is making your code way slower than it should be.

You could always store all of those values into arrays, and run your queries after the loops.

I believe that would make your code more readable too, making debugging that much easier.

Posted: Tue Sep 06, 2005 5:04 pm
by pickle
1) DOH!!!

2) Moving on... output $sql2 before it's executed to see what the query is.

Posted: Wed Sep 07, 2005 1:08 am
by pookie62
Hi there..
Thanks for your replies, first let me correct something..
The example shows no seperator, which is the semicolon. You don't see it in the sample because I copy/pasted this after opening the csv file in Excel (sorry...)

scrotaye > how should the code look in your opinion ?

In another script this code did the trick:

Code: Select all

foreach($data as $column) 
  { 
    $column= preg_replace('#^((?:(?:(?:[0-9]{1,3}\.(?:[0-9]{3}\.)*)(?:[0-9]{3}))|0|[0-9]*))(,[0-9]+)?$#e','str_replace(\'.\',\'\',\'\\1\').str_replace(\',\',\'.\',\'\\2\')',trim($column, '"')); 
    $sql2 .= "'{$column}', "; 
  }
I don't know how to insert this in the script.
Someone of you guys ??


Thanks again !

Posted: Thu Sep 08, 2005 4:16 am
by pookie62
Anyone can help me ??

Posted: Thu Sep 08, 2005 4:58 am
by timvw
What is the type of the column that needs to have the floating numbers?

Posted: Thu Sep 08, 2005 5:39 am
by pookie62
Hi Timvw,

Id bigint(20)
InschrijvingId bigint(20)
SerieNr smallint(6)
Tijd decimal(10,2)
Punten smallint(6)

Posted: Fri Sep 09, 2005 3:30 pm
by pookie62
So what's next ??

Posted: Fri Sep 09, 2005 3:34 pm
by feyd
I would bet MySQL isn't returning those fields as strings. They are too large for PHP to handle them in a different form (it appears..)

Posted: Sat Sep 10, 2005 3:47 am
by pookie62
Hi Feyd,

Could be, I don't have a great knowledge of php.. :oops:
What is the solution then for importing these values ?

When I can import a value with two decimals, I have what I want..
Can it be done ? And if so, how ??

Thanks for your time and support, I depend on it...

Posted: Sat Sep 10, 2005 7:25 am
by feyd
the decimals aren't the problem simply converting the comma to a dot should get them working. I was talking about the two ID fields getting mangled.

Posted: Sat Sep 10, 2005 9:37 am
by pookie62
The conversion can only take place with the PHP script, just before inserting I think.
How should I do that in the script I use

Can you please show me.. ?
Thanks

Posted: Sat Sep 10, 2005 9:42 am
by feyd

Posted: Sat Sep 10, 2005 12:43 pm
by pookie62
This is how I did it, seems to work..
Does it look oke ??

Thanks !