Format date and monetary value to save to the database

Question

Format date and monetary value to save to the database

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I'm extracting data from a .xlsx file. converting them to .csv and displaying on the 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 get into 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. leaves - UVA SAFRA 2015 - APB

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

Follow 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 data, but I can not 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/importa/importaFile.php:68 Stack trace: # 0 /Applications/MAMP/htdocs/sistemas/scripts_da_web/php/importa/importa.filename.php (68): PDOStatement-> bindValue (0, '10 -01-15 ') # 1 {main} thrown in /Applications/MAMP/htdocs/sistemas/scripts_da_web/php/importa/importaArquivo.php on line 68 "

php csv

asked by anonymous 15.12.2015 / 21:49

2 answers

How to add the logo in the fixed menu after a certain scrolling on the page? How to configure routes when the application is not in the asp.net mvc server root?

score 1 · Answer 1

Your question was:

I would have to play anyway on the bench, and then pick them up, convert date and value and play in another table?

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

score 1 · Answer 2

Since the original date is in dd-mm-yy format (day, month and year with 2 digits), whose year is 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 the time.

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

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

For the currency, it depends how the column in the database is defined. The recommendation is type decimal(14,6) , even if it works with integer currencies and regardless of the visual format, display decimal values with 2 boxes.

$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 display with 2 boxes and formatted with a comma, in the visual pattern of Brazil, use number_format () :

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

Note that I have not adapted the answer to the code posted in the question since this is your job.

Here you have the information you need to go ahead and implement for your case or use for other miscellaneous cases.