How to properly export string columns in csv?

Question

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:

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.

Answer 1

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}}