AnsweredAssumed Answered

Are there secret tags in CSV files?

Question asked by maribethprice on Jan 15, 2017
Latest reply on Jan 16, 2017 by maribethprice

I would love an Esri programmer (or anyone for that matter) to answer this for me.  I encountered some unusual behavior in an Excel table named ORprecipnormals (97-2003 .xls format) containing precipitation normals for climate stations. ArcMap versions 10.2 through 10.5 read it fine; Pro insists on reading the numeric values as text fields, rendering them useless. I've always known that the Excel interpreter can have some gremlins in it. I tried updating the Excel table to .xlsx format--still got text.  I tried saving it as a CSV file--still got text.

 

In desperation, I copied the data in Excel and pasted them (as values only) into a new spreadsheet (named simply precipnormals), saved the spreadsheet as .xlsx, and also saved it as a CSV file.  Pro now correctly interprets both  spreadsheet and CSV files to contain numbers (Double). 

 

That the new spreadsheet works doesn't surprise me; there must have been something funky in the original spreadsheet. But I am mystified why one CSV file gets interpreted correctly and one does not.  Side by side in Notepad, they appear identical. Isn't that the whole point of a CSV file?  They are simple ASCII and direct and transportable.  Yet it appears that Excel dumped some invisible tag into the first CSV, and Pro is heeding it to interpret the values as text.  Why oh why would this be? Note that ArcMap does NOT heed those invisible tags; it interprets any of the four files correctly.

 

The attachments include the two CSV files shown together in Notepad, the Fields view showing how they are interpreted differently in Pro, and finally, the files themselves.

 

I await enlightenment.

 

Meanwhile, for those with more pressing practical concerns, if Pro is making hash of your spreadsheets, try the copy/paste-special-values trick.

 

Maribeth

Outcomes