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$
    – M.R.
    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$
    – M.R.
    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$
    – Chris Degnen
    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$
    – Chris Degnen
    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$
    – Chris Degnen
    Jan 6, 2016 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.