Excel is stripping leading 0's from CSV files

Question

It believe that Excel is stripping leading 0s. I was told that updating the column format to text during the export will fix this and to modify Excel output (coming from "ProofAndTracking actionIpromoteuAutomation" file, yet I can't find this file or how to access where the excel formatting code is generated).

I've never worked with Excel. I tried this, but it seems to be a local fix: http://excelribbon.tips.net/T010262_Handling_Leading_Zeros_in_CSV_Files.html Could someone point me in the right direction to start?

I've researched these answers How to stop the leading 0's from being stripped off when exporting to excel from a datatable? and Export Excel : Avoid stripping the leading zeros
Thanks in advance!

Here is the function I believe is causing the problem, but not sure yet how to format columns to text here. /* * format and send order and tracking info (via Excel spreadsheet) for the day for ipromoteu (150837) */

public function actionIpromoteuAutomation() {
    $ordersGroup1 = $this->getIpromoteuProof();
    $ordersGroup2 = $this->getIpromoteuProofHistory();
    $orders = array_merge($ordersGroup1, $ordersGroup2);
    $fileName = 'Hit Promo Order Tracking ' . date('m-d-Y');
    $this->_export($orders, $fileName);
    $fileName = $fileName . '.xls';
    $toIPROMOTEU = array(//email to be sent to HR when request is submitted
        'body' => 'Attached, please find the Excel spreadsheet containing'
        . ' order details for orders on ' . date('m/d/Y') . '.',
        'from_email' => '[email protected]',
        'from_name' => 'Hit Promotional Products',
        'subject' => 'Order Tracking Information - ' . date('m/d/Y'),
        'to_emails' => '[email protected]',
        'attachments' => array(array('path' => Yii::app()->basePath . '/../tmp/' . $fileName, 'filename' => $fileName)),
    );
    Hit::email((object) $toIPROMOTEU);

    $this->logForDeveloper('Order records sent to iPromoteU for '. date('m/d/Y'));
}

private function _export($data, $fileName, $format = 'excel', $output = false) {
    // get a reference to the path of PHPExcel classes
    $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');

    // Turn off our yii library autoload
    spl_autoload_unregister(array('YiiBase', 'autoload'));
    include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    if ($format == 'excel') {
        // Set properties
        $objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)
                ->setLastModifiedBy(Yii::app()->user->name)
                ->setTitle('Order Tracking Report');
    }

    $styleArray = array(
        'font' => array(
            'bold' => true,
            'underline' => true,
        )
    );
    $objPHPExcel->setActiveSheetIndex(0);
    $sheet = $objPHPExcel->getActiveSheet();

    for ($rowCounter = 0; $rowCounter < sizeof($data); $rowCounter++) {
        $sheet->getStyle("A" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle("B" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle("C" . ($rowCounter + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $sheet->setCellValue("A" . ($rowCounter + 1), date('m/d/Y', strtotime($data[$rowCounter]['date'])));
        //$sheet->setCellValue("A".($rowCounter+1), $data[$rowCounter]['date']);
        if ($data[$rowCounter]['fedex_tracking'] != '')
            $sheet->setCellValue("B" . ($rowCounter + 1), $data[$rowCounter]['fedex_tracking']);
        else
            $sheet->setCellValue("B" . ($rowCounter + 1), $data[$rowCounter]['other_tracking']);
        $sheet->setCellValue("C" . ($rowCounter + 1), $data[$rowCounter]['sales_order_number']);
        $sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
    }

    $sheet->setCellValue("A1", "DATE")
            ->setCellValue("B1", "TRACKING")
            ->setCellValue("C1", "PO NUMBER")
            ->getStyle("A1:C1")->applyFromArray($styleArray);

    ////Set the column widths
    $sheet->getColumnDimension("A")->setWidth(25);
    $sheet->getColumnDimension("B")->setWidth(25);
    $sheet->getColumnDimension("C")->setWidth(25);
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('Order Tracking Report');

    // Set active sheet index to the first sheet,
    // so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);

    $fileName = $fileName . ($format == 'excel' ? '.xls' : '.csv');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $path = Yii::app()->basePath . '/../tmp/' . $fileName;
    $objWriter->save($path);

    // Once we have finished using the library, give back the
    // power to Yii...
    spl_autoload_register(array('YiiBase', 'autoload'));
}

public function getIpromoteuProof() {
    $date = date('Ymd');
    $db2params = Yii::app()->params['db2params'];
    $db = Zend_Db::factory('Db2', $db2params);
    $select = $db->select()
            ->from(
                    // table
                    array('t' => 'WBPIPRAE'),
                    // columns
                    array(
                'sales_order_number' => 'TRIM(WAESPO#)',
                    ),
                    // schema
                    $db2params['schemas']['hitdta'])
            ->joinLeft(
                    // table
                    array('f' => 'MFD1MD'), 'CONCAT(TRIM(WAEORD#),RIGHT(TRIM(MDORDR),3))=TRIM(MDORDR)',
                    // columns
                    array(
                'fedex_tracking' => 'TRIM(MDFTRK)',
                'other_tracking' => 'TRIM(MDFBRC)',
                'date' => 'TRIM(MDUPDT)',
                    ),
                    // schema
                    $db2params['schemas']['varfil'])
            ->where("MDUPDT = '" . $date . "'")
            ->where("WAEIVKY='150837'")
            ->order('sales_order_number')
            ->distinct(true);

    $stmt = $db->query($select);
    $orders = $stmt->fetchAll();
    return $orders;
}

Answer 1

OK, Excel can create the csv columns as text but the issue is with the import.

When Excel opens csv files it 'helpfully' converts anything that looks like a number to a number, stripping leading zero's.

First make sure Excel is exporting it as it should, open the file in a text editor and check the leading zero is present.

You will probably have to change how the file is imported. Instead of them just double clicking the csv file, they need to run through the data import wizard:

Import 'From Text'

Select Delimited

Make sure comma is ticked

Select column as text

This should resolve the problem on the other side.

Another alternative is to add a single apostrophe ' before the numbers, however if they use macros/software to interact with the file this might break that.

Edit

After looking at the PHP code (I can't test) you might be able to change the line:

$sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

To:

$sheet->getStyle("B" . ($rowCounter + 1))->getNumberFormat()->setFormatCode('000000000000'); // With however many zero's you need to keep

This however may still get removed when you export as it's still technically a number and formatting will be removed.