4 ms·

CSV as serialization format? Ouch. Could we do better? My experience with CSV has been nothing but pain in the past. Ambiguous formats, quoting issues, incompat

by jakubp 9y ago

CSV as serialization format? Ouch. Could we do better? My experience with CSV has been nothing but pain in the past. Ambiguous formats, quoting issues, incompatible libraries between languages and popular GUI tools like Excel or data vis apps. I wonder if there's anything better.

mangecoeur 9y ago
The point of the format is that you get json metadata to avoid the ambiguous issues (and it also has requirements on the csv format to use, although whether people will follow them is a different matter). On the other hand, I used their python API a bit and for loading tables it's way too complicated, with the documentation going into great detail for faffing with metadata but not for actual loading (and nothing like a simple `read_datatable` function). That said, because it's just a folder with CSVs you can just read them individually, although then there's nothing to take advantage of the metadata automaticall.
- _pwalsh 9y ago
  (I work on the Frictionless Data specifications and tooling at Open Knowledge International.) Point taken about the API. The Data Package [1] and Table Schema [2] libraries are generally designed as low-level libraries for building higher-level applications using the specifications. goodtables-py [3] is an example of a higher-level application built on top. But, point taken, we will look at it, and we'd welcome your feedback on the issue tracker [4]. [1]: https://github.com/frictionlessdata/datapackage-py/issues https://github.com/frictionlessdata/datapackage-py/issues [2]: https://github.com/frictionlessdata/tableschema-py/issues https://github.com/frictionlessdata/tableschema-py/issues [3]: https://github.com/frictionlessdata/goodtables-py/issues https://github.com/frictionlessdata/goodtables-py/issues [4]: https://github.com/frictionlessdata/datapackage-py/issues https://github.com/frictionlessdata/datapackage-py/issues
slowmotiony 9y ago
I hate csv so much. Simply opening a csv in Excel and saving it breaks the format so much that most parsers cannot understand it anymore. Plus there is no way to know which encoding the csv came in - Notepad++ might say one thing and PowerShell will say another. Have fun figuring out why cyrrilic characters or umlauts cause issues in your scripts...
- carlps 9y ago
  I think the excel issue is a problem with excel, not csv. Why can't excel preserve the format of the file?
- jarman 9y ago
  Problem is not CSV, it's Excel. It's hard to blame format, when application is insane enough to use locale settings to determine file parsing
  - deleted 9y ago
    [deleted]
- jarman 9y ago
  >why cyrrilic characters or umlauts cause issues in your scripts... Any text format can be broken by broken character encoding. I saw plenty of XML being used without any charset declarations. And JSON is in same position as CSV.
