# Why does a certain CSV file import properly into LibreOffice but not into Mathematica 10.0?

I'm working on a project involving data manipulation and it involves working with CSV files.

I know that the files are properly formatted as when I import them into LibreOffice, all of the columns contain the information they are meant to. When I import the files into Mathematica (10.0) however, data from the 45th column (for example) shows up in the 1st column.

I'm using:

    Import["filename.csv","Data"]


Does anyone know what the issue might be?

Thank you~

-
Could you provide one of the csv files in question? I never had problems importing csv files – paw Aug 29 '14 at 15:09
Try Import["filename.csv","CSV"] – paw Aug 29 '14 at 15:09
Here's a link to the file: expirebox.com/download/13fef09bf65bcc7491068139c6b637ee.html I tried using "CSV" instead of "Data" but I ended up with the same problem. – jqwerty Aug 29 '14 at 15:15
Some of the comma-separated-values contain new lines, so this is not a valid CSV file. – rhermans Aug 29 '14 at 15:26
Just to let you guys know I removed the listing notes column and all the issues were resolved. – jqwerty Aug 29 '14 at 16:49

I think that the existing comma-separated-values, fields, containing "new lines" are the offending elements in your file.

If you do

Length /@ Import["data.csv"]


{64, 64, 64, 64, 64, 64, 1, 1, 25, 64, 64, 8, 1, 1, 1, 1, 1, 25, 7, 1, 1, 1, 1, 25, 1, 2, 2, 25, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 7, 1, 1, 1, 1, 1, 1, 1, 25, 12, 1, 25, 64, 9, 1, 1, 1, 1, 1, 25, 64, 64, 64, 64, 64, 64, 64, 64, 64}

when all records should be of 64 fields.

CSV is not a general standard, so Mathematicas's implementation is as good as any other in principle. Nevertheless, RFC 4180 defines the following

1. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example:

"aaa","b CRLF bb","ccc" CRLF zzz,yyy,xxx

where CRLF stands for "carriage return" and "line feed" characters.

So to answer the question: Why? Because Mathematica is ignoring that CSV "feature" and considering CRLF within quotations marks as the end of the line record.

-
As a comment, I think that a better implementation would be to offer the option to use or ignore the feature of CRLF protected by quotation marks. – rhermans Aug 29 '14 at 16:09
Thanks for the explanation! – jqwerty Aug 29 '14 at 16:09

Here's one way to resolve the CRLF problem.

data = Import[fname, "Text"];
StringReplacePart[data, "\[Wolf]",
StringPosition[data, "\n" ~~ DigitCharacter]];
StringReplace[%, "\n" -> ""];
StringReplace[%, "\[Wolf]" -> "\n"];
data2 = ImportString[%, "CSV"];


First, I import the file as a single text string. I notice in the file that all of the 'valid' linefeeds are immediately followed by a digit character (from cell 1) so we replace all the valid linefeeds with a character that is not in the original string (say, the Mathematica wolf). Then delete all the remaining linefeeds and reinsert the valid ones. data2 now contains 59 rows, each with 64 columns.

-