Message Boards Message Boards

Import CSV containing line-breaks?

GROUPS:

Mathematica does not seem to handle CSV files containing line breaks. A specification of CSV at http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm provides details on how line breaks should be encoded. When integrating with other systems I encounter this encoding. Mathematica does not handle the line breaks correctly.

Test input:

Conference room 1, "John,
Please bring the M. Mathers file for review
-J.L.
",10/18/2002

Mathematica Notebook Test Case:

In[39]:= csv=Import["~/Downloads/test.csv"]
Out[39]= {{Conference room 1, "John,},{Please bring the M. Mathers file for review},{-J.L.},{",10/18/2002}}
In[40]:= Length[csv]
Out[40]= 4
In[41]:= csv[[1]]
Out[41]= {Conference room 1, "John,}
In[42]:= csv[[2]]
Out[42]= {Please bring the M. Mathers file for review}
In[43]:= csv[[3]]
Out[43]= {-J.L.}
In[44]:= csv[[4]]
Out[44]= {",10/18/2002}

Test system: "11.1.1 for Linux x86 (64-bit) (April 18, 2017)"

Attachments:
POSTED BY: Donald Pellegrino
Answer
2 months ago

This is indeed unexpected behavior.

str="Conference room 1, \"John,
Please bring the M. Mathers file for review
-J.L.
\",10/18/2002";
ImportString[str,"CSV"]
Grid[%,Frame->All]

Indeed does not work as it should, what works slightly better is:

ImportString[str, "CSV", "TextDelimiters" -> "\"", "LineSeparators" -> {"\r\n", "\n", "\r"}]

Though it leaves some quotes. I'm not sure if we can tame the Import in 11.1.1 to make it work.

A little bird told me, though, that in the next version of Mathematica the CSV import will be redesigned, and the import works perfectly for your example without any options…

POSTED BY: Sander Huisman
Answer
2 months ago

There is a bug in "TextDelimiters":

https://mathematica.stackexchange.com/a/140789/280

POSTED BY: Alexey Popkov
Answer
2 months ago

Great. CSV remains a very important data format and handling it consistently really makes life easier. Thanks for validating the test case. I am eager for the next version.

POSTED BY: Donald Pellegrino
Answer
2 months ago
POSTED BY: Alexey Popkov
Answer
2 months ago

Just released Mathematica 11.2.0 with updated CSV importer seems to work correctly with your example:

ImportString["Conference room 1, \"John,\nPlease bring the M. Mathers file for review\n-J.L.\n\",10/18/2002", 
 "CSV"]
{{"Conference room 1", 
"John,\nPlease bring the M. Mathers file for review\n-J.L.\n", 
"10/18/2002"}} 

But unfortunately the bug in "TextDelimiters" is still there.

POSTED BY: Alexey Popkov
Answer
1 month ago

Group Abstract Group Abstract