0

I import an "xlsx" file with php into a MySQL database.

In Excel the number looks like this (German number format): 2,564.30 or -2,564.30

I convert this to this string into a number in order to be able to save this in the MySQL as a number with the following:

floatval (preg_replace("/[-]?[^-0-9\.]/", "", $row[16]))

The number is imported but not including the minus. I only get positive numbers.

The output is always

2564.30

Unfortunately always without a minus!

I am not familiar with regex.

Update

Sorry, but none of the variants work.

This ist the Number in Excel: 1

And this in mysql: 2

In the mysql database formated as: float(12,2) Here the Code from the import.php:

<?php

include '../assets/inc/db.php';
include '../assets/inc/db2.php';
include '../assets/inc/SimpleXLSX.php';

require '../vendor/autoload.php';

if(isset($_GET['bvh'])){
  $bvh = $_GET['bvh'];
}else{
  echo "Kein BVH ausgewählt";
}

$inputFileType = 'Xlsx';
$inputFileName = './importe/as4u_export.xlsx';
$sheetname = '0001';

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only
        if ($row >= 5 && $row <= 65536) {
            if (in_array($column,range('A','Q'))) {
                return true;
            }
        }
        return false;
    }
}

/**  Create an Instance of our Read Filter  **/
$filterSubset = new MyReadFilter();

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Tell the Reader that we want to use the Read Filter  **/
$reader->setReadFilter($filterSubset);
/**  Load only the rows and columns that match our filter to Spreadsheet  **/
$spreadsheet = $reader->load($inputFileName);

$data = $spreadsheet->getActivesheet()->toArray();

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

foreach($data as $row){
  $insert_data = array(
    ':as4u_KOA'       =>  $row[5],
    ':as4u_KOA_name'  =>  $row[8],
    ':as4u_text'      =>  $row[14],
    ':as4u_betrag'    =>  floatval(preg_replace('~^-\K|[^-\d.]~', '', $row[16])),
  );

  $query = "INSERT INTO ww_as4u (as4u_bvh_id, as4u_KOA, as4u_KOA_name, as4u_text, as4u_betrag) VALUES ('$bvh', :as4u_KOA, :as4u_KOA_name, :as4u_text, :as4u_betrag)";
  $statement = $conn->prepare($query);
  $statement->execute($insert_data);
}
halfer
  • 18,701
  • 13
  • 79
  • 158
Johann Danner
  • 1
  • 1
    Possibly relevant: [Unformat money when parsing in PHP](https://stackoverflow.com/questions/5139793/unformat-money-when-parsing-in-php) – El_Vanja Apr 23 '21 at 10:21
  • What else have you tried to resolve the problem? This looks like a great moment to learn some TDD: gather test cases, write unit tests to build a "parser" – Nico Haase Apr 23 '21 at 13:43
  • My answer at least didn't work because you've given the wrong number format - the one you've given in your original post was "2,564.30" (English number format), the one shown in your screenshot (2.817,42) is in German number format. I've updated [my answer](https://stackoverflow.com/a/67228982/3323348) so it deals with the latter. Please edit your post again so it shows the number format you actually use in your Excel file, so that those who've provided an answer have a chance to adapt their solutions. – nosurs Apr 23 '21 at 13:54

3 Answers3

1

As an alternative to using regular expressions, you could also use PHP's NumberFormatter class.

$formatter = new NumberFormatter("de-DE", NumberFormatter::DECIMAL);
$formatter->parse("2.564,30");  // Gives 2564.3
$formatter->parse("-2.564,30"); // Gives -2564.3

Since NumberFormatter is part of PHP's Intl (= Internationalization) extension, it might or might not be already installed/enabled, depending on your PHP version.

In case it's not readily available, see:

For NumberFormat itself, see:

nosurs
  • 670
  • 4
  • 12
0

You can use

preg_replace('~^-\K|[^-\d.]~', '', $row[16])

See the regex demo.

Details

  • ^-\K - matches a - at the start of string and omits the match
  • | - or
  • [^-\d.] - any char other than -, `digits and dots.

See a PHP demo:

$strs = [' 2,564.30', '-2,564.30'];
print_r(preg_replace('~^-\K|[^-\d.]~', '', $strs));
// => Array( [0] => 2564.30    [1] => -2564.30 )
Wiktor Stribiżew
  • 484,719
  • 26
  • 302
  • 397
0

try this: "/[^-0-9.]/" for the regex

marac990
  • 102
  • 4