[Solved] Import 20-digit ICCID numbers from CSV

[dmey]

Edit: Split from [Solved] Disable scientific notation for long numbers because that topic is solved and your post adds nothing of value to that discussion.

I'm going to once again necro this very old thread, but this is just insanity and none of these workarounds work.

I deal with 20-digit ICCID numbers all day. These are numbers that tie the SIM card in people's cell phones to their mobile operator's network.

I also deal with CSV files all day.

LibreOffice decides that whenever I open a CSV file with these 20-digit ICCID numbers, that it wants to convert them all to scientific notation. Then when I save the CSV file back to my computer, it has changed all my ICCID's to scientific notation and I'VE LOST ALL OF MY DATA!!!

I would like to have words with whoever is in charge of keeping this asinine behavior stuck in the past. This is ABSOLUTELY INFURIATING!

[RusselB]

Import the 20 digit numbers as text.
These are not, imo, based on the description of the numbers, going to be used for any mathematics.
If you need to check if a number already exists, this can be done as easily with text as with a number.
Importing and exporting these "numbers" as text will prevent the change between a 20 digit number and Calc's scientific representation of that number.

[MrProgrammer]

Hi, and welcome to the forum.

I'm going to once again necro this very old thread …

That thread is not yours. It belongs to jazzparadigm and you should not post in it unless you are contributing to a solution for them. Open your own topic. I did that for you, since you are new and may not understand how this forum works. It will help you to read our Survival guide.

LibreOffice decides that whenever I open a CSV file with these 20-digit ICCID numbers, that it wants to convert them all to scientific notation.

No, you decided to ask LibreOffice to convert the ICCID identifiers to numbers, and since they would be very large numbers, Calc uses scientific notation. When you tell Calc to open CSV, you are presented with the Text Import dialog. There you control how the import is done. When you accepted the default of Standard for the field with the ICCID identifiers, you told Calc to interpret them as numbers. But they are identifiers, not numbers. You are not going to sum them or round them or take square roots. Just set the ICCID Column Type to Text and the import will work as you want. This dialog is explained in detail in the following tutorial.
[Tutorial] Text to Columns

It is common to work with data in Calc which looks like numbers but is actually an identifier or code. Ask yourself: Am I going to perform any mathematics with this "number"? If not, it should be stored as text. A frequent case is address data which includes postal codes, which in the United States are called ZIP codes. They always need to be stored as text. If not, Calc will ignore leading zeros since those do not affect a number's value. But the postal code is not a number and leading zeros cannot be ignored. The ZIP code for the Massachusetts State Capitol is 02108. Putting 2108 in the address is wrong, even though 02108 = 2108 arithmetically.

Then when I save the CSV file back to my computer, it has changed all my ICCID's to scientific notation and I'VE LOST ALL OF MY DATA!!!

Numbers in Calc (and other spreadsheets) are limited to 15 significant digits. When you provide a 20-digit number, the exact values of last five digits cannot be saved in the cell though they increase the scientific notation's exponent. Could you not see that the data was imported incorrectly? The display would show something like 1.235E+019, not 12345123451234512345. Your choice to save back to the original file after an incorrect import was what destroyed your data. If you were unsure, you could have used File → Save As to preserve the original file until you could verify that your procedure was correct.

I would like to have words with whoever is in charge of keeping this asinine behavior stuck in the past.

This is not a developers' forum. We are all users, just like you. You're welcome to rant if it makes you feel better, but nothing you post here is going to affect how OpenOffice or LibreOffice works. Developers will not see this topic. I think if you study the tutorial above it will help prevent misunderstandings and data loss in the future.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Mountaineer]

...
I deal with 20-digit ICCID numbers all day. These are numbers that tie the SIM card in people's cell phones to their mobile operator's network.

I also deal with CSV files all day.

LibreOffice decides that whenever I open a CSV file with these 20-digit ICCID numbers, that it wants to convert them all to scientific notation. Then when I save the CSV file back to my computer, it has changed all my ICCID's to scientific notation and I'VE LOST ALL OF MY DATA!!!

I would like to have words with whoever is in charge of keeping this asinine behavior stuck in the past. This is ABSOLUTELY INFURIATING!

