docjure
docjure copied to clipboard
mjul
Numeric text string being read as float
Hi,
Probably a newbie question:
I have a cell set as Text, say 9A5655897 then when I read with select-columns I get "9A5655897"
But if I have 91371672 then I get 9.13712672E8
Is there a way of forcing a read as a string?
I am on Mac OS if that makes a difference.
Thanks
In general, Excel prefers numbers.
If you are up to it, you can have a look at the Docjure code to see how it reads the cell value. Maybe there is a clue there.
You can also try applying the clojure str function to turn the data into strings.
Hi, tried the str function bit that just gives the floating point version in quotes! I did look at the code. I'm not an expert but will think about submitting a change. My work around is to save the spreadsheet as a CSV and read that! Thanks
I've tested in Excel. By default, number string is stored as number. When the field with small green triangle, indicates it is a number stored as string:
https://support.microsoft.com/en-us/office/fix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0#__toc260840018
I noticed that when reading a 10-digit integer I would also get a floating-point number. I decided on a lark to format it using cl-format (the formatting string used was "~14,'0d". It turns out that this will give me a 14-digit, zero left-padded string!
I ran into this also, and found an SO post, which points to org.apache.poi.ss.usermodel.DataFormatter.formatCellValue, which seems useful, based on the javadoc, if you want a string that looks the same as in Excel:
DataFormatter contains methods for formatting the value stored in a Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel.
Please submit a PR if it would be useful to provide a helper function in the Clojure library that wraps a call to this formatter.