It is impossible enter numbers larger than 15 digits on a spread sheet, unless the cell or row or column was pre-formatted to be text (Format cells>Numbers>Text). Any number, larger than 15 digits is automatically converted to scientific notation which is all right for calculations but not for text value. Furthermore, such numbers converted to scientific notation and saved as text, are irretrievably lost. For example: 12345678901234567 entered as text, is not the same as its scientific equivalent 1.234567890123456E+16 and yet the long form number will be lost and cannot be retrieved. That means, the spread sheet cannot be used for storing long numerical passwords or open a .csv file containing such passwords or data. Pre-formatting the spreadsheet to “text” is not a solution because when opening a .csv file (or any data file) you cannot know which cells will require such formatting and the value in those cells will be lost.
1. Open a spreadsheet and enter a 14 digit number in a cell. Since the default column-width is probably less than 0.9-inch, the number format will automatically switch to scientific notation. 2. Enlarge the column-width to twice its default size and enter three more numbers under the previously entered number, 15, 16, and 17 digits long. The 17 digit number will automatically switch to scientific notation no matter how wide your column is and the 16 digit number will change as soon as you format it to “text”. 3. If the 16 or 17 digit numbers are now copied to a text file, it will copy in the scientific format which is not the same as the original number that was typed in the spreadsheet. As a matter of fact, the original number is lost (unless you happen to remember it) because the scientific notation cannot be converted back to long form beyond 16 digits.
This is not a bug; and this is exactly why passwords, telephone numbers, card ids, and any other sequences of characters that are not numbers (i.e., are not representing a value, and are not used for calculations) should be entered as text. See this FAQ: https://wiki.documentfoundation.org/Faq/Calc/Accuracy It explains that Calc uses 64-bit binary numbers internally; and that format only guarantees 15 decimals stored accurately. Just pre-format respective cells/columns (which is also correct for CSVs), or enter with leading apostrophe.
Thanks for you answer but I am missing something. Are you suggesting that to open a .csv file in Calc, I should first format numbers to TEXT for the entire spreadsheet and then open the .csv file? Since I don't know ahead of time which cells are going to contain larger than 15 digits, that's the only way I can prevent large numbers turning into scientific notation. A .CSV file, however, can contain numbers as well (e.g. population figures) which then would have to be found and converted back to numbers?
CSVs typically contain data in columns - i.e., any given column contains a single data type. A column named "password" is likely to contain passwords, and a column named "sum" in likely to contain numbers. In the CSV import dialog, right-clicking on any column header in the preview area allows you to choose the data type there in the respective column. So right-click on the "Password" column header, and select "Text" in import.
Thanks, that makes sense. Sort of. I could not find any reference to the steps you mentioned anywhere in the LO CALC GUIDE or anywhere else. Wouldn’t it be simpler if Calc had an option to store larger than 15 digit numbers as text, since converting text to numbers is relatively easy and well documented.
(In reply to Tamas from comment #5) > I could not find any reference to the > steps you mentioned anywhere in the LO CALC GUIDE or anywhere else. You have a problem that importing a CSV, some data imports not as you expect. Moreover, you think that the problem is that you cannot *format* cells as text (I use your teems). The most straightforward thing to check is help. You press F1, and a search of keyword CSV in Index gives you "csv files -- importing and exporting" [1] among the top results. That page includes the mention how to *format* columns in the dialog. > Wouldn’t > it be simpler if Calc had an option to store larger than 15 digit numbers > as text, since converting text to numbers is relatively easy and well > documented. No. This proposal assumes that numbers shorter would import "correctly", which is not the case (not all decimal numbers are representable in binary), and would make part of column have numbers, part text, leading to more confusion. Whenever you are dealing with such data type agnostic media as CSV, no amount of guessing would solve user problems, and the correct answer is "user should do the decision themselves". [1] https://help.libreoffice.org/latest/en-US/text/scalc/guide/csv_files.html?DbPAR=CALC