4 ms·
An additional problem is that Excel, the tool many people use to open CSV files, uses the computers regional settings for decimal separator and thousands separa
by roelschroeven 6y ago
An additional problem is that Excel, the tool many people use to open CSV files, uses the computers regional settings for decimal separator and thousands separator. CSV files are used as an interchange format, so the format of the numbers in it does not depend on the region of that specific computer.
You can tell Excel to always use '.' as a decimal separator, but then it also uses it for presenting numbers to the user. It boggles my mind that software like Excel doesn't understand the difference between reading/formatting numbers for human use and reading/formatting numbers primarily meant for talking to other software.
- tlbsofware 6y agoExcel will also take a number with a leading zero and drop it while it converts it from csv, for ex 01234 -> 1234, which is SUPER bad if you are dealing with any sort of IDs to invoices or work orders from outside the company
- sk5t 6y agoThat is a good occasion to curse Excel (how does it know the number isn't octal, too?) and also curse the person or group that designed a textual coding system consisting of digits with leading zeros. Looking at you, National Drug Code.
- JoBrad 6y agoFWIW, the new importer for Excel lets you easily preview and adjust this behavior before importing the entire file.
- sk5t 6y agoExcel, while amazing on its own, is horrible for exchanging data in so many ways. I've maintained a policy at my company (life sciences) not to accept Excel files in any automated process; while parsing csv/tsv is a pain, xlsx/xlsb/etc. would amplify that pain in so many ways both predictable and surprising. https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/ https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al...
- arethuza 6y agoParsing proper Excel files is fine - you just have to use a proper Excel parser such as EPPlus or Aspose.Cells or similar. In lots of ways this is actually easier than handling CSV files from random sources.
- sk5t 6y agoI've done it with Apache POI, and while that 'worked' it doesn't wash away the fundamental problems with Excel datatype guessing. CSV is not great either (no fun explaining encoding or invisible control characters and unusual whitespace to the average IT person) but it is nevertheless less bad.