Changing decimal values ?

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

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

Changing decimal values ?

Post by pookie62 »

Hi,
I'm importing a csv file into Mysql.
Problem is that the csv has values like this: 4,45647 (Note the comma)
I need to change the comma to dot to import this correctly into Mysql.
Anyone knows how to ?

Script I use:

Code: Select all

<?php
  mysql_select_db($database_ASN, $ASN);
  
$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
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

try a str_replace after you get the data from the csv

Code: Select all

$data2 = str_replace(",",".",$original_data);
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi scrotaye,
Thanks for your reply, sorry for being newbie, but where in the script would I place this line ? :oops:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you'll have to be sensitive of where you run it as you may trample on a non numeric one.. this may help:

Code: Select all

$columns[] = 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, '"'));
that should be replaces this line:

Code: Select all

$columns[] = trim($column, '"');
note: that regex is untested, although I used a very similar one for a test yesterday that worked flawlessly...
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Thanks Feyd,
gonna try it in an hour.. I'll let you know the result.
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi again,
Sorry, but the data in the table is still rounded in whole numbers (there is a dot now instead of comma, so the job is halfway..)
Any suggestions ?
Script :

Code: Select all

<?php 
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN); 
if (!file_exists('Klassement.csv')) 
{ 
die ("Er is geen Klassement bestand aangetroffen, voer eerst Competitie bijwerken uit vanaf het ASN Beheer menu !"); 
}   
   
$fp = fopen('Klassement.csv', 'r'); 

// first line has column names 
$data = fgetcsv($fp, 2048, ';'); 
$columns = array(); 
foreach($data as $column) 
{ 
$columns[] = 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, '"')); 
} 

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
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

ok so wait you need to round the number to a certain decimal, is that what you are saying?

if so just use round();
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

shiznatix, you're right
I would like it to display 3 decimals
How do I say that in the script ?
Thanks...
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

plzzzz anyone ?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

round($yourdata,2); will round to two decimal places
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi scrotaye,
I'm sorry for being such a newbie :oops: , but I'm not sure where and how in the query I must write this..
Could you show me ?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I believe it would be like this

Code: Select all

foreach($data as $column) 
  { 
    $column = round(mysql_real_escape_string($column),2); 
    $sql2 .= "'{$column}', "; 
  }
Although I can't be quite sure because I've never dealt with .CSV files, and I don't know exactly which variable your decimal numbers are being stored in.

Whatever variable your numbers are in, you want to round($var,2);
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Oke this is script so far:

Code: Select all

<?php 
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN); 
if (!file_exists('Klassement.csv')) 
{ 
die ("Er is geen Klassement bestand aangetroffen, voer eerst Competitie bijwerken uit vanaf het ASN Beheer menu !"); 
}   
   
$fp = fopen('Klassement.csv', 'r'); 

// first line has column names 
$data = fgetcsv($fp, 2048, ';'); 
$columns = array(); 
foreach($data as $column) 
{ 
$columns[] = 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, '"')); 
} 

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)  <= last change
  foreach($data as $column) 
  { 
    $column = round(mysql_real_escape_string($column),2); 
    $sql2 .= "'{$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); 
?>
Giving this output now:
Executing: INSERT INTO testklassement (KlasseId, LevelId, DeelnemerId, Tot_percentage, Gem_percentage) VALUES ('1', '0', '2', '4', '0', '0')
Column count doesn't match value count at row 1

While the values in the csv are:
"KlasseId";"LevelId";"DeelnemerId";"Tot_percentage";"Gem_percentage"
1;"A";2;4,04;0,80
1;"A";3;4,97;0,82
1;"A";9;5,14;0,85

etc..
The table can be emptied before inserting the new data.(probably easier..)

Thanks for supporting!!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you're passing 6 fields, but only defining 5 that will be passed in your query.
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Feyd,
That's because the values aren't interpreted correctly.. how must I do this ?
Importing the values as they are in the csv file, need to be imported in the Mysql table..
I'm lost... driving me nuts..
Post Reply