PHP fputcsv showing long integers in excel as scientific notation

Question

I have a simple script that outputs data from my database to a CSV file.

My problem is that when I export mobile numbers, excel sees them as long integers and displays them in scientific notation.

If I go to format cells and change it to a number they all display fine.

Is there a way to set the column as a integer without changing it in excel.

Here is my code:

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');


$output = fopen('php://output', 'w');


fputcsv($output, array('Book Reference','Company','Type','Site Name', 'Date', 'Time', 'Name', 'Email', 'Mobile', 'Team ID'));

Below is the code that sits inside my while loop

// THIS IS WHERE I SET VARIABLES

fputcsv($output, array($booking_reference,$company,$type,$site_name,$date,$time,$name,$email,$mobile,$team_id));

can't do mucha bout that with CSV, because csv has absolutely no way of indicating formatting of a field. if you want a "large" number to be treated as text, you should probably look into using an excel library to generate a native .xls file, where you CAN force large numbers to be treated as text. — Marc B, Apr 20 '15 at 21:53
Import the file into excel and, from there, you can define the field properties. The issue is more with how excel defaults CSV imports. — David Wyly, Apr 20 '15 at 21:55
I like PHPExcel for generating xls files with formatted cells. This SO thread has many more suggestions: http://stackoverflow.com/questions/3133357/what-is-the-best-way-to-create-xls-file-in-php — John McMahon, Apr 20 '15 at 22:38

score 2 · Answer 1 · answered Apr 20 '15 at 23:51

I had to deal with this sort of thing once. Although it is not very elegant and is not pure CSV. The only way I know off to force Excel to cast a value, from a csv-source, is to use a 'formula' as a value in the csv-data.

So ... to force excel to interpret the numbers as text you could use:

....,'="5551212 "',...

score 1 · Answer 2 · answered Apr 20 '15 at 21:59

CSVs use a delimiter to separate column data. There are no formatting options for specific data types. The issue lies in the fact that Excel defaults the data type automatically. You need to override this when you import your CSV manually.

To do this: For all CSVs, you can import all data as text by going to Data -> Get External Data from Text, and then selecting the Delimited bullet point, check My data has headers, click Next, then check the field for Comma under the Delimiters column, hit Next and -- this is the key -- highlight all of the columns in the data preview, and then select Text under the Column data format before hitting Finish.

This will allow for all CSV files to be imported as text, and solves all leading and trailing 0 problems, all scientific notation problems, and ensures that every VLOOKUP matches when making a direct comparison to another text field.

Yeah I am aware that I can change the settings in Excel, was more looking for a solution for the file itself. May have to look into a library to generate .xls. Thanks for your response. — Dan Winter-Wijntjes, Apr 20 '15 at 22:08

score 0 · Answer 3 · answered Dec 18 '19 at 01:02

To avoid scientific notation, you must add single quotes to each value of the array you want to convert to csv.

This example worked in my case.

         function insertquote($value) {
            return "'$value'";
        }



        foreach ($list as $ferow) {
            fputcsv($fp, array_map(insertquote, $ferrow), ';');
        }

score 0 · Answer 4 · answered Dec 15 '20 at 09:16

I had succes by using "Ale DC" answer but changed the "insertquote" function.

    foreach ($array as $line) 
            fputcsv($fh, array_map('insertquote', $line), $delimiter);
    
function insertquote($value) {
        return "\xEF\xBB\xBF".$value;
    }

PHP fputcsv showing long integers in excel as scientific notation

4 Answers4