[Solved] The apostrophe before a number in Calc

[Pedroski55]

Hi, I have class lists, each class in a different sheet.

When I click in a cell with the student number and look in the top bar at the cell entry I see some sheets have an apostrophe before the student number,
like class 16BE3:

'1625010301

and on another sheet no apostrophe, like class 16BE2:

1625010201

I am tinkering with python, writing a routine to insert test scores automatically. In the 2 sheets that have an apostrophe before the number, my python works fine, but on the others it just writes a default 1. It cannot see the student number, so it does not write the score.

What is this apostrophe and how can I get rid of it?

I read the student number and corresponding score from a csv file all into a python dictionary. The student number is the key. I do not see the apostrophe as part of the key if I print the dictionary to the screen. I write the scores to the excel file, using the key (= student number) to find the right student for that score.

I think it may come from the difference between German and English: In Germany they use a comma to separate the decimal places like 1,55 , whereas in English we use a period like 1.55 but I am not sure really. Sometimes I work in German because I have German classes too.

The names and numbers were read into a marking program from a csv file and the marking program produces a csv file with the results of any tests.

Thanks in advance for any tips or advice!

[RusselB]

The unwanted apostrophe is easiest handled by ensuring the correct import settings during the import dialog.
Frequently these are on numbers that are either currency, dates or times. The easiest way to handle these, is to ensure that the Detect Special Numbers box is checked in the Text Import Dialog.
You seem to have a contradiction, where some of the numbers are read correctly (without the apostrophe) and some not (ie: with the apostrophe). I have no idea as to how that managed from the same source.
As the numbers with the apostrophe are student numbers, they don't need to be numbers, since you aren't doing any mathematics with them, so having the apostrophe matters little.
Technically with the apostrophe, the "numbers" are being recognized as text.
hmmm. could there be quotation marks, in the source file, around the numbers that are showing the apostrophe? If so, then the quotation marks are telling the dialog that the information is text, rather than numeric.
As to your python code, I have no knowledge regarding that language, so I don't have a clue as to why your code is working with the text versions of the numbers, but not the numeric. You might want to consider making a new post in the Macros and UNO API forum for assistance with your code.

You might also want to take a look at [Tutorial]Text to Columns and [Tutorial] Ten concepts that every Calc user should know

[Villeroy]

Every spreadsheet program since 1979 distinguishes between text and numbers as separate data types.
The text "0123" is another text than "123" and both are completely different from the number 123.
The number 123 can be formatted (displayed) in thousands of different ways and it remains always the same number.

Every spreadsheet program has methods how to enter something like a phone number, article number, zip code or other identifiers as a text even if they consist of digits only. A leading apostrophe in front of numeric entry inhibits the numeric evaluation of the entry or formula expression. Likewise '=SUM(A1:B9) enters the literal text "=SUM(A1:B9)".

[Pedroski55]

Thanks. I did a workaround, loaded the 16BE4 and 16BE1 csv result files in writer, then ctrl f searched for all spaces ' ' and replaced with nothing. Now all the student numbers are numbers. In the python dictionary they are strings, so I just str(key) and everything works.

I have a vague memory of switching calc from English to German a while back, then there was something with changing the number format. Maybe I put a new class in an old sheet with German decimal format.

Anyway, works now, thanks for the tips!

[keme]

Note that if the student number is a fixed-width field, some may have leading zero(es). This would make the str() result different from preexisting string fields if they use zero padding. Using eval() on the text values may be safer.

The safest is to stick to one data type. Conversion between text and numbers will become unreliable when you have 14 or more significant digits.

Calc, and most other spreadsheets, work with a numerical precision of around 14 decimal digits, and cannot store longer numbers verbatim as numerical data. With text, you can have arbitrary length (more or less...).

[Villeroy]

So you are importing tabular text files (csv or something). You get an import dialog with import options. All these options do matter and there is a help button on that dialog.
[and somhow you mention Python dictionaries as well but you do not post what you actually do with Python, how the dictionary gets its data,...]

I have a vague memory of switching calc from English to German a while back

Does not matter at all. You can set the import language for every text import individually on that import dialog.

A student number is an identifier. Student 1234 is not "more than" student 1233 and you never calculate the sum, average, median or any other arithmetics from student numbers. Possibly the student 1234 is another student than the one having the id 01234. I would consider importing numeric identifiers as text as appropriate.

Please stop being obscure.

Unfortunately, nobody ever posts any example data from the imported text file, so we can not help you to find correct import options.
This is a line from one of my German bank account exports:

Code: Select all

9999999;06.10.2017;06.10.2017;AMAZON PAYMENTS EUROPE S.C.A.;5;"09675";111-222-999999 Amazon;.Mktplce EU-DE 000000001;11111;;;;;;;;;;;;-26,94;-3.817,14;EUR

It contains comma separated decimals (26,94) and point separated dates (06.10.2017), columns are separated by semicolons.
So I choose German(Germany) as import language and semicolon as the only column separator.
There is one text in double-quotes "09675". This is a field with numeric identifiers.
I choose " as text separator.
I choose to interprete values with text separators as text.
I choose to interprete "special numbers" because there are German dates I want to import by their numeric value rather than textual representation.

The preview columns at bottom of the import dialog even allow you to define exceptions from the above stated import rules. You can select columns and declare them explicitly as text, MDY date, DMY date or YMD date or as something US English.
I would import the first column (which is an account number) as text but I won't use it anyway so I neglect that.
If I had something like $123,456.99 (US currency) in my German bank account export file, I could declare this column as being US English explicitly.