Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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~

share|improve this question
2  
Could you provide one of the csv files in question? I never had problems importing csv files –  paw Aug 29 at 15:09
    
Try Import["filename.csv","CSV"] –  paw Aug 29 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 at 15:15
2  
Some of the comma-separated-values contain new lines, so this is not a valid CSV file. –  rhermans Aug 29 at 15:26
1  
Just to let you guys know I removed the listing notes column and all the issues were resolved. –  jqwerty Aug 29 at 16:49

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
1  
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 at 16:09
    
Thanks for the explanation! –  jqwerty Aug 29 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.