5 ms·
Excel does an atrocious job of handling CSV files. It regularly alters data, messes up encodings and either can't or couldn't (I haven't checked in a few years)
by IanCal 5y ago
Excel does an atrocious job of handling CSV files. It regularly alters data, messes up encodings and either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I.
Source: dealing with CSV files people exported from Excel and the horrors that flowed from there.
- swader999 5y agoI've donated hours of my life to resolving excel corrupted csv files. They haven't been satisfying hours.
- bitwize 5y ago> either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I. It can't do this because it confuses such files with files in SYLK format, which was YET ANOTHER attempt to standardize spreadsheet data interchange, dating from the 80s.
- IanCal 5y agoWhat I find incredible about this is that it decides that the file ending ".CSV" must not be a CSV file but SYLK. Then loading it as SYLK fails, and it doesn't then try and load it as a CSV file instead.
- 41209 5y agoDoes any widely used application do it better. I always view CSV as a lowest common denominator, of course more precise formats exist, but not everyone can use those. Csvs normally get the job done, but like anything else you need to know it's limitations. Something like a basic phone book should work, your scientific data, with dozens upon dozens of floating point numbers may not work.
- capeterson 5y agoLibreOffice is generally a little less aggressive about formatting, but still has issues similar to Excel. For me personally, I usually view things in Excel/LO but don't save, and if I need to modify anything I'll use a text editor for one-off changes, or I'll use something like Python with the pandas library for more programmatic changes. Pandas does have issues forcing timestamps to convert sometimes, but that can be easily configured. Otherwise, no problems with this method. There's definitely a lot of friction if you want to edit .CSV's without your formatting/encoding being altered, unfortunately.
- munk-a 5y agoI much prefer ingesting CSVs into LibreOffice since it's a lot more upfront about what encoding it's going to try and use and the delimiter it has chosen - and those choices are extremely easy to modify and flexible. Excel also has a habit of utterly clobbering character encoding on export - once you start seeing umlauts and cedillas in your text you'll notice that Excel has a really bad habit of chosen latin-1.
- capeterson 5y agoYeah I suppose that's the crux of it. I really like that first popup that LibreOffice has when you open it up where you can specify encoding, delimiters, etc. LibreOffice does a similar amount of clobbering, but in general it notifies you of any would-be clobbering and allows you to abort beforehand, which is really nice. I still avoid exporting via LibreOffice though for very sensitive situations, but it is noticeably better still.
- 5y ago