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 ?

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:
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

, 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..