Page 1 of 2

Changing decimal values ?

Posted: Thu Aug 04, 2005 4:01 am
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);
?>

Posted: Thu Aug 04, 2005 4:40 am
by s.dot
try a str_replace after you get the data from the csv

Code: Select all

$data2 = str_replace(",",".",$original_data);

Posted: Thu Aug 04, 2005 6:57 am
by pookie62
Hi scrotaye,
Thanks for your reply, sorry for being newbie, but where in the script would I place this line ? :oops:

Posted: Thu Aug 04, 2005 7:30 am
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...

Posted: Thu Aug 04, 2005 8:17 am
by pookie62
Thanks Feyd,
gonna try it in an hour.. I'll let you know the result.

Posted: Thu Aug 04, 2005 10:12 am
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); 
?>

Posted: Thu Aug 04, 2005 10:33 am
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();

Posted: Thu Aug 04, 2005 10:49 am
by pookie62
shiznatix, you're right
I would like it to display 3 decimals
How do I say that in the script ?
Thanks...

Posted: Fri Aug 05, 2005 12:55 am
by pookie62
plzzzz anyone ?

Posted: Fri Aug 05, 2005 1:18 am
by s.dot
round($yourdata,2); will round to two decimal places

Posted: Fri Aug 05, 2005 1:35 am
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 ?

Posted: Fri Aug 05, 2005 1:37 am
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);

Posted: Fri Aug 05, 2005 2:03 am
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!!

Posted: Fri Aug 05, 2005 8:32 am
by feyd
you're passing 6 fields, but only defining 5 that will be passed in your query.

Posted: Fri Aug 05, 2005 9:39 am
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..