Importing CSV with multiline text fields

Question

Bug introduced in 8 or earlier and fixed in 11.2.0

---

Here is a valid CSV:

a1,b1,c1,d1,"e1
e1
e1"
a2,b2,"c2
c2
c2",d2,e2

It has two rows and five columns, and contains two fields with embedded CRLF. (See the CSV standard). But Mathematica doesn't parse it as such.

Import["test.csv", "CSV"]

Result:

{{"a1,b1,c1,d1,\"e1"}, {"e1"}, {"e1\""}, {"a2,b2,\"c2"}, {"c2"}, {"c2\"", "d2", "e2"}}

The result is the same if I use

Import["test.csv","CSV","TextDelimiters"->"\""]

How can I get Mathematica to parse the CSV correctly?

Answer 1

Here is a code which accepts inside the fields delimited by double-quotes:

• commas
• carriage-returns
• any character different from double-quotes

This solution is not fully compliant with the standard, particularly with the point 6 of page 3, where it is written that a double-quotes may exist in a field.

This solution has not been tried on large files.

Preliminary

Generation of the file to import :

Export["file.txt", "a1,b1,c1,d1,\"e1
 e1
 e1\"
 a2,b2,\"c2
 c2
 c2\",d2,e2", "Text"]  

Importation of the whole file as a single string:

text00 = Import["file.txt", "Text"]  

Here is a utility for nice rendering of the CarriageReturn/Line-Feed (CRLF) :

toVisibleCRLF[exp_] := exp /. str_String :>  
        Row[StringSplit[str , "\n" -> Style["CRLF", Red]] ];  

Test of the utility on the imported text :

toVisibleCRLF[text00]  

Parsing

The idea is to isolate the shortest pairs of quotes (1) by wrapping them in a inert structure called doNotSplit[] :

list00 = StringSplit[text00, str : Shortest["\"" ~~ ___ ~~ "\""] :> doNotSplit[str]];
list00 // toVisibleCRLF // Column

Then the proper substrings are splitted at the commas and carriage-return.
Commas are eliminated.
Carriage-returns are maintained because we will need them for further record separations:

list01 = Flatten[
   If[Head[#] =!= doNotSplit, 
      StringSplit[#, {",", "\n" -> "\n"}], #] & /@ list00];
list01 // toVisibleCRLF // Column  

Next records are isolated:

list02 = Split[list01, And[#1 =!= "\n", #2 =!= "\n"] &];
list02 // toVisibleCRLF // (Grid[{#}] & /@ # &) // Column   

The code list02 = Split[list01, And[#1 =!= "\n", #2 =!= "\n"] &] is equivalent to
list02 = Split[list01, ! Or[#1 === "\n", #2 === "\n"] &]. It has splitted the list before and after each \n. That's the simplest way the remove the \n afterwards.

The final result is obtained after removing of the \n and the doNotSplit :

result = DeleteCases[list02 /. doNotSplit[str_] :> str, {"\n"}];
result // Grid  

The whole useful code :

text00 = Import["file.txt", "Text"]; 
list00 = StringSplit[text00, 
   str : Shortest["\"" ~~ ___ ~~ "\""] :> doNotSplit[str]];
list01 = Flatten[
   If[Head[#] =!= doNotSplit, 
      StringSplit[#, {",", "\n" -> "\n"}], #] & /@ list00];
list02 = Split[list01, And[#1 =!= "\n", #2 =!= "\n"] &];
result = DeleteCases[list02 /. doNotSplit[str_] :> str, {"\n"}]

Edit

(1) "shortest" in the meaning of the Shortest[] of Mathematica, which is a little bit special. For example, the pattern Shortest["A"~~__~~"B"] assumes the whole text is read from left to right and it takes the text between the first "A" and the next "B".
Of course, if there are several sequences "A...B" Mathematica redo it again.

Answer 2

Here is a start..

Export["test.txt", "a1,b1,c1,d1,\"e1\ne1\ne1\"\na2,b2,\"c2\nc2\nc2\",d2,e2"]

i = 0;
StringSplit[StringJoin[#], ","] & /@ 
  Split[Characters[Import["test.txt", "Text"]],
   (If[# == "\"", ++i]; ! (# == "\n" && EvenQ[i]) ) &]

Note this will break if there are commas within quoted strings.

Edit: a bit more elegant..and this takes care of the comma-within-quotes issue.

stringsplitnoquote[string_String, d_String /; StringLength[d] == 1] :=
  Module[{i = 1},
  StringSplit[
   string, _?((If[# == "\"", i *= -1]; i == 1 && # == d) &)]]

stringsplitnoquote[#, ","] & /@ 
     stringsplitnoquote[ Import["test.txt", "Text"] , "\n"]

Due credit.. borrowed from here : https://mathematica.stackexchange.com/a/103030/2079

Answer 3

Explicit setting for the option "LineSeparators" solves the problem (checked with versions 8.0.4, 10.4.1 and 11.1.1):

ImportString["a1,b1,c1,d1,\"e1
e1
e1\"
a2,b2,\"c2
c2
c2\",d2,e2", "CSV", "LineSeparators" -> "\n"]

{{"a1", "b1", "c1", "d1", "e1\ne1\ne1"}, 
 {"a2", "b2", "c2\nc2\nc2", "d2", "e2"}}

Just released Mathematica 11.2.0 with updated CSV importer handles your example correctly by default:

$Version
ImportString["a1,b1,c1,d1,\"e1
e1
e1\"
a2,b2,\"c2
c2
c2\",d2,e2", "CSV"]

"11.2.0 for Microsoft Windows (64-bit) (September 11, 2017)"

{{"a1", "b1", "c1", "d1", "e1\ne1\ne1"}, 
 {"a2", "b2", "c2\nc2\nc2", "d2", "e2"}}