Several years ago I got new work and found excel there. I switched quickly to AOO/LO because this program asks what to do with a csv-file, while excel just assumed everything is in the typical format they expect. (It wasn't we used tab-separated more often and rarely , )

So when you import data check the columns and change the type of the columns. In your case to text.

You/ your company decided to use csv, which contains usually no meta-information, so you have to tell Calc to protect
long numbers from SIM-cards
Phone-numbers with leading zeros
german zip-codes with leading zeros
and
type of international dates 2.4. / Feb. Fourth or Apr. 2nd
1,000 may be american 1000 or german 1
....

It may be better to use a database to store your data in a database. These get a description of what you want to store before they get the data. However exchanging data with the spreadsheet, if this is necessary, may need the same precautions as above.

Spreadsheets use a lot of assumtions, to make life easier for most. But if you didn't fit in the profile you have to be careful...
And you choose the tools you use. Do you really think spreadsheets are so popular because the annoy everybody who is working with them. Maybe you should adjust your perspective.

J.

[Villeroy]

Every day use of spreadsheets is one of the most momentous misapprehensions in IT history. When you start one of these programs for the first time, you grasp instantly what it was invented for -- but this is actually not what it was invented for.

[John_Ha]

Every day use of spreadsheets is one of the most momentous misapprehensions in IT history. When you start one of these programs for the first time, you grasp instantly what it was invented for -- but this is actually not what it was invented for.

+5

See spreadsheet disasters for some real life horror stories. Top 10 Spreadsheet Disasters echoes your words almost exactly.

Spreadsheets can be very useful especially when it comes to financial applications, but when mistakes are made, as we can see from our infographic, the consequences can be serious. But when you consider that most business users have no formal spreadsheet training, it’s hardly surprising that errors happen.

Clearly, errors are one of the biggest issues when it comes to untrained staff using spreadsheets, but poor knowledge and the adoption of bad spreadsheet practices also leads to time wastage and inefficiencies ...

The single biggest cause of spreadsheet disasters is inexperienced, untrained personnel inputting and manipulating spreadsheet data.

The problem isn't really with the software which does what it is designed to do within limitations which are well known. The problem is with users using it incorrectly as they have no understanding of it and no training.

That being said I do have some sympathy for the poster as neither the AOO nor the LO manual explains the largest or smallest numbers allowed so it is only by knowing the PC uses the IEEE Standard for Floating-Point Arithmetic (IEEE 754) - 64 bit number representation that you get any idea.

As MrProgrammer says, if you type in the 20 digit number 12345123451234512345 it is displayed as 12345123451234500000. Similarly, if you type in the 20 digit 99999999999999999999 it is displayed as 100000000000000000000. Both of these displayed numbers are the closest allowable number to the typed value. This is a consequence of the PC, and hence Calc, using IEEE 64-bit Floating point representation to calculate and store numbers.

Exploring what happens at the extremes will throw up many surprising and unexpected consequences.

[jrkrideau]

Interesting paper on floating point arithemetic which, I believe, is the issue that the OP has hit. No spreadsheet that I have heard of will deal with the issue. Once one is over ~15 digits, accuracy is shot.

Some of the symbolic math programs such as Maple, Mathematica, and the open source Maxima can handle arbitrary-precision arithmetic. Perhaps Sage, too?

Somewhat bizarrely, apparently, so can Perl,

[John_Ha]

No spreadsheet that I have heard of will deal with the issue.

PCs (and other general purpose computers) are equipped with a hardware IEEE Format Floating Point Processor Unit as part of the CPU. All programs use it apart from those like Maple, Mathematica, Maxima and Python which implement extended precision calculations in bespoke software and are hence much, much, much slower.

Once one is over ~15 digits, accuracy is shot.

Hardly shot! That's equivalent to measuring the distance from London to New York to an accuracy of less than one billionth of an inch! Or the distance of the earth to the moon to an accuracy of one millionth of an inch. Or the distance to the sun to an accuracy of less than one thousandth of an inch.

Seeing that America is moving away from Europe by about 4 cm/year this means you can use Calc to record how the distance between America and Europe increases each billionth of a second.

[jrkrideau]

Seeing that America is moving away from Europe by about 4 cm/year this means you can use Calc to record how the distance between America and Europe increases each billionth of a second.

Definitely shot. The Galactic Land Registry Office will never accept that level of inaccuracy.

[John_Ha]

Definitely shot. The Galactic Land Registry Office will never accept that level of inaccuracy.

Blast - I had forgotten about the Galactic Land Registry Office.

I had been working in integers or, to be more precise, those integers which can be represented exactly (ie with zero error) by floating point numbers with a fractional part of zero.

Now that you have reminded me of the Galactic Land Registry Office I think I shall have to move to using the full range of floating point numbers, both floating point and integer. The smallest number Calc recognises is then about about 10^-308, which is 0. followed by 307 zeros followed by 1. The largest number is about 10^308 which is 1 followed by 308 zeros. While the relative error remains constant across the range the absolute error varies and is proportional to the magnitude of the stored number: a small number has a small error and a large number has a large error. IEEE 754 has this graph.

As the 13 light year wide observable universe is only about 10^18 inches across that should be more than enough to satisfy the Galactic Land Registry Office.

As an aside, the IBM RS6000 computers use 64 bit arithmetic but have a 128 bit "partial products" storage register in the FPU so that intermediate values in a calculation can be held to more accuracy so giving more accurate answers. Also, as many mathematical functions are actually calculated by Newton-Rapheson approximations this speeds up the convergence so the computer runs faster than if partial products were truncated to 64 bit precision.

[John_Ha]

I deal with 20-digit ICCID numbers all day. These are numbers that tie the SIM card in people's cell phones to their mobile operator's network.

Being serious for a change ...

Being able to differentiate between 99999999999999999990 and 99999999999999999991 as numbers requires an accuracy or precision of one part in 10^20. That is like measuring the width of the entire universe to an accuracy of one thousandth of an inch. I think you can see why no-one needs to do that.

As advised earlier, the best solution is to input and store them as a text string when they can be as long as you want. You do not need to calculate (add, subtract, multiply, divide) with your ICCID numbers but you can still sort them into order and compare adjacent values to see if they are the same. It's just you cannot add or subtract them.

Edit: The sort will be in alphabetical order, not in numerical order.

It is possible by means of workarounds to add or subtract two 20 digit text strings as though they were numbers so, if you need to, come back for more advice. Similarly you can count how many numbers there are between any two given numbers.

A poor workaround is to split the 20 digit number into two chunks each of 10 digits, or a leading 6 digits and a following 14 digits. These will be stored accurately. You can use CONCATENATE to join them but only as a text string if you want to retain all the digits, so you are really back to the first method.

[Lupp]

Let's continue with ...
□... Whatever we are used to do all the time.
□... Misusing spreadsheets.
□... Blaming spreadsheets.
□... Blaming users.
□... Using spreadsheets for once-in-a-while-tasks we have no specialized supporting software for.
□... Misusing databases.
□... Using databases where we can hardly avoid them.
□... Hailing only foolproof solutions for whatever.
□... Believing in things capable of creating errors in a foolprof way.
□... Hailing the Artificial Inteligence to come.
□... Bending the problems to fit the tools.
□... Developing the 10 001st DB engine even better than the existing ones.
□... Criticizing US date formats.
□... Critisizing forum.openoffice.org/en/ for its default date format.
□... Bashing nonsense "gendering" in Germany.
□... Criticism in Deng Xiaoping.
□... Adding another post to...

Posting to forums like this one means...
□... Trying to be helpful.
□... Trying to teach.
□... Helping everybody to learn based on his (f/m) own forces.

Urgently to do...
□... Delete this post?

[John_Ha]

According to What Is an ICCID Number?

An ICCID is a unique 18-22 digit code that includes a SIM card’s [industry code], country, home network, and identification number.
...
As an example, let’s use 891004234814455936F [which splits as 89 1 004 234814455936F].
...
The final set of digits is completely unique to each SIM. No other SIM in the world will share this exact combination. This is what allows you to view a specific SIM in a SIM manager, activate or cancel a service, or interact with your connectivity provider’s customer service. In our example, this set of digits is 234814455936F.

I disagree with the use of "18-22 digit" as one, the final F, is a character.

So, it seems that the ICCID code could sensibly be split into several parts as I suggested earlier - it's not just a poor workaround. As only 13 digits are unique in the identification number they can accurately be stored as a number albeit the final character F is a text character and would need to be handled.

All in all, it's best to work in text strings, not numbers.

[jrkrideau]

Th real question is why store a code as a number when it is not a number? It simply increases the chance of error or data loss. See Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics

It's like my old joke about my doctor's spreadsheet accidentally multiplying by my telephone number rather than my weight well calculating a drug dosage.

[robleyd]

I disagree with the use of "18-22 digit" as one, the final F, is a character

Putting on my devil's advocate hat; just to further muddy the waters, maybe it is a hex value

[John_Ha]

It's like my old joke about my doctor's spreadsheet accidentally multiplying by my telephone number rather than my weight while calculating a drug dosage.

Very prescient.

See '6.2cm-tall man' offered priority Covid vaccine after NHS blunder where a guy who is 6ft 2" tall was recorded as being 6.2cm tall. This gave him a BMI (Body Mass Index - ratio of height and weight) of 28,000, where over 25 is considered overweight, and pushed him to the front of the queue for a Covid vaccination.