Page 1 of 1

No decimal values on csv import..help ?

Posted: Tue Aug 02, 2005 2:58 pm
by pookie62
Hi,
I import a csv file into table, but all values (two columns with decimal values) are rounded to whole numbers.. 8O
Is there a mistake in the script or what ?
Thanks for checking..

Code: Select all

$fp = fopen('Klassement.csv', 'r');
 
// first line has column names
$data = fgetcsv($fp, 2048, ';');
$columns = array();
foreach($data as $column)
{
  $columns[] = trim($column, '"');
}

mysql_query('DELETE FROM testklassement');
 
$sql = 'INSERT INTO testklassement (';
$sql .= implode($columns, ', ');
$sql .= ') VALUES (';
 
// next lines have values
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
{
  $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);
?>

Posted: Tue Aug 02, 2005 3:50 pm
by JAM
Try echo'ing out what you insert first. ( the $sql )
If that doesn't help you find the problem, check that you use the correct type of field in the database (ie. DECIMAL(M,N) or eq).

Posted: Tue Aug 02, 2005 7:33 pm
by hansteam
Your problem probably lies in your database type. It sounds like you're trying to insert a decimal value into an int (or similar). Make sure that your database field is FLOAT or similar.

Posted: Wed Aug 03, 2005 2:06 am
by pookie62
Thanks for your support guys, but no luck..
I even removed the fields in table, added new fields (decimal) but still no decimal values.

Layout table:

KlasseId bigint(20) Ja NULL
LevelId char(1) Ja NULL
DeelnemerId bigint(20) Ja NULL
Tot_percentage decimal(10,0) Ja NULL
Gem_percentage decimal(10,0) Ja NULL

Echo from query:
Executing: INSERT INTO testklassement (KlasseId, LevelId, DeelnemerId, Tot_percentage, Gem_percentage) VALUES ('1', 'A', '2', '4,04', '0,80')

First line csv are field names
"KlasseId";"LevelId";"DeelnemerId";"Tot_percentage";"Gem_percentage"
1;"A";2;4,04;0,80
1;"A";3;4,97;0,82

Anybody idea's ??

Posted: Wed Aug 03, 2005 2:13 am
by feyd
decimal(10,0) is 10 places, no decimals.

Posted: Wed Aug 03, 2005 2:24 am
by s.dot
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]


For MySQL 5.0.3 and above:

A packed “exact” fixed-point number. M is the total number of digits and D is the number of decimals. The decimal point and (for negative numbers) the '-' sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 64. The maximum number of supported decimals (D) is 30. If UNSIGNED is specified, negative values are disallowed.

If D is omitted, the default is 0. If M is omitted, the default is 10.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 64 decimal digits.
you would need to have your 0 set to the number of decimal places you wish to have.

"If D is 0, values have no decimal point or fractional part. "

Posted: Wed Aug 03, 2005 2:28 am
by pookie62
oke, changed the field values to 3,3
now the data in the table looks like this:
1 A 2 4.000 0.000
1 A 3 4.000 0.000
Can it be that the comma's in the csv values are not proper read by the script?
Must be something with this I think, now how to solve this..
Getting bold places on the head.... ;-)