[deleted]
CleverCSV: A Drop-In Replacement for Python's CSV Module
21–30 of 44 posts
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#22The problem mostly with CSV beyond the lack of standards, character encodings, and lack of compression, inability to efficiently write or read, and problems handling multiline freeform text, is that ultimately the structure is inband with the data, which is what makes all problems probable.
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#23Earlier quoted context omitted.
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.
For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#24Earlier quoted context omitted.
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...
Blame the calculator...'misuse of Microsoft Excel'
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#25Earlier quoted context omitted.
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.
For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
Maybe the most common spreadsheet software should get it's act together and support this simple standard?
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#26> 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.
I wrote a Stack Overflow answer like this. Only Mac Excel had this issue, if I recall correctly.
https://stackoverflow.com/a/16766198
To be fair, the latest version of Excel has improved handling of CSV files immensely.
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#27The only issue I've ever had with python's CSV module is that it's slow. Is this any faster?
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#28Earlier quoted context omitted.
For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
Maybe the most common spreadsheet software should get it's act together and support this simple standard?
Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance.
If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#29Earlier quoted context omitted.
Maybe the most common spreadsheet software should get it's act together and support this simple standard?
Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance. If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
Its not just non-Anglo .. semicolon is often used because data often contains commas, and it’s just easier to swop your delimiter than encapsulate your fields properly ... even then it’s just a simple matter to use the alternative delimeter, or convert by a simple search and replace (once youve added encapuslation to the relevant fields of course).
Though CSV directly implies commas I find it helpful to consider it a synonym for “delimited text”. The specifics of the delimiter don’t bother me so much so long as the format is consistent.
Re: CleverCSV: A Drop-In Replacement for Python's CSV Module
#30> 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…
Sounds like good system testing to me!
EDIT or absence thereof