Addressing both commas and double quotes in a CSV

4.2k views Asked by At

I am writing a report in SQR which produces a CSV output file. While testing, I ran into an instance where the following string is used in one of my fields:

TABLE - 210" X 60" X 29", OAK,

I currently have double quotes surrounding the string. When I use this method, the output produced is this:

Col 1 |Col 2|Col 3

TABLE - 210 X 60" X 29" | OAK | ,

Obviously, the entire line should fit in the first column.

I understand that it's possible to use other delimiters, but would prefer to keep a comma delimiter. Does anyone know of a way to address this issue?

Here is a simplified version of current code:

Let $asset_descr = '"' || &dep_asset.descr || '"'
Let $string = $asset_descr
Write 1 from $string 
1

There are 1 answers

1
friedpikmin On BEST ANSWER

I was able to finally solve this after A LOT of searching and testing. In order to fix this, I simply replaced each double-quote with two double-quotes. Having two double-quotes treats the double-quote as an 'escape value'... which I guess means that the double-quote is not treated like a closed quote.

This might be a SQR specific solution.

Simplified code solution:

Let $asset_descr = '"' || Replace(&dep_asset.descr, '"', '""') || '"'
Let $string = $asset_descr
Write 1 from $string