feature request: support for semicolons as "CSV" separator

[mbunkus]

Today I discovered jaq absolutely love the rationale behind it & its feature set. I really hoped to be able to use it for a lot of my CSV work for which I'm usually whipping up small custom scripts in Perl or Python.

Unfortunately I'm dealing with a lot of files that use semicolons instead of commas or tab characters as the field separator, as a lot of programs in my vicinity produce them that way. jaq doesn't seem to support those, at least according to my tests & study of the (well-written) jaq documentation. Would it be possible to implement support for them? A dedicated field separator option would be more than enough for me, but maybe even attempting auto-detection on the first line of the file if the input format is CSV (either via the file extension or --from.

Thanks for considering.

[01mf02]

Hi @mbunkus, thanks for your kind words. :)

Can you tell me which programs in your vicinity produce these files? I'm trying to stick to standards as much as possible, to avoid an ever-growing number of format-specific options. And the CSV RFC is quite clear on the separator: it's a comma.

That being said, perhaps cat file.csv | tr ';' '\t' | jaq --from tsv ... does the trick?

[mbunkus]

Well, there are several industry-specific programs that do so, several of my customers' self-written reporting tools do, as do some of the ones that I use privately, e.g. for finances (and I think one of my banks produces statements with ;). And don't forget the biggest one of them all, Microsoft Excel. Just to confirm that I'm not talking out of my behind I asked a colleague to use his Excel to produce a very simple spreadsheet & save it as CSV, which resulted in the following:

muh;eins,5;1,5;eins,fünf
mäh;5,1;123;456

Please note that at least for Excel the default cell separator depends on the selected locale (!). This is with German locale; with English locale it seems to default to , — probably because in German locale the , itself is used as the decimal point (as in, 1.234,45 € vs 1,234.45 $ one would use in e.g. the USA).

Of course I can use tr, but only if I'm 100% sure there are no ; in the cells themselves. I also get wanting to adhere to standards, been there, done that, got the customer complaints to prove it (I have written several pieces of software that had to support all kinds of weird variants both for reading & for producing CSV over the years). Personally I'd really like jaq to be able to handle at least the most common ones out of the box, at least on the input side. To me this seems to be one of jaq's strong points, having support for those very common formats instead of only JSON. I don't care that much about writing CSVs with ; as the separator, though, but it would be nice to have, too.

[mbunkus]

Eh, that CSV copy-paste from Excel is probably confusing to read. To make it clear, the table looked like this:

| muh | eins,5 | 1,5 | eins,fünf |
| mäh | 5,1    | 123 | 456       |

[01mf02]

@mbunkus, I hear you. Let's suppose that we would make the separator character configurable. How would you set it? Something like --from csv:sep=";"? That means than when parsing command-line arguments, we would need to keep a map from parameter names to parameter values, such as {"sep": ";"}, and validate them, before storing them as field in Format::Csv. That's a considerable change. Would you be willing to do it? I am currently not very motivated to make such a change myself, but I would review a PR.

In a next step, to keep the features consistent, this would also imply supporting the same in --to, as well as something like fromcsv({sep: ";"}) plus the same for tocsv. To keep all this stuff in sync will require some thought.

(For reference, yq had precisely the same feature request two years ago: mikefarah/yq#1950. They went with --csv-separator as separate flag, but I find this not very satisfying, because it is not clear whether this applies to input, output, or both. What if you want to convert from one representation to another?)

[mbunkus]

I don't have thaaaat much experience writing Rust. Definitely more than zero, two or three smaller programs implementing stuff at $work. Not sure if I feel up to implementing a CLI parser extension — but I'm willing to take a look at some point over the next several weeks (pretty busy with stuff at the moment).

On the other hand I totally understand that you're not keen to invest the time either.

So maybe just keep the issue open for the time being and see if/when I can make some progress?

About how to implement it: I had thought about that as well before posting the issue & suggesting some kind of CLI option/configurability. I don't like an option named specifically after CSV either (so --csv-separator is out), on the other hand, having sub-options requires a bit more thought about its structure (separator or sub-options? Probably a comma or a space. Escape character? Probably a backslash.), though I've done that plenty in the past. The design isn't a problem for me, implementing it in idiomatic Rust is where I lack the experience.

[01mf02]

I have to also say that I'm really pretty divided on this issue. In particular, I fear that having configuration options for data formats opens the Pandora's box for all kind of syntax variations, where it will be hard to draw the line anywhere. And so far, I have always been happy in the long run to stick with official standards as much as possible.

Furthermore, I recently opened a CSV file with vim and noticed that it had quite nice syntax highlighting:

However, I also found that using ; as separator breaks that syntax highlighting. This is a bit anecdotal, but this shows to me that using non-standard CSV will break down not only for jaq, but also for many other tools. And when I ask myself where I want to be in this, I would rather not incentivize using tools that produce invalid CSV, but rather encourage people to use tools that produce correct CSV.

I think that the cleanest way to proceed would be to write a small program that converts from ";-CSV" to actual CSV, and then use jaq on that. You could probably even make an alias if you are using this often enough that writing a pipe bothers you. I understand that tr probably does not cut it always, but I think that the use of a tiny bit of Python would be enough to do what you want. Heck, this sounds like a perfect case for LLMs. A simple, boring script that has a well-defined scope and can be easily audited. :)

