fputcsv and integer typcasting to string

Question

So I have a line that I want to do a fputcsv on that has some integers (that I need to be treated as strings but they are numbers). These integers have leading zeroes that get cut off when I do the fputcsv but I don't want that to occur, is there any way around this? I tried just typcasting as (string) and putting my variable in quotations but the only way I have found so far is to just put quotes around the entire number which leads the quotation marks to be shown in the csv file when I open it up in excel, which I don't want to occur. Does anyone know of a way to get this to work? I think the fputcsv is just automatically assigning this variable a type for some reason and making it a integer or something...

EDIT Example Text:

What I fputcsv:

02305109

What I get in the csv file opened in excel:

but the leading zero is still there when I just use vi to open said csv file. Really strange.

Additionally, when I put quotes around the string before the fputcsv the file has triple quoted it rather than just putting one set of quotes.. really odd. — Ben Nelson, Jul 10 '12 at 18:12
If you use the Excel text import wizard to open the CSV you can tell Excel to treat that column as text. Padding numbers with leading "0" is a pain in Excel (and elsewhere) and best avoided if possible. — Tim Williams, Jul 10 '12 at 18:39

score 26 · Answer 1 · answered Dec 08 '16 at 21:16

26

I had the same problem for long numbers which I wanted as a string. Wrap it in single quotes and make it evaluable.

'="' . $yourNumber . '"'

answered Dec 08 '16 at 21:16

user1792407

281
3
3

score 3 · Answer 2 · edited Mar 20 '17 at 10:18

3

Excel is interpreting the value as a number and formatting it as so. This has nothing to do with php.

This SU post has some information: https://superuser.com/questions/234997/how-can-i-stop-excel-from-eating-my-delicious-csv-files-and-excreting-useless-da

edited Mar 20 '17 at 10:18

Community

1
1

answered Jul 10 '12 at 18:38

databyss

5,538
1
19
23

1

I have had this issue before. Excel tried to be smart but is quite stupid when it comes to casting and default preferences. Seems such a simple thing that could have been fixed a decade ago – roberthuttinger Jul 16 '13 at 13:26
False. Formating a proper csv means enclosing strings with quotation marks. If you use fputcsv there is no option to encase the numbers as a string that I can find anywhere. – Altimus Prime Nov 23 '16 at 17:34
you can edit the ouput of the csv cell value as a formula, so you would do something like : $lines["mobile_phone"]="=\"".$lines["mobile_phone"]."\""; – Miguel Aug 28 '17 at 10:34

score 3 · Answer 3 · answered Jul 02 '13 at 15:20

3

Try prepending (or appending) your leading-zero integers with a null character.

$csv[0] = 02392398."\0";

answered Jul 02 '13 at 15:20

Sander

1,465
9
22

score 2 · Answer 4 · answered Oct 03 '16 at 11:43

2

output it as a formula, so as:

$line["phone"]= "=\"" .$line["phone"]. "\"";

answered Oct 03 '16 at 11:43

Miguel

2,305
2
26
25

score 1 · Answer 5 · answered Apr 26 '18 at 07:49

It is too simple my code is

//header utf-8
fprintf($fh, chr(0xEF).chr(0xBB).chr(0xBF));
fputcsv($fh, $this->_head);
$headerDisplayed = true;
//put data in file
foreach ( $this->_sqlResultArray as $data ) {
    // Put the data into the stream
    fputcsv($fh, array_map(function($v){
        //adding "\r" at the end of each field to force it as text
        return $v."\r";
    },$data));
}

score 0 · Answer 6 · answered Oct 25 '17 at 13:04

0

Adding a single quote to the beginning of the data will solve the problem.

i.e '930838493828584738 instead of 930838493828584738 which converts to this 934E+n

If the csv file is provided by a third party, this could be a problem.

answered Oct 25 '17 at 13:04

PeterT

49
7

score -1 · Answer 7 · answered Jul 10 '12 at 18:43

-1

format the column using "00000000"

This has the advantage that it will preserve the format on save

answered Jul 10 '12 at 18:43

SeanC

15,067
5
42
63

This doesn't work (in Excel 2010 anyway). The leading zeros appear to be lost and can't appear again no matter what formatting you use afterward. – psynnott Apr 14 '14 at 15:57
@psynnott, it works in every version of Excel I have used, from v2.x to 2013. make sure you are going to Custom format, and typing in the amount of zero's you want to appear. on a reload of the file, they will disappear again, but that's an annoyance of excel. If you look at the file in a text editor, you will see all the relevant zeros – SeanC Apr 14 '14 at 21:10
Yea, unfortunately my users are going to open a CSV in Excel by default 99% of the time and complain about formatting not being what they expected. Of course, asking them to do a bit of (understandable) work to fix it is received with sighs and complaints :) I hate programming sometimes! – psynnott Apr 15 '14 at 14:23

score -1 · Answer 8 · answered Nov 11 '13 at 11:17

-1

You just need to add a quote to the beginning of the number:

'123456

and excel will not format this cell as a number.

answered Nov 11 '13 at 11:17

joaoramos

1

This does not work (in Excel 2010 anyway). CSV files with a quote like above will show a literal quote in the cell. – psynnott Apr 14 '14 at 15:56

score -2 · Answer 9 · answered Jul 10 '12 at 18:25

-2

Try a leading apostrophe (single quote). IIRC that keeps the leading zeros but doesn't appear in Excel.

answered Jul 10 '12 at 18:25

grubber

1

@grubber which requires the Excel user to know what they are doing. In 99% of the cases this won't be the case! – psynnott Apr 14 '14 at 15:56

fputcsv and integer typcasting to string

9 Answers9