how to have csvq treating double quotation marks just as normal characters?

[redongh]

given a file test.csv containing

c0,c1
aaa,000
bbb,111
"cc,222
ddd,333

and performing a simple select SELECT * FROM test; on it yields a data parse error (extraneous " in field) for line 6 (which doesn't exist btw.) and i wonder if this behaviour (interpretation of double quotation marks) can be turned of, so all characters (besides the delimiters) at any positions are just treated as any other ordinary character.

i checked the flags description and thought that @@ANSI_QUOTES might have interfered but since csvq 'SHOW @@ANSI_QUOTES;' returns a false, i assume this is turned off by default and therefore should not cause any issues here?

if the double quotation character is put to the middle position on that line (e.g. c"c,222) the query runs fine but weird things happen to that first columns entry as soon as the table is altered (e.g. csvq 'ALTER TABLE test ADD c2 LAST;').
i was actually about to report that as an issue but then decided to first ask if i might be missing something in regards to the configuration possibilities of the "-interpretation...

[mithrandie]

There is no way to make the quotation mark at the beginning of a field be interpreted as part of the value.
The effect of the @@ANSI_QUOTES flag is applies to executable statements, not to data to be read.

CSV data are read according to RFC 4180.
Therefore, fields containing double quotation marks must be enclosed with double quotation marks and escaped.

6. Fields containing line breaks (CRLF), double quotes, and commas
   should be enclosed in double-quotes. For example:

  "aaa","b CRLF
  bb","ccc" CRLF
  zzz,yyy,xxx

7. If double-quotes are used to enclose fields, then a double-quote
   appearing inside a field must be escaped by preceding it with
   another double quote. For example:

   "aaa","b""bb","ccc"

In csvq, if there are double quotation marks in the middle of an unenclosed field, they are interpreted as part of the field value and read as is, but if the field begins with a double quotation mark, it is interpreted as an enclosure.
Furthermore, if you attempt to update a file with values containing double quotation marks, those values will be written to the file in an enclosed and escaped form.

[redongh]

Thanks for that concise answer which helped me a lot to better understand how csvq works.

While playing around with this I also found that fixed width data is treated differently:
No escaped double quotation marks are expected in this case and all data is interpreted as raw meaning if there are already escaped double quotation marks like described in RFC4180, they will be escaped again by csvq when exporting in CSV/TSV!

That is of course in line with what you stated above but might be worth noting when deciding how to read data into csvq.