No decimal values on csv import..help ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

No decimal values on csv import..help ?

Post 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);
?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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).
hansteam
Forum Newbie
Posts: 11
Joined: Tue Aug 02, 2005 2:52 pm
Location: Minnesota

Post 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.
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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 ??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

decimal(10,0) is 10 places, no decimals.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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. "
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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.... ;-)
Post Reply