Support for CSV-formatted strings

[kenorb]

Examples which I should expect to work:

$ echo '"1,2,3"' | jq -R "@csv"
jq: error (at <stdin>:1): string ("\"1,2,3\"") cannot be csv-formatted, only array
$ echo '"1,2,3"' | jq -r "@csv"
jq: error (at <stdin>:1): string ("1,2,3") cannot be csv-formatted, only array
$ echo '"1,2,3"' | jq -r "@tsv"
jq: error (at <stdin>:1): string ("1,2,3") cannot be tsv-formatted, only array

This works fine:

$ echo '[1,2,3]' | jq -r "@csv"
1,2,3
$ echo '[1,2,3]' | jq -r "@tsv"
1	2	3

However, the idea is to convert CSV from the string, not from the array.

My version:

$ jq --version
jq-1.5

[pkoppstein]

1. Please read the jq documentation for @csv, the input of which must be a flat array.

2. Consider this variant of your first try:

   echo '"1,2,3"' | jq -r 'split(",") | @csv'
   "1","2","3"

3. For your reference, usage questions are best asked at stackoverflow.com with the jq tag: https://stackoverflow.com/questions/tagged/jq

[kenorb]

Does it mean the feature won't be implemented?

[pkoppstein]

Does it mean the feature won't be implemented?

