Numeric text string being read as float

[keithmantell]

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

[mjul]

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.

[keithmantell]

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

[kimim]

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

[kimim]

"2014" in this file is treated as string.
1900-based-dates.xlsx

[andres-moreno]

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!

[viesti]

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.

[mjul]

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.