Live data from Hacker News

CleverCSV: A Drop-In Replacement for Python's CSV Module

github.com

11–20 of 44 posts

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#11
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#12
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.

One in five genetics papers contains errors thanks to Microsoft Excel https://genomebiology.biomedcentral.com/articles/10.1186/s13...

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#13
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas".

Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv format that has more columns than expected and everything from the that field onwards was off by one. Most teams were lucky, their code barfed when presented with a line with too many fields. Some teams were unlucky and it didn't. How unlucky _they_ were was somewhat varied based on how much data validation they did.

That caused a big headache for a lot of people trying to recover from the actions of the genius who thought doing that against the production endpoint vs the test endpoint.

That incident pretty much solidified my reputation in the team as some kind of weird voodoo type of chaos monkey, because my shifts _always_ had a significantly higher sev2 rate caused by things failing that I'd not touched or had any influence over.

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#14
post #13
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv…

Better an issue raised by a engineer then that same issue caused by an end user :shrug:

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#15
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file with Unicode in it - regardless of whatever ancient version and OS they're using - is to use tab separators and encode as UTF-16LE with a leading BOM.

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#16
post #8
post #4

Earlier quoted context omitted.

Pandas uses the Python csv module under the hood. Most of the code in the read_csv method is used to get the data from the csv file and load it into data frames.

Are you sure? Wes McKinney rewrote the Pandas CSV parser way back in late 2012 because the batteries-included version was slow and memory-hungry. Did Python upstream his version later on, or replace it with something faster (I'd be surprised if they did either, but I'd be curious to know)

It's configurable. The default parser is written in C, but it's possible to use a parser based on the csv module if you needs things like separators specified by a regex.

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#17
post #14
post #13

Earlier quoted context omitted.

At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv…

Better an issue raised by a engineer then that same issue caused by an end user :shrug:

The engineer technically was an end user. It was a service that could get called as an indirect result of customer action, but without any customer supplied information passed along, which is part of the reason it didn't do any input clean-up (at least according to the post-incident report). They assumed they could trust people not to screw things up.

To be fair, it had been running for a long time that way without anyone doing that to them.

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#18
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

Funny enough, this is the exact problem I had today at work. The easiest way to fix this problem is to add a tab character (“\t”) somewhere within the value (most likely at the end) and Excel will not format it. For example: “value\t”. Another way is “=“”value”””

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#19

This seems kind of philosophically similar to FTFY, https://github.com/LuminosoInsight/python-ftfy , except for CSV files instead of botched text strings.

I knew of ftfy already but I would love some more examples of these kinds of data cleanup libraries; it's always super useful.

Not in any particular order, but here is a list of really useful text butchering utilities, that I keep in the same grab-bag as ftfy: csvclean[1], unidecode[2], transliterate[3], charset-normalizer[4], cchardet[5], phonenumbers[6][7], iconv[8], enca/enconv[9].

[1] https://csvkit.readthedocs.io/en/1.0.3/scripts/csvclean.html

[2] https://pypi.org/project/Unidecode/

[3] https://pypi.org/project/transliterate/

[4] https://pypi.org/project/charset-normalizer/

[5] https://pypi.org/project/cchardet/

[6] https://pypi.org/project/phonenumbers/

[7] which is a python port of Google's libphonenumber: https://github.com/google/libphonenumber

[8] https://linux.die.net/man/1/iconv

[9] https://linux.die.net/man/1/enca

Re: CleverCSV: A Drop-In Replacement for Python's CSV Module

#20
post #15
post #9

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes…

I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file with Unicode in it - regardless of whatever ancient version and OS they're using - is to use tab separators and encode as UTF-16LE with a leading BOM.

It isn't just Unicode, Excel has been like this for every version I can remember, and I've used v5.0, possibly earlier versions (1992/93).

Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.

Post reply on HN