I'm not sure what "feature" you mean, but as illustrated, it's trivially easy to convert a JSON string with embedded commas into valid CSV, and if you want the numbers in the JSON string to be recognized as such, you could modify the filter to: split(",") | map(tonumber? // .) | @csv.

In any case, to the best of my knowledge, there is no outstanding "enhancement request" under consideration for further changes to either @csv or @tsv.

[kenorb]

This for me doesn't work as expected as split removes quoted commas:

$ echo '1,"2a,2b",3' | jq -Rr '.|split(",")|@tsv'
1	"2a	2b"	3
$ echo '1,"2a,2b",3' | jq -Rr '.|split(",")|@csv'
"1","""2a","2b""","3"
$ echo '1,"2a,2b",3' | jq -Rr '.|split(",")|@text'
["1","\"2a","2b\"","3"]

So I know it's just more than just splitting the commas. I've checked already scan()/capture() as potential use, but the regex would be to complex.

I'm aware this workaround works:

$ echo '[1,"2a,2b",3]' | jq -r "@tsv"
1	2a,2b	3

but then the input is not a proper CSV input as expected, as brackets need to be added, so input requires additional external processing. So I believe processing for string could work similar way by supporting strings but without the [ and ] brackets, like:

$ echo '"1,\"2a,2b\",3"' | jq -r "@tsv"
jq: error (at <stdin>:1): string ("1,\"2a,2b\...) cannot be tsv-formatted, only array

If I understand filters correctly (such as @csv, @tsv) that are expecting input in this specific format, but the limitation is that 'the input must be an array'. It seems other inputs supports plain input, but these specific one does not support string format, so feature request is about supporting strings as described, but I may misunderstand what input means.

I understand jq is not a proper converter, but I think it would be a good feature to support input CSV in a string format as well. Related GH-645.

Another use case: Converting CSV to TSV. A lot of CLI tools can do this simple conversion, so why not to make jq better.

[pkoppstein]

1. Currently, the best way to get jq to read a CSV file is to use EITHER a CSV-to-TSV or CSV-to-json program that can pipe into jq, or perhaps to use a wrapper such as cq. For further details, see the jq FAQ: Can jq process CSV or TSV files?

2. There has been some discussion about adding pre-filters, but given the backlog, such additions may be a long time in coming to fruition, especially given the predisposition of the jq maintainers to avoid new comand-line options and bloat. That is, jq seeks to dovetail with other tools if available, which is the case with CSV, especially since jq can handle TSV input natively.

[borekb]

Just having a use case for this, going to note the results of my brief research:

• jq alone cannot really be used, many Stack Overflow examples use split(",") which doesn't really work if data contains escaped commas, see e.g. Support for CSV-formatted strings #1650 (comment).
• cq as suggested in the FAQ is relatively hard to get to, it's part of jqt which seems to be a small experimental project that doesn't even have binary releases on GitHub. The author states "jqt is developed under the Fedora Linux distribution, and a lot of portability issues are expected at this stage of development.".
• Googling "csv to json command line" yields a couple of npm packages and quick'n'dirty scripts around the web. Unlike with jq itself, one has to try several options, evaluate them, etc.

So I don't think that CSV scenario has, currently, a similarly good solution as e.g. YAML (which is very-well covered by yq).

[nicowilliams]

I'm unclear as to what you're asking for.

Is it that you want to be able to read CSV/TSV into arrays, or CSV/TSV and pass it through?

[nicowilliams]

I do think it'd be nice if jq supported more input formats that raw and JSON. Ideally I'd like jq to support CSV/TSV as inputs (with and without value quoting), and maybe even YAML and XML. But if this isn't what you're asking for then never mind.

[borekb]

@nicowilliams Yes, process CSV with jq, or as you say, generally more input formats.

[nicowilliams]

OK, thanks. We would definitely not use @ syntax for that -- that syntax has to be exclusively about output because otherwise we'd have confusing behavior, I think.

We'd have fromcsv and fromtsv builtins. In fact, you can code these in jq right now:

$ jq -cn '[range(5)]|@csv|split(",")'
["0","1","2","3","4"]

but that doesn't take into account quoting of string values. To handle that would require something more like a reduction-based parser:

    reduce (explode[]|[.]|implode) as $char ({result:[""],inquoted:false,nextisbackslashquoted:false};
        (.result|length|. -1) as $idx |
        if .inquoted and (.nextisbackslashquoted|not) and $char != "\"" then
            .result[$idx] += $char
        elif .inquoted and (.nextisbackslashquoted|not) and $char == "\"" then
            .inquoted = false # should remember that next $char must be comma (or no next $char cause EOL)
        elif .nextisbackslashquoted then
            .result[$idx] += $char
        elif .inquoted then
            .result[$idx] += $char
        elif $char == "," then
            .result += [""]
        elif .result[$idx]|length == 0 and $char == "\"" then
            .inquoted = true
        else
            .result[$idx] += $char
        end) | .result;

That needs a bit more work, but it does work:

$ jq -Rcn '
def fromcsv:
    reduce (explode[]|[.]|implode) as $char ({result:[""],inquoted:false,nextisbackslashquoted:false};
        (.result|length|. -1) as $idx |
        if .inquoted and (.nextisbackslashquoted|not) and $char != "\"" then
            .result[$idx] += $char
        elif .inquoted and (.nextisbackslashquoted|not) and $char == "\"" then
            .inquoted = false # should remember that next $char must be comma (or no next $char cause EOL)
        elif .nextisbackslashquoted then
            .result[$idx] += $char
        elif .inquoted then
            .result[$idx] += $char
        elif $char == "," then
            .result += [""]
        elif .result[$idx]|length == 0 and $char == "\"" then
            .inquoted = true
        else
            .result[$idx] += $char
        end) | .result; "\"a,b\",c,d e f,\"g,h i,j\""|fromcsv'
["a,b","c","d e f","g,h i,j"]
$ 

[pkoppstein]

That needs a bit more work, but it does work

Yes, it works well, except perhaps for the handling of embedded carriage-returns. It would at least be desirable for fromcsv to be able to act as the inverse of @csv.

Consider therefore:

$ jq -rn '["a\nb","c"] | @csv'
"a
b","c"

In my view, the above is correct, but that is largely immaterial, because whether it's correct or not, we see that there's an invertibility issue:

$ jq -rn '["a\nb","c"] | @csv' | jq -Rcf fromcsv.jq
["a"]
["b\"","c"]

[nicowilliams]

@pkoppstein Yes, that's one of the things I meant by "it needs a bit more work.

[pkoppstein]

@nicowilliams - Yes, I guessed as much, but there is so much confusion/disagreement/inconsistency about this particular point, I thought it would be worthwhile illustrating how well-behaved @csv actually is.

[nicowilliams]

@kenorb, @pkoppstein Here you go:

$ jq -cn '
def fromcsv:
    def initialize: {result:[""],ready:false,inquoted:false,nextisbackslashquoted:false};
    foreach (if endswith("\n") then . else . + "\n" end | explode[]|[.]|implode) as $char (initialize;
        (.result|length|. -1) as $idx |
        if .ready then initialize else . end |
        if .inquoted and (.nextisbackslashquoted|not) and $char != "\"" then
            .result[$idx] += $char
        elif .inquoted and (.nextisbackslashquoted|not) and $char == "\"" then
            .inquoted = false # should remember that next $char must be comma (or no next $char cause EOL)
        elif .nextisbackslashquoted then
            .result[$idx] += $char
        elif .inquoted then
            .result[$idx] += $char
        elif $char == "," then
            .result += [""]
        elif $char == "\n" then
            .ready = true
        elif .result[$idx]|length == 0 and $char == "\"" then
            .inquoted = true
        else
            .result[$idx] += $char
        end;
        if .ready then .result else empty end); "\"a,\nb\",c,d e f,\"g,h i,j\"\n\"a,z\"\n"|fromcsv'
["a,\nb","c","d e f","g,h i,j"]
["a,z"]
$ 

The foreach adds a newline to the input if it doesn't already have it, then iterates over every character in the input. The update expression does all the parsing. The extract expression extracts complete outputs.

You can check that fromcsv consumes the outputs of @csv. Mind you, I've still not thought it entirely through, so there may well be a bug in there.

This pattern can be extended to parse things like XML too if you like (but, wow, that might be a lot of work!).