CODEWITHSUNDEEP
phpcsv
jack.cap.rooney
jack.cap.rooney

Reputation: 1306

fputcsv display leading zeros

I'm using a PHP script to generate an excel CSV file from a result-set query. All works fine but when I read my excel file, I can not display leading zeros.

This is my code:

$rows = $this->Query($sql);

$filename = "/www/zendsvr/htdocs/Project/public/report.xls";
$realPath = realpath( $filename );

$filename = realpath( $filename );
$handle = fopen( $filename, "w" );
$finalData = array();

for( $i = 0; $i < count( $rows ); $i++ ) {
    $finalData[] = array( utf8_decode( $rows[$i]->CODE ) );
}

foreach ( $finalData AS $finalRow ) {
    fputcsv( $handle, $finalRow, "\t" );
}

fclose( $handle );

If I make a var_dump() of $finalData[] I see the correct value, for example '000198', '000199', '000200' but the same value in my XLS file is 198,199,200

How can I also display leading zeros in the XLS file?

Upvotes: 14

Views: 14431

Answers (4)

SimonDepelchin
SimonDepelchin

Reputation: 2080

I was surprised after so many years no-one came with a working solution.

Here's what worked for me:

$records = [
    ['name' => 'John', 'phone' => '01234567'],
    ['name' => 'Jane', 'phone' => '01234569'],
];

$file = fopen('php://output', 'w');

fputcsv($file, ['Name', 'Phone N°']);

foreach ($records as $record) {
    fputcsv($file, [$record['name'], "=\"" . $record['phone'] . "\""]);
}

Upvotes: 12

Ray
Ray

Reputation: 793

To add to DemoUser's approach:

The \t approach worked for me however with a slight change

$column_data = "\t000543";

Adding the \t solved my problem of having the leading zero's disappear, even though the CSV was correct and Excel was 'wrong' the \t worked in keeping the leading zeros intact when displaying in excel.

Upvotes: 28

Salman Arshad
Salman Arshad

Reputation: 272096

Your data is saved correctly, it is Excel who is trying to be smart.

Use the Excel import data wizard instead of opening the file directly (rename the .csv file to .txt if necessary). On the import wizard, choose the data type of the column as "text" instead of "general":

Text import wizard step 3

Upvotes: 5

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Try casting it to string like:

fputcsv($fp, (string) $val);

OR, Opening XLS file will truncate leading 0 so try adding /t before zero to avoid 0 truncation while creating your row values.

Upvotes: 3

Related Questions