5 ms·

CSV has more problems than just quoting: one problem is that excel is so commonly used to open CSV files that various weirdnesses in excel have essentially beco

CSV has more problems than just quoting: one problem is that excel is so commonly used to open CSV files that various weirdnesses in excel have essentially become part of the CSV format. For example, if a value looks like a formula, then the only way to get excel to treat it as text is to put a single quote in front of it, "'=not a formula". This in turn means that its common for values in a CSV to begin with a single quote, but for that single quote to not be intended as part of the value. I think a decent format could be like CSV, but tab separated, where the header row is mandatory, and with explicit support for data types. Tabs and newlines (along with any other unicode character) can be included via backslash escaping. eg. (imagine the spaces are tabs) int:Id str:Name json:Attributes dyn:Other 1 John\tSmith {"age": 30} int:42 2 Bill {"age": 40} str:Hello Applications can use their own type specifiers (eg. "formula:") to avoid needing to introduce special rules. The value of a field (before interpretation) is the exact string written in the file. The type specifier indicates how that string should be interpreted as a typed value by the application. For example, the `str` type specifier says that backslash escape sequences should be converted to the corresponding characters when interpreting the value. This has the following properties: - Machine and human readable and writable. - Does not place artificial restrictions on values. - Can be extended as needed with new type specifiers. - Can be processed losslessly even if an application does not understand all type specifiers in use. - Is not ambiguous about whether data is part of a header or not. - Can store common data patterns (eg. all values in a column have the same type) efficiently without being restrictive (values can still have different types even in the same column). - Can easily jump to a line or field (newlines and tabs not allowed except as separators - they must be escaped when in a value). - Easy to remember.

prepend 4y ago
There’s lots of protocols like what you describe and I’ve written a few myself. But they are a pain to teach to other people. Csv isn’t perfect, but I’d rather live with its weaknesses than use anything else.
gnulinux 4y ago
I like the format description, thanks. Sounds like something I might implement some day for fun! The reason I still don't like this is because most of the time I'm sharing csv is to a jira ticket or an email, or to customer and customer insists it needs to be csv. When customer wants csv there is nothing I can do. But in other cases I see this CSV file. So it's visual clarity is important. CSV has all the problems you listed, but TSV (and similar, such as your format) has the short coming of relying on an invisible separator. And worse, the width of tab is undefined, undecided. I don't know what the best trade-of... Maybe we need better tooling so frontends show CSV/TSV et al as separated by cells.
- marcosdumay 4y ago
  When the customer wants csv, it's because they will open it on Excel, so anything about standardization or data quality is moot. They will have their data mangled in an application specific format. You need to export to csv. But that doesn't mean you can't use an actually usable format for anything else.
masklinn 4y ago
> - Can be extended as needed with new type specifiers. That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.
- Diggsey 4y ago
  This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false. There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applications being able to process records losslessly even if they don't understand the format specifier)
  - masklinn 4y ago
    > This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false. Except it's true. For a trivial example, json which does not offer these capabilities doesn't suffer from incompatibilities or security issues anywhere close to how much yaml does. Because a json file which is not POD requires a lot of additional documentation and code. > There's a reason the vast majority of file formats do have some capacity for extension. Yes, naïvety (if not outright brain damage): for a long long time it's been assumed that flexibility and extensibility were innocuous, and while we've known that was not at all the case for at least a generation, these ideas remain attractive nuisances. More modern formats have tended to stay away from these, and those few which have not (like, once again, yaml) are case studies in why they should have.

prepend 4y ago

There’s lots of protocols like what you describe and I’ve written a few myself. But they are a pain to teach to other people. Csv isn’t perfect, but I’d rather live with its weaknesses than use anything else.

gnulinux 4y ago

I like the format description, thanks. Sounds like something I might implement some day for fun! The reason I still don't like this is because most of the time I'm sharing csv is to a jira ticket or an email, or to customer and customer insists it needs to be csv. When customer wants csv there is nothing I can do. But in other cases I see this CSV file. So it's visual clarity is important. CSV has all the problems you listed, but TSV (and similar, such as your format) has the short coming of relying on an invisible separator. And worse, the width of tab is undefined, undecided. I don't know what the best trade-of... Maybe we need better tooling so frontends show CSV/TSV et al as separated by cells.

marcosdumay 4y ago
When the customer wants csv, it's because they will open it on Excel, so anything about standardization or data quality is moot. They will have their data mangled in an application specific format. You need to export to csv. But that doesn't mean you can't use an actually usable format for anything else.

masklinn 4y ago

> - Can be extended as needed with new type specifiers. That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.

Diggsey 4y ago
This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false. There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applications being able to process records losslessly even if they don't understand the format specifier)
- masklinn 4y ago
  > This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false. Except it's true. For a trivial example, json which does not offer these capabilities doesn't suffer from incompatibilities or security issues anywhere close to how much yaml does. Because a json file which is not POD requires a lot of additional documentation and code. > There's a reason the vast majority of file formats do have some capacity for extension. Yes, naïvety (if not outright brain damage): for a long long time it's been assumed that flexibility and extensibility were innocuous, and while we've known that was not at all the case for at least a generation, these ideas remain attractive nuisances. More modern formats have tended to stay away from these, and those few which have not (like, once again, yaml) are case studies in why they should have.