I have a use case where a customer needs to load JSON-serialized objects via a CSV import. Some of these objects contain strings which contain double-quotes. Typically I would simply add a '\' before the nested double-quote in order to escape it, however this seems to conflict with the parsing of the CSV file. We're using PHP 7.0 and the function "fgetcsv" to read the lines of the file. Whenever I do this I notice odd behavior after an escaped double-quote is encountered. Here's a sample row from the CSV:
"{""test"": ""\""this\"" is a test""}"
And here is how PHP reads this column using fgetcsv:
{"test": "\"this\"" is a test""}"
I have confirmed any double-quotes after the initial escaped double-quote run into this problem. Thinking the backslash may be causing issues with escaping I tried using another backslash to escape the backslash:
"{""test"": ""\\""this\\"" is a test""}"
And here's the result:
{"test": "\\"this\\" is a test"}
So while this does resolve the issue with any double-quotes beyond the first, I am left with two backslashes instead of one.
Without changing the underlying code, is there a way to escape this data so that fgetcsv will interpret it appropriately? Like so:
{"test": "\"this\" is a test"}
You could try using \" to represent a double quote instead of "".
E.g., "{\"test\": \"\\\"this\\\" is a test\"}"
Whether this works may depend on the version of fgetcsv you are using -- I'm not sure.
Alternatively, if you're using fgetcsv 5.3 or later, you could try changing the fgetcsv parameters to change the enclosure character or escape character so that it doesn't conflict with JSON. See the parameters in the fgetcsv docs.
enclosure
The optional enclosure parameter sets the field enclosure character (one character only).
escape
The optional escape parameter sets the escape character (one character only).
Note: Usually an enclosure character is escaped inside a field by doubling it; however, the escape character can be used as an alternative. So for the default parameter values "" and \" have the same meaning. Other than allowing to escape the enclosure character the escape character has no special meaning; it isn't even meant to escape itself.
(emphasis in original)