2

I am creating a CSV export, and have a date string that keeps getting formatted when open in Excel or Numbers. How can i force the data to be text.

Stop Excel from automatically converting certain text values to dates

I can't seem to apply the above solution in PHP.

if(strlen($date) == 10){

        // 12-24-1983
        $date = explode('-',$date);
        $m = $date[0];
        $d = $date[1];
        $Y = $date[2];

        $date = $Y.'-'.$d.'-'.$m.' 00:00:00';
    }
    else {
        // 2014-08-07 21:04:19
        $date = explode(' ',$date);
        $datePiece = $date[0];
        $timePiece = $date[1];

        $datePiece = explode('-',$datePiece);

        $Y = $datePiece[0];
        $m = $datePiece[1];
        $d = $datePiece[2];

        $date = $Y.'-'.$d.'-'.$m.' '.$timePiece;
    }

    return "$date";
Community
  • 1
  • 1
Hector
  • 682
  • 3
  • 14
  • 27
  • 1
    don't use csv, then. use PHPExcel and build a proper excel file, where you can specify cell formatting. – Marc B Oct 07 '14 at 17:06
  • yes, it looks like you can't seem to apply the above solution to PHP. that is true from the example you give: it actually shows that nothing has been applied at all. however for asking on stackoverflow, you need to ask a programming question. not being able to apply some code-example you find on this website without sharing other further details of your programming problem (what you miss in understanding for example) does most likely not qualify as a programming question. so it's unclear at best but in it's own even unfitting for a programming Q&A site. try to improve your question by editing. – hakre Oct 07 '14 at 17:43
  • Also possible self-duplicate of: http://stackoverflow.com/q/26238178/367456 – hakre Oct 07 '14 at 17:45
  • If you don't want to use PHPExcel, open the CSV file with Libre Office and save it as XLSX. Microsoft Excel refuses to process CSV data in a useful way (maybe they help you if you buy one of their support contracts, can't say about that). – hakre Oct 07 '14 at 17:50

1 Answers1

1

Have you tried putting a single quote in front of your date value? In excel, that would treat it as a string value (not date) for that one cell.

=================================================

This a spreadsheet issue, not a PHP coding one. If you want the spreadsheet to view it differently, tell the spreadsheet program to view/format it differently.

The correct data is there, the issue is not with the data; it's with the program viewing the data. So as mentioned you can encode some other formatting values into an xls (xlsx) file to pragmatically enforce for that specific spreadsheet program/format, but csv doesn't allow you to choose formatting.

airtech
  • 486
  • 3
  • 12