5 ms·

I think the most fun I had parsing a CSV file was when I found out it switched from UTF8 to UTF16 after several thousand lines, as differently encoded CSV files

I think the most fun I had parsing a CSV file was when I found out it switched from UTF8 to UTF16 after several thousand lines, as differently encoded CSV files had been concatenated together directly into a single file...

tonyedgecombe 6y ago
I've seen some terrible CSV files where people tried to squeeze hierarchical data into a flat file format. Usually the customer can't provide any sort of schema or documentation because if they could they would have recognised CSV isn't appropriate. This means you are in an endless cycle of fixing the code as unexpected items keep appearing in the data.
- jointpdf 6y ago
  I tend to think of dataframes (R, Python pandas) as the standard way for representing and working with the data contained in a .csv. “List columns” (https://jennybc.github.io/purrr-tutorial/ls13_list-columns.html https://jennybc.github.io/purrr-tutorial/ls13_list-columns.h...) inside dataframes can actually be a convenient way to represent data that is mostly flat, but has some hierarchical data associates it (recent example: dataframe representing a corpus of documents, with a column containing a list of citations). So while .csv probably isn’t the best format to store hierarchical data, it’s fairly trivial to work with if encountered.
GolDDranks 6y ago
I have a similar story: parsing a CSV file that had an unexpected 0x1A byte in the middle. Turns out the file was concatenated together in multiple passes from multiple files using the COPY DOS command, which by default adds ASCII SUB to the end as an EOF marker: https://en.wikipedia.org/wiki/Substitute_character https://en.wikipedia.org/wiki/Substitute_character
- SanchoPanda 6y ago
  I have done this to myself in the past. Those were....dark days.
  - GolDDranks 6y ago
    The sad thing is that this happened last year.
    - oefrha 6y ago
      You don't say, someone sent a CSV like that to me yesterday...
usrusr 6y ago
When you hit that kind of data which isn't quite a corner case anymore but already in the realm of subtly broken, you'll actually be happy to have written your own ad-hoc CSV implementation. Figuring out that problem will be much harder if, because programming is scary, you are using a black box library. Proper CSV handling is simple, just don't try to take shortcuts to make it even simpler than it is. There just happens to be a lot of CSV around that is too broken to be read without a bit of handholding, but no gold standard library will be able take that problem out of your hands (an argument might be made for libraries on the producer side of files). That little list of pitfalls in the article would be a good guideline for "things you should know before" but it doesn't make a particularly compelling point against rolling your own. Personally I'd even argue that CSV is the only format where, on the consumer side, rolling your own is actually advisable. Because every beyond-corner-case is different and the format is such fertile soil for imperfect as-hoc processing that happens outside of your code.
jaxx75 6y ago
Try a UTF8 CSV with Windows-1252 free-form text in some fields. I believe dealing with character encoding is the most frustrating thing a developer can do.
- recursive 6y ago
  > I believe dealing with character encoding is the most frustrating thing a developer can do. I'll take your encoding work if you take my timezone work.

tonyedgecombe 6y ago

I've seen some terrible CSV files where people tried to squeeze hierarchical data into a flat file format. Usually the customer can't provide any sort of schema or documentation because if they could they would have recognised CSV isn't appropriate. This means you are in an endless cycle of fixing the code as unexpected items keep appearing in the data.

jointpdf 6y ago
I tend to think of dataframes (R, Python pandas) as the standard way for representing and working with the data contained in a .csv. “List columns” (https://jennybc.github.io/purrr-tutorial/ls13_list-columns.html https://jennybc.github.io/purrr-tutorial/ls13_list-columns.h...) inside dataframes can actually be a convenient way to represent data that is mostly flat, but has some hierarchical data associates it (recent example: dataframe representing a corpus of documents, with a column containing a list of citations). So while .csv probably isn’t the best format to store hierarchical data, it’s fairly trivial to work with if encountered.

GolDDranks 6y ago

I have a similar story: parsing a CSV file that had an unexpected 0x1A byte in the middle. Turns out the file was concatenated together in multiple passes from multiple files using the COPY DOS command, which by default adds ASCII SUB to the end as an EOF marker: https://en.wikipedia.org/wiki/Substitute_character https://en.wikipedia.org/wiki/Substitute_character

SanchoPanda 6y ago
I have done this to myself in the past. Those were....dark days.
- GolDDranks 6y ago
  The sad thing is that this happened last year.
  - oefrha 6y ago
    You don't say, someone sent a CSV like that to me yesterday...

usrusr 6y ago

When you hit that kind of data which isn't quite a corner case anymore but already in the realm of subtly broken, you'll actually be happy to have written your own ad-hoc CSV implementation. Figuring out that problem will be much harder if, because programming is scary, you are using a black box library. Proper CSV handling is simple, just don't try to take shortcuts to make it even simpler than it is. There just happens to be a lot of CSV around that is too broken to be read without a bit of handholding, but no gold standard library will be able take that problem out of your hands (an argument might be made for libraries on the producer side of files). That little list of pitfalls in the article would be a good guideline for "things you should know before" but it doesn't make a particularly compelling point against rolling your own. Personally I'd even argue that CSV is the only format where, on the consumer side, rolling your own is actually advisable. Because every beyond-corner-case is different and the format is such fertile soil for imperfect as-hoc processing that happens outside of your code.

jaxx75 6y ago

Try a UTF8 CSV with Windows-1252 free-form text in some fields. I believe dealing with character encoding is the most frustrating thing a developer can do.

recursive 6y ago
> I believe dealing with character encoding is the most frustrating thing a developer can do. I'll take your encoding work if you take my timezone work.