Create CSV script in truncate 0 in values issue in PHP

Question

I have used this one script but some 01523 values in CSV in write 1523.

$file = fopen('demosaved.csv', 'w');

// save the column headers
fputcsv($file, array('Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'));

// Sample data. This can be fetched from mysql too
$data = array(
array(01523, 'Data 12', 'Data 13', 'Data 14', 'Data 15')
);

// save each row of the data
foreach ($data as $row)
{
fputcsv($file, $row);
}

// Close the file
fclose($file);

In csv output like 1523 I want 01523.

Pease I don't want to use '/n', '/t' , and '01523' in value. Also How can I set all header and values in the double quote? like "column 1","column 2"...

Wrap it in quotes/convert it to string. Also, 01523 is an octal number in php, so I'm surprised you obtain 1523. — Federico klez Culloca, Mar 21 '18 at 16:34
And if it _is_ enclosed in quotes, you _will_ get 01523 in your CSV. It's just Excel that's dropping the leading zero. Look at it in a text editor. — Don't Panic, Mar 21 '18 at 16:38
yes. when I am open first time in text editor 0 is displayed but when i am checking in excel 0 not display. — Magento2 Devloper, Mar 21 '18 at 16:41
Some interesting informations : https://stackoverflow.com/questions/2489553/forcing-fputcsv-to-use-enclosure-for-all-fields — Syscall, Mar 21 '18 at 16:41
And how exactly is Excel related to PHP? Excel just "fixes" your data, but that is obviously not PHPs fault... — Honk der Hase, Mar 21 '18 at 16:43
It's not php fault but I think some script or code do like that. that's why I am asking about it. — Magento2 Devloper, Mar 21 '18 at 16:45

Don't Panic · Answer 1 · 2018-03-21T17:36:07.270

1

After you run your script (assuming 01523 in your array is actually the string "01523"), the contents of demosaved.csv will be:

"Column 1","Column 2","Column 3","Column 4","Column 5"
01523,"Data 12","Data 13","Data 14","Data 15"

If you open it in a text editor, you will see the leading zero, because it is there in the file. If you open it in Excel, you will not see the leading zero, even though it is there in the file, because that's how Excel displays numbers with leading zeroes.

Even if you edit your demosaved.csv file in your text editor and put quotes around the number with the leading zero, so it's "01523","Data 12"... instead of 01523,"Data 12",..., Excel will still not display the leading zero. The only way to force Excel to display the leading zero in a number in a CSV file is to use one of the tricks you want to avoid.

If the intended use of your output file is to be opened in Excel, you can create an Excel document instead of a CSV file. Here's a quick example with PhpSpreadsheet:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray($data, null, 'A1');
$writer = new Xlsx($spreadsheet);
$writer->save('example.xlsx');

The leading zero will show up in example.xlsx. But this file format cannot be used to pass your data to another application that expects a CSV. If that is the intended use of your output file, the way you're already doing it is fine.

However, if you do open the CSV in Excel, edit it (or not), and save the changes, the leading zero will be gone.

edited Mar 21 '18 at 17:36

answered Mar 21 '18 at 17:20

Don't Panic

41,125
10
61
80

Thank you for your great answer but application in only CSV file need. – Magento2 Devloper Mar 21 '18 at 17:24
Then, as I said, they way you're doing it is fine. The leading zero _is there_ in the CSV file. You just can't see it in Excel. – Don't Panic Mar 21 '18 at 17:25
you are right but what happens client does not understand, he just open CSV file and checks.He wants 0 in csv. – Magento2 Devloper Mar 21 '18 at 17:34
I suppose you just have to explain it to them beforehand. As far as I know, there's no way to force Excel to automatically display the leading zero without adding something else to the value that will probably affect its usefulness in the application that uses the CSV. – Don't Panic Mar 21 '18 at 17:39
If you open it in Libre Office there is an option to treat the column as text in the opening dialog window. This might be an option if you can't do what Don't Panic suggests. – Pete Mar 22 '18 at 14:55
@Pete actually, Excel offers the same option if you open Excel first and use the import wizard, (or Data->From Text/CSV in newer versions) but I guess the concern is that it may be opened by someone who doesn't know you need to do that. I wish Excel would just default to opening CSVs that way, but unfortunately not. – Don't Panic Mar 22 '18 at 15:02
@Don'tPanic You sound like you've experienced some of the same pain that I have. Don't even start me on character-encoding and trying to get clients to avoid non-breaking spaces. – Pete Mar 22 '18 at 15:58

score 0 · Answer 2 · answered Mar 21 '18 at 16:44

0

Don't open it in Excel. It always drops leading zeros off number fields unless you tell it otherwise. If you open it in a text editor and the zero is there, then PHP is doing the job correctly and you need to go to an Excel forum for help.

The answer is to change the formatting if you insist on using Excel.

answered Mar 21 '18 at 16:44

Pete

1,289
10
18

you are right but my question is - In CSV output like 1523 I want 01523. – Magento2 Devloper Mar 21 '18 at 16:46
'01523' is a string. As a number 01523 and 1523 are EXACTLY the same thing. – Pete Mar 21 '18 at 16:48
1

@Pete actually, as a number 01523 is 851. – Don't Panic Mar 21 '18 at 16:50
not same things some system in import csv time checking 5 digit Pincode ex-09523 so after putting in csv convert to 4 digits. – Magento2 Devloper Mar 21 '18 at 16:52
the system needs 5 digits. when we generate time to get in csv 4 digits values. think about my point. – Magento2 Devloper Mar 21 '18 at 16:54
If you enclose it in quotes, as suggested above, then the zero will be maintained in the csv. If you need a leading zero when parsing the csv, you can check for it there. Have you considered sprintf or str_pad? – Pete Mar 21 '18 at 16:58
@Magento2Devloper - yes, I see what you mean about octals - which means a string is the OP's only option unless it is parsed back to a leading zero afterwards – Pete Mar 21 '18 at 17:01
yes, I already tried with "01523" passing but the output is same. – Magento2 Devloper Mar 21 '18 at 17:05
so How can i get all header and values in double quote? like "column 1","column 2"... – Magento2 Devloper Mar 22 '18 at 07:56

Create CSV script in truncate 0 in values issue in PHP

2 Answers2