Excel Line Endings Break PHP


Excel Line Endings Break PHP

Here’s a quick tip that might save the PHP developers out there some time. (It would have saved me thirty minutes of debugging if I had know this earlier.)

When you export an Excel file as a CSV in Office for Mac, Excel saves the file using CR line breaks instead of the standard LF (Unix) or CRLF (Windows) endings. CR’s will break PHP’s fgetcsv() function. So, before you attempt to parse the CSV file, make sure you convert it to the appropriate line endings.

More info about issues detecting line endings in PHP is available here.

Related Posts Plugin for WordPress, Blogger...

Tell us what you think

  • arthasmx

    @David Goodwin

    Thanks! hhehehe

  • Timo Scott


    only one PHP solution? mmmh

    dbTube is commercial….like the other graphical tools. :-/
    I think I can invest 4.99 Euro….



  • Timo Scott


    coding, coding, coding…..
    Is there a tool available for a “generic” Excel import? I have the same
    problem, importing a excel sheet. And the sheet format is a “moving target”

    I have read a lot of threads and a lot of code. But I want a
    more generic solution. Maybe a import definition file (mapping)
    and ONE generic import PHP script.

    ….is there something out there?


  • http://codepoets.co.uk David Goodwin

    ini_set(‘auto_detect_line_endings’, true)

    worked for me.


  • Ron

    Thanks for the post. This was pissing me off to no end.

  • Jason Wehmhoener

    Here’s a shell command that might help:

    tr \r \n /path/to/new_script

    From: http://www.macdevcenter.com/pub/a/mac/2003/11/07/scripting_osx.html

  • http://www.sitening.com Scott

    Official method of the Telalink Intern program:

    perl -pi.bak -e “s/\r/\n/g;” file.txt

  • http://www.sitening.com/ Tyler Hall

    I wasn’t trying to expose a flaw in PHP or Excel with this post. It was just meant to help other PHP developers avoid 30 extra minutes of debugging that I had to go through :)

  • http://www.sitening.com/ Thomas O’Connell

    I agree that the headline might be misleading, but the basic point that platform-specific line endings still matter and the reference to related information in PHP still have value.

  • http://jaxn.org/blog Jackson

    Isn’t that just what is historically referred to as “Mac line endings”?

    I have had to type the following too many times in the past:

    $string = str_replace(“\r”,”\n”,str_replace(“\r\n”,”\n”,$string));