Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '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
2  
Some of the comma-separated-values contain new lines, so this is not a valid CSV file. – rhermans Aug 29 '14 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 '14 at 16:49
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 '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.

share|improve this answer
    
This solution only works because the new records always starts with a DigitCharacter... Is there a better way to do this? I was thinking of using a regular expression with Shortest[ "\"" ~~ ... EndOfLine ... ~~ "\""] or something along those lines, but to no avail.... Not to mention that there could be multiple EndOfLine within double quotes... – Denis Cousineau Apr 28 at 1:51

I can propose a more general solution. It is based on regular expressions (or here, string patterns). The idea is to look for the shortest strings in the file that begins with double-quote, ends with double quotes, does not contain any double quote, but can contain one or many EndOfLine. As it is a CSV, there are comma before the double-quote (thus, this solution cannot be applied if the double-quote string can be the first field in the input). First, lets define a string pattern with any text or EndOfLine:

textenter = Repeated[Except["\""] .. ~~ EndOfLine];

It can contain any number of CRLF as long as some text preceeds them.

The second step is to locate all the substrings in a string variable, say csvstring, that match a comma, a double-quote, a textenter pattern, some final text before another double-quote:

u = StringCases[cvsstring,{Shortest[",\"" ~~ x : textenter ~~ y___ ~~ "\""]}];

Next, replace all the occuences found in u with the same but the CRLF replaced by some character (here I used [Wolf] just for the sake of the example):

v = StringReplace[cvsstring, Map[# :> StringReplace[#, "\n" -> "\[Wolf]"] &, u]];

This is it; convert v as any csv text string containing strings delimited with double quote with:

ImportString[v, "CSV", "TextDelimiters" -> "\""]

This can be made into a function with:

RemoveInnerCRLF[cvsstring_] := Module[{u, v},
  u = StringCases[cvsstring,{Shortest[",\"" ~~ x : textenter ~~ y___ ~~ "\""]}];
  v = StringReplace[cvsstring, Map[# :> StringReplace[#, "\n" -> "\[Wolf]"] &, u]];
  ImportString[v, "CSV", "TextDelimiters" -> "\""]
  ]

which you can use with

reg = RemoveInnerCRLF[Import[myfile, "Text"]];

where myfile is some indication of a file that needs to be imported.

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.