vitorbaptistaa 9y ago
Although there're multiple issues with CSV, it's the de facto standard for tabular data. We try to avoid its pitfalls by limiting how the CSV must be (UTF-8, header in the first line, etc.) There's a brief explanation on why CSV was selected on https://specs.frictionlessdata.io/tabular-data-package/#why-csv https://specs.frictionlessdata.io/tabular-data-package/#why-...
_pwalsh 9y ago
Hi, (I work on the Frictionless Data specifications and tooling at Open Knowledge International.) CSV has many, many warts. However, it is the best thing we have right now for serialising data in a way that is easily read by humans (and consumer-grade software) and machines. Libraries like our Tabulator [1] which is used under-the-hood help provide an API to deal with many of the gotcha's when dealing with the format. [1]: https://github.com/frictionlessdata/tabulator-py https://github.com/frictionlessdata/tabulator-py
- jakubp 9y ago
  Thanks, will have a look at tabulator. I appreciate a list of validators you published on FD site, can help at least a bit when working with non-techies to vet their data before submission.
  - curragh 9y ago
    Not on the list is ETLyte (https://sorrell.github.io/etlyte/ https://sorrell.github.io/etlyte/), which I built so that an insurance company's corporate clients could vet their flatfiles before submitting them - it has worked very well across multiple files, with custom validations, and is very speedy (uses SQLite). As far as "non-techies" go, it's pretty straightforward, but confined to the command line, so I guess I need to get working on a web frontend for this :)
BerislavLopac 9y ago
Avro?

by jakubp 9y ago

mangecoeur 9y ago

The point of the format is that you get json metadata to avoid the ambiguous issues (and it also has requirements on the csv format to use, although whether people will follow them is a different matter). On the other hand, I used their python API a bit and for loading tables it's way too complicated, with the documentation going into great detail for faffing with metadata but not for actual loading (and nothing like a simple `read_datatable` function). That said, because it's just a folder with CSVs you can just read them individually, although then there's nothing to take advantage of the metadata automaticall.

_pwalsh 9y ago
(I work on the Frictionless Data specifications and tooling at Open Knowledge International.) Point taken about the API. The Data Package [1] and Table Schema [2] libraries are generally designed as low-level libraries for building higher-level applications using the specifications. goodtables-py [3] is an example of a higher-level application built on top. But, point taken, we will look at it, and we'd welcome your feedback on the issue tracker [4]. [1]: https://github.com/frictionlessdata/datapackage-py/issues https://github.com/frictionlessdata/datapackage-py/issues [2]: https://github.com/frictionlessdata/tableschema-py/issues https://github.com/frictionlessdata/tableschema-py/issues [3]: https://github.com/frictionlessdata/goodtables-py/issues https://github.com/frictionlessdata/goodtables-py/issues [4]: https://github.com/frictionlessdata/datapackage-py/issues https://github.com/frictionlessdata/datapackage-py/issues

slowmotiony 9y ago

I hate csv so much. Simply opening a csv in Excel and saving it breaks the format so much that most parsers cannot understand it anymore. Plus there is no way to know which encoding the csv came in - Notepad++ might say one thing and PowerShell will say another. Have fun figuring out why cyrrilic characters or umlauts cause issues in your scripts...

carlps 9y ago
I think the excel issue is a problem with excel, not csv. Why can't excel preserve the format of the file?
jarman 9y ago
Problem is not CSV, it's Excel. It's hard to blame format, when application is insane enough to use locale settings to determine file parsing
- deleted 9y ago
  [deleted]
jarman 9y ago
>why cyrrilic characters or umlauts cause issues in your scripts... Any text format can be broken by broken character encoding. I saw plenty of XML being used without any charset declarations. And JSON is in same position as CSV.

vitorbaptistaa 9y ago

Although there're multiple issues with CSV, it's the de facto standard for tabular data. We try to avoid its pitfalls by limiting how the CSV must be (UTF-8, header in the first line, etc.) There's a brief explanation on why CSV was selected on https://specs.frictionlessdata.io/tabular-data-package/#why-csv https://specs.frictionlessdata.io/tabular-data-package/#why-...

_pwalsh 9y ago

Hi, (I work on the Frictionless Data specifications and tooling at Open Knowledge International.) CSV has many, many warts. However, it is the best thing we have right now for serialising data in a way that is easily read by humans (and consumer-grade software) and machines. Libraries like our Tabulator [1] which is used under-the-hood help provide an API to deal with many of the gotcha's when dealing with the format. [1]: https://github.com/frictionlessdata/tabulator-py https://github.com/frictionlessdata/tabulator-py

jakubp 9y ago
Thanks, will have a look at tabulator. I appreciate a list of validators you published on FD site, can help at least a bit when working with non-techies to vet their data before submission.
- curragh 9y ago
  Not on the list is ETLyte (https://sorrell.github.io/etlyte/ https://sorrell.github.io/etlyte/), which I built so that an insurance company's corporate clients could vet their flatfiles before submitting them - it has worked very well across multiple files, with custom validations, and is very speedy (uses SQLite). As far as "non-techies" go, it's pretty straightforward, but confined to the command line, so I guess I need to get working on a web frontend for this :)

BerislavLopac 9y ago

Avro?