fputcsv display leading zeros

Question

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?

How do you open your XLS file ? I mean XLS editor have automatic format functions, so be sure that's not your editor which removes the `0` of your data. — jbrtrnd, Jun 18 '12 at 07:35

score 28 · Answer 1 · edited Jun 04 '18 at 15:36

28

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.

edited Jun 04 '18 at 15:36

Wahyu Kristianto

8,719
6
43
68

answered Feb 16 '16 at 19:15

Ray

773
9
21

simple solution saved my day. – The developer Nov 28 '17 at 10:25
1

@bhawani using single or double quotes? – Ray Jan 24 '18 at 01:12

score 12 · Answer 2 · answered Feb 29 '20 at 15:14

12

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'] . "\""]);
}

answered Feb 29 '20 at 15:14

SimonDepelchin

2,013
22
18

1

This should be selected as answered question – user256968 Jun 01 '20 at 17:23

Salman A · Accepted Answer · 2012-06-18T07:56:45.457

5

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

edited Jun 18 '12 at 07:56

answered Jun 18 '12 at 07:46

Salman A

262,204
82
430
521

In case you are exporting a CSV file which is used in another import process by end users, we need to warn then. Then again an oversight causes other consequences. I'm still hopeful to find a way to go around this forced Microsoft Excel feature! – hpaknia Dec 08 '17 at 21:52

score 3 · Answer 4 · answered Jun 18 '12 at 07:37

3

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.

answered Jun 18 '12 at 07:37

Sudhir Bastakoti

99,167
15
158
162

1

i've tryed but alwais same problem! – jack.cap.rooney Jun 18 '12 at 13:31

fputcsv display leading zeros

4 Answers4

Linked