On Tue, 24 Oct 2023 08:49:37 +1300, Peter Reutemann wrote:
But, open-source tools aren't without blame for "automagic" either. E.g. pandas: https://stackoverflow.com/questions/41417214/prevent-pandas-from-reading-na-...
The issue in that case seems to be trying to interpret a non-numeric string as a number. That would mean converting _any_ such string to “NaN”, not just “NA”. That might be some issue with automatically inferring column types (I’m no Pandas expert), but apparently you can explicitly specify types anyway. And why not use NaN to express “not applicable”? It’s the numerical equivalent of a “null” value.
I always liked that about OpenOffice/LibreOffice that they had an actual import dialog (eg for CSV files) with a preview. Excel always imported your CSV files as it thought best - with the usual disastrous outcome (oh, look, now I have to go through the extract step of applying the convert text to columns function)...
There is apparently some kind of import wizard you can engage in Excel (according to that original paper), but it seems most users are unaware it exists. Also note that incorrect data conversions are just one aspect of the problems with Excel. The whole idea of trying to express mathematical models of any complexity in a spreadsheet is inherently going to be trouble.