CSV Parser: Support Mac-style line endings

Ran into this problem yesterday,
usually when running my own csv imports I throw this in the top of the file to prevent issues on my mac.

ini_set('auto_detect_line_endings', true);

Later today if I get back to it I will add this to the module and do some tests with my files that weren't working to see if it fixes the issue here.

edit: I added the line to the top of ParserCSV.inc and that fixed the problem.

Subscribing

It is a pain to do a replace of Mac CR with a CR/LF combination all the time.

Will try Dru-paul's fix.

I'd like to make auto detect line endings a hidden setting (see README.txt)

My question now is whether it should be on or off by default. This basically breaks down to:

1. How large is the performance penalty? E. g. on a 10k lines file?
2. How common are carriage-returns as item separators?

http://www.php.net/manual/en/filesystem.configuration.php#ini.auto-detec...

I think 2. is not an issue. I'd like to get a better idea of 1. before turning on auto_detect_line_endings per default. If we can't answer the performance question, I am happy to accept a patch that makes it a hidden setting and leaves it off for now.

Alex to help shed some light on #1. I found this.

It is worth noting that:
ini_set('auto_detect_line_endings', true);
is only inspected and used by the stream when it reads the *first* line (via fgets). Subsequent reads use the value it detect then.
This is only an issue if (for some strange reason!) you have a mixture of different line ending conventions in a file.

Here
http://bugs.php.net/bug.php?id=23636

So this leads me to believe the size or number of lines in a file has no affect if it is only used when the first line of the file is read.
We've been fine on CSV imports of around 1800 lines.

#4: that's useful to know, thanks. Seems like performance is not going to be a hurdle. An actual performance test is quick and won't hurt though.

If we can confirm that the performance penalty is minimal, this patch is may be all we need.

I've also attached a fix that's been working for me. It
- replaces the line endings CSV files to unix ones that the PHP file functions recognize
- saves the normalized file as a temporary file
- uses the new temporary file to iterate over

If there is any nervousness about making the import depend on a PHP ini setting, this alternative is working well for me.

(this is a first attempt at a Drupal patch. If I'm going through the wrong channels or goofing here, sorry in advance!)

#8

I don't think that's a good approach as it loads a potentially huge file into memory, parses through it and then writes it back to disk. Back to #6.

this is a first attempt at a Drupal patch.

From a technical perspective, there are 2 things not right with the patch

- it's not rolled from the project directory
- it uses tabs where it should use spaces http://drupal.org/coding-standards

Sorry for the delayed response and thank you very much for the feedback on the patch. I'll be sure to correct these issues for future patches!

I've been running into the same issue and tried different things to get it to work. The main thing was I had smart quotes in a CSV that were only allowing partial imports into Feeds. One method that seems to work for me is:

1. Open CSV file in Textwrangler on the Mac
2. Under the 'Text' menu, choose 'Convert to ASCII'
3. Save as CSV file with the 'Line Breaks' setting set to 'Windows'.

I created the source CSV file in Excel in Windows, using the CSV (Macintosh) setting. I have not tried creating the source file using Excel for Mac because I did not see a CSV file extension in the save options.

Hm... I tried the patch in #6, and it doesn't seem to make a difference to me. CSVs created in Mac Excel still don't work, unless I manually edit the CSV in vi to replace the ^M linebreaks with "real" unix ones. Feeds 6.1.0-beta10 .

I should note that the CSV contains some multi-entry taxonomy term fields (haven't done anything to make it detect those yet), and a long quoted body. It's possible that PHP is picking up the wrong character for line endings, and then not finding any.

Patch in #6 works great. Thanks!

Subscribe

EDIT: For anybody else who comes across this in the mean time, I find the quickest solution so far is to edit the CSV in vim and run the command

:%s/\r/\r/g

#6 is definitely the most correct fix possible.
And it worked for me today, when I hit it for the first time (after committing the sin of trying to use Excel on the Mac)

Here's a re-roll against todays 7.x-2.x-dev.
Test, commit and backports are all trivial, safe, and I can't imagine any performance issue with what is a core PHP utility.

If this problem is a 'feature request', then due to the unhelpful lack of error message ("There were no new nodes" - which could indicate an error anywhere)
I'd prefer the message said "This CSV appears to be empty" - which would give us a clue where to look. I spent quite a while backtracing the process until I found that the CSV thought the entire file was a header line.

#16 works well for me, had the same issues with excel mac.

I ran this patch. Now I can import my nodes again on a Mac using Feeds. However one of my CSV columns is still throwing issues on the import. When I import the column I'm mapping to my node "Body" field is throwing this error in Drupal:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xED\xE7cere...' for column 'body_value' at row 1
Error message SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xED\xE7cere...' for column 'message' at row 1

I have FULL HTML set on my import as the text format, and this column does contain HTML. It worked before when I first installed Feeds but now it's not working on re-import and also once I've applied the patch per above.

Additionally if I remove this column mapping on the import, the rest of the import works and the nodes are created without issue.
Any suggestions?

-Trevor

Figured it out. The CSV files need to be in UTF-8 format. Per this post:

http://drupal.org/node/1140194#comment-4679698

I tweaked them and now they are importing as long as they have been saved in UTF-8.

Seems to be working now.

I'll try this without the patch applied as well as now I'm not sure if the patch is required if they are in UTF-8.

-Trevor

Yes this does seem to work without having to apply the patch. All I needed to do is re-save my CSVs in UTF-8. Then they import fine.

-Trevor

This should be re-rolled to work with #1369874: Don't roll own CSV parser; use PHP's native one.

Still running into this and it's been 8 months since the last comment. Status on this?

I just spent a few hours wondering why my imports weren't working. Only when I went to open the csv file in my code editor, that a warning came up about line endings which lead me to this issue.

I used a program called CSVEditor for mac, in which you can select the line endings you want when saving. This fixed the issue for me.

Agreed with #16 on the error messages. Thanks for the work on the patch.

Patch on #16 works for me.

http://drupalcode.org/project/feeds.git/commit/b32b431

Committed to 7.x

http://drupalcode.org/project/feeds.git/commit/ed44dd1

THANKYOU!