Now you piqued my curiosity. I tried it with ChatGPT:

Write me a Python script that converts from semicolon-separated CSV (as produced by Excel) to valid (comma-separated) CSV. It should read its input from stdin, and write to stdout.

It gave me something like this --- I made it a bit shorter myself:

#!/usr/bin/env python3
import sys
import csv

for row in csv.reader(sys.stdin, delimiter=';'):
    csv.writer(sys.stdout, delimiter=',', lineterminator='\n').writerow(row)

Let's try it:

$ cat bla.ssv 
muh;eins,5;1,5;eins,fünf
mäh;5,1;123;456
$ ./ssv.py < bla.ssv 
muh,"eins,5","1,5","eins,fünf"
mäh,"5,1",123,456

That looks good. I would use that.

[mbunkus]

I get all of your arguments. I do not have a good answer or rebuttal on the whole "customizable" & "where's the line" topic.

The only thing I'd like to push back a bit is "but rather encourage people to use tools that produce correct CSV": with most sources I simply don't have a choice in tools. I cannot tell my bank to produce standards-compliant CSV for bank statements. Nor can I tell my customers not to use Excel with a German locale.

I think that the cleanest way to proceed would be to write a small program that converts from ";-CSV" to actual CSV

Yep, that's what I've done already, including a shell alias. Just that I write those things in Perl and that I've written so many CSV-handling Perl scripts that I can write those in my sleep 😂

Anyway, thanks for the rationale, and feel free to close the issue if you don't want to go down the customization route.

PS: an anecdote. Stop reading if not interested 😁 I'm actually in a very similar boat with the OSS program I've been developing & maintaining for more than two decades now (MKVToolNix), a tool that reads multiple multimedia formats (think MP4, AVI, MPEG TS/PS, Matroska, Ogg etc. etc.) & re-packages them into Matroska files among other things. I'm dealing with a lot of file formats for which there are formal standards/specs by organizations such as ISO, IEC or private ones such as Xiph for Ogg/Vorbis/Opus etc. The output side has specs that I work on myself with others.

Therefore I'm very much in the same boat in that I have to adjust MKVToolNix to adhere to specs both on the input & the output side while at the same time dealing with source files that are often produced by tools that do not adhere to the specs 100% — or even worse, that contain data for which there simply is no explicit spec (e.g. when a new codec comes out & there's no formal spec yet but tools package that codec in e.g. MP4 anyway). Users expect that my tools can read such data as well, and I usually acquiesce, at least on the input side as the users of those pieces of media usually do not have a choice in how that media was created.

On the output side I also receive a lot of requests to adjust MKVToolNix to produce Matroska files that don't use feature X or do feature Y differently because they have difficulty with playback on hardware device A or with software player B. Such requests I usually decline as that way lies madness: I cannot produce one format by default that works on all systems out there. The only sane thing for me to do is to write (output) files that adhere to the Matroska specs. It's up to the developers of devices & software reading those files to handle spec-compliant files. I sometimes do add optional workarounds gated behind various CLI options, though, especially if a certain bad behavior is somewhat wide-spread — but I never make it the default.

All that to say, I try to adhere to the principal of "be liberal in what you accept and strict in what you produce" (or however that saying goes). Again, not trying to put pressure on you here; just saying that I really get what you're struggling with here.