questdb / questdb/questdb

REST API /exp CSV escaping incompatible with MS Excel

Open
#2,004 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Bug REST API
Dominant language
Java
Stars
17.3k
Forks
1.6k
Avg merge
5d 10h
Merged PRs (30d)
28

Description

Describe the bug

If string/symbol columns tabs or new-line characters these are escaped in a manner that is similar to JSON escaping.

Today:

curl -G --data-urlencode "query=select * from table5 limit 5" http://localhost:9000/exp
"a","b","c"
10.0,"a\"\nb\rc\r\nd\"' 3",
20.5," a\t",
,,x

Our current escaping isn't compatible with MS Excel.

What we want:

For point of comparison, we should do whatever the csv module in Python's standard library does as it mirrors Excel closely from a compatibility point of view.

>>> import csv
>>> data = [['double-quote', 'new line', 'tab', 'windows new line'],['"', '\n', '\t', '\r\n']]
>>> import io
>>> output = io.StringIO()
>>> csv.writer(output, dialect='excel').writerows(data)
>>> print(output.getvalue())
double-quote,new line,tab,windows new line
"""","
",      ,"
"

We could also consider avoiding double-quoting strings unless required to avoid bloat.

To reproduce
  1. Insert some data with new lines, windows new lines, tabs, etc.
  2. Query it from /exp (or just via the web console as CSV).
  3. Observe incompatible escaping.
Expected Behavior

Once fixed, the output - saved as a .csv file - should open cleanly in excel, including multi-value strings.

Environment
- **QuestDB version**: 6.2.1
- **OS**: all
- **Browser**: any
Additional context

No response

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at the REST API /exp CSV export and the web console CSV path described in the reproduction steps. Compare its escaping with Python's csv.writer using the excel dialect, then verify that exported files open cleanly in Excel with quotes, tabs, and newline-containing values preserved.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
api, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.