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.

Jon Henshaw
Co-Founder and President

Jon is the Co-founder and President of Raven Internet Marketing Tools.

Jon is the Co-founder and President of Raven Internet Marketing Tools.

  • 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(“rn”,”n”,$string));

  • 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://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 Scott

    Official method of the Telalink Intern program:

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

  • 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

  • Ron

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

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

    ini_set(‘auto_detect_line_endings’, true)

    worked for me.

    David.

  • Timo Scott

    Hi,

    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?

    Timo

  • Timo Scott

    @andreas

    only one PHP solution? mmmh

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

    Thanks

    Timo

  • arthasmx

    @David Goodwin

    Thanks! hhehehe