"POSSIBLE DATA LOSS" (Excel reports, when you save a CSV)
Yeah, I get it... the formatting goes away... why so alarmist? And is the formatting really "data" anyway?
Syndicated from the fediverse. Read and engage on the original instance.
View original on lemmy.caView original on lemmy.ca
7 replies
It loses a lot more than just formatting. Formulas, any graphics, graphs/charts, it only saves the active sheet. And formatting can be super important with date values.
Because not all CSV are comma delineated values, annoyingly my bank uses colon delineated values because commas are used as decimal divider in my part of the world.
And as excel doesn't give a shit about non US formated stuff it always leads to issues.
That is interesting. It’s been a llng time since I’ve used Excel or CSV, but I could’ve awore that you just needed to set some option to quote everything and that would allow commas in the data; i.e.
“One, two, and three”,”something else”,”1.320,42”I’m a web developer that lets people export to CSV and quoting things is usually the answer. Though for a number with a comma in it I’d rather not quote it and leave it as a bare integer/decimal so that the data type is correctly inferred.
Speaking of, I have a handful of hacks I do specifically for Excel so that it infers types correctly — it needs more prodding than other applications importing CSV.
Yeah, CSV is an archaic data format that lacks a lot of important nuances; like understanding that not everything is done the same way everywhere around the world.
I guess one viable alternative would be to use tabs for delimiting the data, since (afaik) tabs are not commonly found in normal spreadsheet data (I admit that I could be wrong).
I’ve messed with both. But yeah, keeping an international mind is best. Separators are really unnecessary for data transfer so I usually just exclude them.
One quirk I remember for Excel to recognize a date it has to be
YYYY-MM-DD 12:00 PMbecause it doesn’t understand ISO-8601. 😓At my job IT would regularly advise employees to open "CSV" files with Libre Office because it nicely asks you upfront how it should be handled. With sane defaults and easy to understand adjustments.
Excel just assumes stuff and fuck you, if you think otherwise. I think theoretically you can get Excel to import CSVs created under different standards but it's burried somewhere in submenus. And good luck if the file has different encoding than your OS.