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.

Solution 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 . '"'

Solution 2

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

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

Solution 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

Solution 4

output it as a formula, so as:

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

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

View more solutions

Comments

[roberthuttinger]

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

[psx]

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

[psx]

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

[psx]

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.

[SeanC]

@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

[psx]

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!

[Altimus Prime]

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.

[Miguel]

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