> 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…
CleverCSV: A Drop-In Replacement for Python's CSV Module
11–20 of 44 posts
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#12> 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
#13> 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…
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> 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…
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#15> 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…
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#16Earlier 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)
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#17Earlier 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:
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> 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…
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#19This 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.
[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
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#20> 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.
Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.