fopen() returning unexpected array with Mac Excel generated csv

Question

I'm experiencing an interesting issue. I've created a CSV file with Excel and have tried to use fopen() and fgetcsv() to read the file into PHP. Unexpectedly, the array returned by my code makes me think the file has one row with 76 fields vs 5 rows with 16 ( I know, the math isn't right... that's explained a bit later). TextEdit dispels that theory as it renders the file with new lines for every row. When I delete the new lines in TextEdit and then re-enter a newline for each row, the csv is read properly.

Before deleting the new lines, I opened the file in NetBeans with the show hidden characters feature and netbeans seems to think it was a paragraph. It seems fopen() does not.

I saw a similar problem from php str_getcsv array issue and I used the code provided in the first example to no avail. It really seems like Excel is providing a new line character that most other things recognize as a new line, but fopen() does not. Here's my code and the csv file in case people are interested.

$data= array();      
$handle = fopen(Yii::app()->basePath ."/csv/test.csv", "rb");
while ( !feof($handle) ) {
    $data[] = fgetcsv($handle);

}
echo "<pre>";
die(var_dump($data));
fclose($handle);

Here is the csv file:

id,first_name,last_name,address_1,address2,city,state,postal,image_path1,image_path2,image_path3,image_path4,image_path5,text_area,template_name,order_date
id,first_name,last_name,address_1,address2,city,state,postal,image_path1,image_path2,image_path3,image_path4,image_path5,text_area,template_name,order_date
id,first_name,last_name,address_1,address2,city,state,postal,image_path1,image_path2,image_path3,image_path4,image_path5,text_area,template_name,order_date
best ever!,,,704 west jefferson,,winterset,ia,50273,,,,,,,dummy,
hello!!,,,Welcome,,,,,,,,,,,,

Here is a link to a screen shot of the excel table: http://grab.by/r9sg

Also, I just noticed that the last field of each row has a new line , i.e., the array value of what should be the end of the first row is order\nid (I think... it at least has the \n in the string...). That seems weird... I'll provide the array that I see from the var_dump...:

array(1) {
  [0] =>
  array(76) {
    [0] =>
    string(2) "id"
    [1] =>
    string(10) "first_name"
    [2] =>
    string(9) "last_name"
    [3] =>
    string(9) "address_1"
    [4] =>
    string(8) "address2"
    [5] =>
    string(4) "city"
    [6] =>
    string(5) "state"
    [7] =>
    string(6) "postal"
    [8] =>
    string(11) "image_path1"
    [9] =>
    string(11) "image_path2"
    [10] =>
    string(11) "image_path3"
    [11] =>
    string(11) "image_path4"
    [12] =>
    string(11) "image_path5"
    [13] =>
    string(9) "text_area"
    [14] =>
    string(13) "template_name"
    [15] =>
    string(13) "order_date
id"
    [16] =>
    string(10) "first_name"
    [17] =>
    string(9) "last_name"
    [18] =>
    string(9) "address_1"
    [19] =>
    string(8) "address2"
    [20] =>
    string(4) "city"
    [21] =>
    string(5) "state"
    [22] =>
    string(6) "postal"
    [23] =>
    string(11) "image_path1"
    [24] =>
    string(11) "image_path2"
    [25] =>
    string(11) "image_path3"
    [26] =>
    string(11) "image_path4"
    [27] =>
    string(11) "image_path5"
    [28] =>
    string(9) "text_area"
    [29] =>
    string(13) "template_name"
    [30] =>
    string(13) "order_date
id"
    [31] =>
    string(10) "first_name"
    [32] =>
    string(9) "last_name"
    [33] =>
    string(9) "address_1"
    [34] =>
    string(8) "address2"
    [35] =>
    string(4) "city"
    [36] =>
    string(5) "state"
    [37] =>
    string(6) "postal"
    [38] =>
    string(11) "image_path1"
    [39] =>
    string(11) "image_path2"
    [40] =>
    string(11) "image_path3"
    [41] =>
    string(11) "image_path4"
    [42] =>
    string(11) "image_path5"
    [43] =>
    string(9) "text_area"
    [44] =>
    string(13) "template_name"
    [45] =>
    string(21) "order_date
best ever!"
    [46] =>
    string(0) ""
    [47] =>
    string(0) ""
    [48] =>
    string(18) "704 west jefferson"
    [49] =>
    string(0) ""
    [50] =>
    string(9) "winterset"
    [51] =>
    string(2) "ia"
    [52] =>
    string(5) "50273"
    [53] =>
    string(0) ""
    [54] =>
    string(0) ""
    [55] =>
    string(0) ""
    [56] =>
    string(0) ""
    [57] =>
    string(0) ""
    [58] =>
    string(0) ""
    [59] =>
    string(5) "dummy"
    [60] =>
    string(8) "
hello!!"
    [61] =>
    string(0) ""
    [62] =>
    string(0) ""
    [63] =>
    string(7) "Welcome"
    [64] =>
    string(0) ""
    [65] =>
    string(0) ""
    [66] =>
    string(0) ""
    [67] =>
    string(0) ""
    [68] =>
    string(0) ""
    [69] =>
    string(0) ""
    [70] =>
    string(0) ""
    [71] =>
    string(0) ""
    [72] =>
    string(0) ""
    [73] =>
    string(0) ""
    [74] =>
    string(0) ""
    [75] =>
    string(0) ""
  }
}

I guess I'm at a loss for trying to explain why if I simply delete a new line in an editor (Netbeans, Coda, TextEdit) and re-enter the new line the code works as expected, but not if I use the file generated by excel. Thoughts? Thanks in advance for any help.

If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem. — Mark Baker
– Mark Baker, Commented Oct 15, 2013 at 10:19

Kyle Jones · Accepted Answer · 2013-10-14 23:15:10Z

1

You're using a Mac. Prior to the UNIXification of Mac OS, CR (ASCII code 13) was the newline character for Macs. Post Mac OS X, LF (ASCII code 10) is the newline character.

Excel apparently is acting like a pre Mac OS X app and using CR as newline, which confuses PHP running on a UNIX machine, which expects LF.

TextEdit recognizes both newline conventions for compatibility but inserts LF preferentially as the newline character because that's what UNIX tools expect. That's why deleting and reinserting newlines "fixes" the problem; the CRs are silently being replaced with LFs.

The simplest way to fix your CSV file is the UNIX command:

tr '\015' '\012' < foo.csv > new.csv

answered Oct 14, 2013 at 23:15

Kyle Jones

5,5521 gold badge24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirk Over a year ago

Thanks for that explanation as well as the solution to fixing the csv!

Collectives™ on Stack Overflow

fopen() returning unexpected array with Mac Excel generated csv

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related