Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel is stripping leading 0's from CSV files

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;
}
like image 951
DR1 Avatar asked Jul 07 '14 13:07

DR1


People also ask

How do you stop Excel from removing leading zeros from CSV?

Set the Text qualifier as either double or single quotes. Set the Column delimiter as a comma. Click OK. Click Save -- DO NOT OPEN THE CSV FILE DIRECTLY WITH EXCEL!

Why does Excel keep deleting my leading zeros?

Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them. This article deals with how to keep your data in its original format, which Excel treats as text.

Can you save a CSV file with leading zeros?

If you format the cells in Excel as (for example) 00000, and save as . csv, the leading zeros will be saved to the . csv file.


1 Answers

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'

From Text

Select Delimited

Next

Make sure comma is ticked

Comma separator

Select column as text

Select 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.

like image 53
bendataclear Avatar answered Sep 28 '22 02:09

bendataclear