Escaping quotes for CSV output

Questions / Help
1

I have database data that I need to output as CSV. The requirements are very simple and I am not using any CSV generator library.

I would like each individual field to be wrapped in quotes, and therefore want to escape any single or double quotes embedded in the data. That is, the values: O'Brien and He said, "How are you?", should render as: "O\'Brien","He said, \"How are you?\"". However, for the life of me, I can’t find an escape function that just does this, nor can I make String.replace do the right thing. Any pointers?

Pete

1 Like
2

I would use the inspect/2 function on each of your args, it will wrap the string and escape embedded quotes.

EDIT: It will escape double quotes, not single. So not a perfect solution for one of your use cases.

Maybe:

value
|> inspect
|> String.replace("'", "\\'")

Will do the trick?

1 Like
3

What have you tried with String.replace?

Roughly this should do:

String.replace(orig, ~w[" '], &("\\#{&1}"), global: true)
1 Like
4

There’s a document that defines the text/csv format, RFC 4180

CSV is a notoriously flexible format, but the input you’ve described is not accepted by the CSV parser I have closest at hand (Numbers). The RFC specifies that double-quotes should be escaped as two double quotes "" and single-quotes aren’t escaped at all.

CSV is easy to get wrong in data-dependent ways; I recommend you use the CSV library.

4 Likes
6

(Fat fingered my first attempt at replying. Trying again.)

Both of the answers provided by @kip and @NobbZ worked as requested. Thank you both. However, working as requested and working correctly are two different things. : The resulting CSV was not processable by Excel. Lending credence to @al2o3cr’s “here be dragons” message. Accordingly, I’m going to go the library route.

Also, this forum is awesome!

7

Just to follow up, I dropped in NimbleCSV and everything’s working like a champ. Thanks again.

1 Like