Glenn Glenn - 3 months ago 8
MySQL Question

Import CSV to MYSQL with a currancy formatted field

I've searched and searched but found virtually nothing. I have a CSV file I am inserting into a mysql database via a php script. Tried many diffrent thing yet can't get it working correctly. The CSV field is sent to us with a column formatted as currancy, tried appending it to a mysql table with VARCHAR, INT, Decimal (10,2), Float, pretty much just went down the list. It loads into the table and show correctly in VARCHAR after using php substr($val,1) to get rid of the '$', but getting it to calculate with number_format($sum, 2,'.',','); comes out completely wrong, like $1,200.00 when it should be $3,456,789.00.

If I manually change the format of the CSV column to TEXT before importing it works fine, but this has to be automated.

Any suggestions on importing a CSV formated as currancy into mysql for calculations? Is there a way to change CSV format when importing using php?

$file = "ftp:1.1.1.1/myfile.csv";
if(($handle = fopen($file, "r")) !== FALSE)
{ fgetcsv($handle);
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$num = count($data);
for ($c=0; $c < $num; $c++) {
$col[$c] = $data[$c];
}
$col13a = $col[27];
$col13 = substr($col13a, 1);
$col13 = substr($col13a, 1);
$query = "INSERT IGNORE INTO allinv (cost) VALUES ('".$col13."')";


$s= mysqli_query($dbConnected, $query);
}

fclose($handle);
}////upload script

////calculation & display script:
$sum=0;

while($row = fetch_array($result)){
$date2= strtotime($row['DateIn']);
$day = ($date-$date2)/86400;
$value = $row['Cost'];
$sum += $value;

echo "<tr>";
echo "<td>".$row['VIN']."</td>";
echo "<td>".$row['Make']."</td>" ;
echo "<td>".$row['Model']."</td>";
echo "<td>".$row['Year']."</td>";
echo "<td>".$row['Color']."</td>";
echo "<td>".number_format($day)."</td>";
echo "<td>".$row['Location']."</select></td>";
echo "<td>".$row['Locationoutside']."</td>";
echo "<td>".'$'.$row['Cost']."</td>";


}"</table>";


echo "<table><tr>
<th>Total Vehicles</th>
<th>Sum of Cost</th></tr>";
$english_format_number = number_format($sum, 2, '.', ',');
$count= mysqli_num_rows($result);
echo "<td>".$count."</td>";
echo "<td> $ ".$english_format_number."</td>";
"</table>";
}

Answer

So I figured it out, didn't have to use any other program. Instead of:

 ////calculation & display script:
  $sum=0;

while($row = fetch_array($result)){
    $date2= strtotime($row['DateIn']);
    $day = ($date-$date2)/86400;
    $value = $row['Cost'];
    $sum += $value;

I changed it to this:

     $sum = 0;

    while($row = fetch_array($result)){
        $date2= strtotime($row['DateIn']);
        $day = ($date-$date2)/86400;
        $value = $row['Cost'];
        $calc = floatval(str_replace('', '', str_replace(',', '', $value)));
        $sum += $calc;

It was only adding the first two numbers due to it being VARCHAR, the comma was stopping it and moving it to the next row. Removed comma, added floatvar and it works great now.