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

Question

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~

Answer 1

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

6. 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.

Answer 2

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.

Answer 3

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.