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:

2305109

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

Laevorotatory

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. – Laevorotatory

How do you know it is not excel stripping the leading 0? – Astrix

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. – Dispensary

See it here. – Photokinesis

Answer 1

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

'="' . $yourNumber . '"'

Clipper

Answer 2

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

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

Breuer

Answer 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

Aggappe

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 – Caco

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. – Snowplow

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"]."\""; – Retrospective

Answer 4

output it as a formula, so as:

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

Retrospective

Answer 5

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));
}

Stereophonic

Answer 6

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

'123456

and excel will not format this cell as a number.

Ninefold

This does not work (in Excel 2010 anyway). CSV files with a quote like above will show a literal quote in the cell. – Gladdie

Answer 7

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.

Louden

Answer 8

format the column using "00000000"

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

Snug

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. – Gladdie

@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 – Snug

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! – Gladdie

Answer 9

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

Recipience

@Recipience which requires the Excel user to know what they are doing. In 99% of the cases this won't be the case! – Gladdie