Format date and monetary value to save to database

Question

I’m extracting data from a file .xlsx. converting them into .csv and showing it on screen. So far so good, however, I want to play this data in a database, using PHP, but for this I need to convert the data, because the way they are, they will not enter the database, see:

10-01-15 SAI 87,059.56 UVA SAFRA 2015 - APB

I need to format the date 10-01-15 and the value 87,059.56, the rest of the data is quiet. get out - UVA SAFRA 2015 - APB

Would I have to play so even on the bench, and then catch them, convert the date and value and play on another table?

Follows the code:

if (isset($_POST['pega'])) {

include_once("PHPExcel/Classes/PHPExcel.php");

$uploadDir = "uploadFile/";

$uploadfile = $uploadDir . $_FILES['arquivo']['name'];

if(move_uploaded_file($_FILES['arquivo']['tmp_name'], $uploadfile)) {
    echo "Arquivo pego com sucesso";
    echo "<br><br>";
}else{
    echo "Não foi possível pegar arquivo";
    echo "<br><br>";
}

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($uploadfile);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$csvFileName = str_replace('.xlsx', '.csv', $uploadfile);
$objWriter->save($csvFileName);
if (($handle = fopen($csvFileName, "r")) !== false) {
    while (($data = fgetcsv($handle, 1000, ",")) !== false) {
        $num = count($data);
        for ($c = 0; $c < $num; $c++) {
            echo $data[$c]." ";
        }
        echo "<br />\n";
    }
    fclose($handle);
}
}

I was able to separate the dice, but I can’t play for the bench, I’m using this:

        $pdo = conectar();
        $insereDados=$pdo->prepare("INSERT INTO dadosImportados (data, tipo, valor, descricao) VALUES (?,?,?,?)");
        $insereDados->bindValue(0, $data[0]); 
        $insereDados->bindValue(1, $data[1]); 
        $insereDados->bindValue(2, $data[2]); 
        $insereDados->bindValue(3, $data[3]); 

        $insereDados->execute();

But this message appears on the screen: "Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[HY093]: Invalid Parameter number: Columns/Parameters are 1-based' in /Applications/MAMP/htdocs/systems/scripts_da_web/php/imports/importFile.php:68 Stack trace: #0 /Applications/MAMP/htdocs/systems/scripts_da_web/php/imports/importArchive.php(68): Pdostatement->bindValue(0, '10-01-15') #1 {main} thrown in /Applications/MAMP/htdocs/systems/scripts_da_web/php/imports/importFile.php on line 68"

Answer 1

Whereas the original date is in the dd-mm-yy format (2-digit day, month and year), the year of which is within the 21st century (2001~2100) and, also considering that it is using Mysql database, the string should be format in the standard ISO 8601

$str = '10-01-15';
$arr = explode('-', $str);
$arr = array_reverse($arr);
echo '20'.implode('-', $arr); // Resulta em 2015-01-10

If the column type in the database is datetime, add time.

Since there is no time specification in the code you posted in the original question, you can add time with zero.

echo '20'.implode('-', $arr).' 00:00:00'; // Resultará em 2015-01-10 00:00:00

For the currency, it depends on how the column is defined in the database. The recommendation is the type decimal(14,6), whether or not working with coins of full value and regardless of the visual format have decimal values of 2 digits.

$str = '87,059.56';
echo str_replace(',', '', $str); // Resulta em 87059.56

If you set the database column to decimal(14,6), the value will be saved as 87059.560000.

When reading this column, to present with 2 houses and formatted with comma, in the visual pattern of Brazil, use the function number_format():

echo number_format('87059.560000', 2, ',', '.'); // Resulta em 87.059,56

Note that I did not adapt the answer to the code posted in the question as this is your work.

Here you have the information needed to proceed and implement for your case or use for other miscellaneous cases.

Answer 2

Your question was:

Would I have to play like that on the bench, and then catch them, convert the date and value and play in another table?

Transform your data the way you want and after that you should throw it in your bank. You must not pass that responsibility on to the bank, because it already has other responsibilities. Treat the dice and play to your bank only once, without going and coming from table. Treat the dice before, and then save them.