4
$\begingroup$

How can I make sure that certain string columns of a matrix I want to export get properly formatted? That is, all quotes escaped, and every string wrapped in quotes. Here's what happens now:

enter image description here

According to the csv specs, embedded double-quote characters must be doubled, and the field must be delimited with double-quotes.

I know about the "TextDelimiter" option, but the proper escaping eludes me.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

You can write the string as you require by using a replacement.

Export["text.csv", {{1, "2", 3}, {1, "2", 3}} /. a_String :> StringJoin["\"", a, "\""]] FilePrint["text.csv"] 
1, "2", 3 1, "2", 3

Edit further to comments

Internal quotes can be put into the CSV according to the OP's linked spec.

Export["text.csv", {{1, "2 \"\"two\"\" 2", 3}, {1, "2 \"\"two\"\" 2", 3}} /. a_String :> StringJoin["\"", a, "\""]]

FilePrint shows the data exactly how it looks in the CSV, including the double quotes.

FilePrint["text.csv"] 
1, "2 ""two"" 2", 3 1, "2 ""two"" 2", 3

Import retrieves the data the same as the string in the Export statement.

Import["text.csv"] 
{{1, "2 \"\"two\"\" 2", 3}, {1, "2 \"\"two\"\" 2", 3}}

Interesting note

It isn't straightforward to import numbers as strings in single quotes since they get converted to numbers. For example.

Export["text.csv", {{1, "2.2", 3}, {1, "\"2.2\"", 3}, {1, "two", 3}, {1, "\"two\"", 3}} /. a_String :> StringJoin["\"", a, "\""]] FilePrint["text.csv"] 
1, "2.2", 3 1, ""2.2"", 3 1, "two", 3 1, ""two"", 3 
Import["text.csv"] 
 {{1, 2.2`, 3}, {1, "\"2.2\"", 3}, {1, "two", 3}, {1, "\"two\"", 3}}

This workaround retrieves one string 22 in single quotes.

Import["text.csv"] /. a_String :> If[NumberQ@ ToExpression[StringTrim[a, "\""]], StringTrim[a, "\""], a] 
 {{1, 2.2`, 3}, {1, "2.2", 3}, {1, "two", 3}, {1, "\"two\"", 3}}
Improve this answer
$\endgroup$
5
  • $\begingroup$ Thanks for your response. what about proper escaping for quote marks inside the string? The docs for csv I linked to require "" as the escape for each inner double quote mark. $\endgroup$ Commented Jan 4, 2016 at 23:59
  • $\begingroup$ I feel this is something that should be done with Export options, no manual replacement rules, no? $\endgroup$ Commented Jan 5, 2016 at 5:35
  • $\begingroup$ Quote marks inside the string should already be escaped otherwise Mathematica would see them a separate strings, e.g. "two"2"two" is two strings and a number multiplied together. With escapes in the strings your example can be: Export["~/text.csv", {{1, "2\"two\"2", 3}, {1, "2\"two\"2", 3}} /. a_String :> StringJoin["\"", a, "\""]] $\endgroup$ Commented Jan 5, 2016 at 9:35
  • $\begingroup$ Re: "isn't this something that should be done with Export?" I think the answer is summed up by this post: Writing numbers as strings in a .csv file - quote: "Here's the thing, with CSV you are COMPLETELY limited in what you can do with the text. ..." $\endgroup$ Commented Jan 6, 2016 at 14:56
  • $\begingroup$ Obviously you can put as many double quotes as you like in the CSV to conform with the specs, e.g. Export["~/text.csv", {{1, "2\"\"two\"\"2", 3}, {1, "2\"\"two\"\"2", 3}} /. a_String :> StringJoin["\"", a, "\""]]. The CSV written out contains no backslashes, as shown by FilePrint. (They are put back in if the CSV is imported into Mathematica using Import.) $\endgroup$ Commented Jan 6, 2016 at 15:13

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.