0
$objReader = PHPExcel_IOFactory::createReader('CSV');

$objPHPExcel = $objReader->load($fileName);
$worksheet = $objPHPExcel->getActiveSheet();
$rowCount = 0;
foreach ($worksheet->getRowIterator() as $row) {
    if ($rowCount != 0){
        $sql_text = 'INSERT INTO test.data("A", "B", "C", "D", "E", "F") VALUES (';
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
        foreach ($cellIterator as $cell) {
            if (!is_null($cell)) {
                $sql_text.= "'" .pg_escape_string($cell->getValue()) ."',";
            }
        }
        $sql_text.=" '$Id";
        $query->setSql($sql_text);
        $results = json_decode($query->exec(true), true);
    }
    $rowCount++
}

Here the first column (with header A) is always missing leading zeroes. Column A may or may not have zeroes and the maximum number of characters it can take is 32.

miken32
  • 42,008
  • 16
  • 111
  • 154
phpNoobie
  • 1
  • 1
  • The problem is your column I would assume is set to 'integer' and 0005 is not an integer, it is a string. You would have to set the column to varchar. See this post http://dba.stackexchange.com/questions/15052/how-do-i-preserve-the-leading-zeros-when-i-insert-a-number-into-this-table – VIDesignz Dec 03 '15 at 23:51
  • 1
    In your database, how does column A look like? What type is it? if it's an integer then it will not save leading zeroes as there are no reason for them to be there. – Oliver Nybroe Dec 03 '15 at 23:54
  • 1
    Also, is there a reason for you to save the leading zeroes in the database and not just add them afterwards when you are outputting from the database? – Oliver Nybroe Dec 03 '15 at 23:56
  • In the database, Column A is varchar. – phpNoobie Dec 04 '15 at 00:09
  • Also, I need to add it as leading zeroes because the data is directly used by geolocation APIs – phpNoobie Dec 04 '15 at 00:12

1 Answers1

0

PHPExcel takes a similar approach to MS Excel itself.... if you enter a number in a cell, then that is treated as a number (integer or float). Even if you enter it with leading zeroes, they're not part of the number itself, so are ignored. You explicitly have to tell MS Excel to treat a numeric value with leading zeroes as a string rather than a number by quote-prefixing it (CSV has no formatting mechanism, so quote prefix can't be applied) or by applying a formatting mask that displays it with leading zeroes - though internally it's still a number - and again, CSV has no formatting, so this isn't an option.

PHPExcel emulates this Excel behaviour through a block of code called a "value binder" that tests each value as it's stored in a cell, and converts it to the appropriate datatype... this is where your leading zeroes are "stripped off" and the value is converted/cast to a standard PHP Integer or float. An alternative "advanced value binder" is also provided as an option for handling more complex values such as fractions like "2/4" and their conversion to 0.5 with an appropriate format mask on the cell to retain the display as a fraction.

This gives you two options.

Firstly, you could write your own Binder that tested the values for leading zeroes and treated them as strings instead of converting them to numbers, overriding the default behaviour, in a similar way to the way the advanced value binder handles fractions.

The alternative is to modify the code inside your foreach ($cellIterator as $cell) { loop to convert them back to a string with leading zeroes.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385