php – Format date and monetary value to save to database

Question:

I'm extracting data from an .xlsx file. converting them to .csv and displaying them on the screen. So far so good, however, I want to throw this data into a database, using PHP, but for that I need to convert the data, because as it is, it won't 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 87,059.56 . leaves – UVA SAFRA 2015 - APB

Would I have to play in the bank anyway, and then take them, convert the date and value and play in another table?

Here's 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 managed to separate the data, but I can't play to the bank, 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/sistemas/scripts_da_web/php/ import/importaFile.php:68 Stack trace: #0 /Applications/MAMP/htdocs/sistemas/web_scripts/php/importa/importaFile.php(68): PDOStatement->bindValue(0, '10-01-15') # 1 {main} thrown in /Applications/MAMP/htdocs/sistemas/web_scripts/php/importa/importaArquivo.php on line 68"

Answer:

His question was:

Would I have to play in the bank anyway, and then take them, convert the date and value and play in another table?

Transform your data the way you want and after that you should play them in your bank. You should not pass this responsibility on to the bank, as it already has other responsibilities. Treat the data and play for your bank only once, without the back and forth of the table. Treat the data first, and then save it.

Scroll to Top