Message Boards Message Boards

Import CSV containing line-breaks?

Posted 7 years ago

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:
5 Replies
Posted 7 years 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
Posted 7 years 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.

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
Posted 7 years ago

There is a bug in "TextDelimiters":

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

POSTED BY: Alexey Popkov
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract