I have tried to turn off all auto-formatting, but libreoffice always finds a way to autoformat. What is particularly annoying this time is it is taking off leading zeros from numbers in a csv file. I wish that there was a way to turn off formatting of numbers - that it would just save and display whatever I have typed into a field. I have looked at the csv file as text and can see that libreoffice did save the numbers with the leading zeros, but I can’t figure out how to import them. How can I resolve this?
leading zeros from numbers in a csv file.
That sounds - strictly speaking - contradictory in terms of calc types. Either these are numbers, then there are no leading zeros (but you could provide a custom format code like 000000 to have 6 places for each number) or the values are of type text and then … see @JohnSUN’s answer.
Well I just assume have it as text, but Calc is forcing interpreting as numbers. Even so, if you don’t consider “000123” as a number, why can I perform mathematical operations on it? (I can type A1+1 and it would show “124”. And why should calc insist that a number must be in decimal form with no leading zeros? Numbers are not symbols, and base 10 with no zeros is just a convention.
I can’t very easily have a custom format to solve this. Some cells have more leading zeros than others, and it would take a long time to manually go through each type give them their own custom formats.
I just stated:
- If you import as text you can’t calculate
- If you import as numbers, you can’t have a defaulted number of leading zeros.
That’s not true. I just imported a csv as text and I can calculate on it. I can use the cell in a function and it takes the numerical value. That means that Calc is treating it in some way as a number. You can’t just add 1 to a string and get the next number.
How Calc converts text to numeric on the fly (not import) during calculations depends on the settings under Tools → Options → Calc → Formula, Detailed Calculation Settings. Select Custom, Details, Contents to Numbers to see possible settings. Recommended is Generate #VALUE! error to spot errors early, but the default is Convert also locale dependent because that is what users are used to because it is what Excel does (and the worst of all settings).
Setting the file import options, select the desired column by clicking on its heading and select the Text type. This will force Calc to read this column as-is, with leading zeros.
Mine is greyed out. I posted my screenshot.
Yours is greyed out because you didn’t follow the @JohnSUN’s advise carefully: you missed the “select the desired column by clicking on its heading” part. Also right-clicking the column allows to choose its type.