![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I'm tearing my hair out here.
I have a spreadsheet and the data was originally entered as 4 digits. I need to pass it to a database system that requires 6 digits. I've tried formatting with 2 leading zeros but I still cannot get the number to properly show up as 00<number>
Any ideas?
Eugenie (Oogie) McGuire
Desert Weyr, LLC - Black Welsh Mountain Sheep http://www.desertweyr.com/
LambTracker - Open Source SW for Shepherds http://www.lambtracker.com
Paonia, CO USA
I'm tearing my hair out here.
Don't!
I have a spreadsheet and the data was originally entered as 4 digits. I need to pass it to a database system that requires 6 digits. I've tried formatting with 2 leading zeros but I still cannot get the number to properly show up as 00<number>. Any ideas?
Yes. If the values you have are numbers, then formatting them (as something like "000000") should display them as you need. Whether you get six digits into your database depends on exactly how you then transfer the values.
But you say this doesn't work. The most likely explanation is that the values stored in the cells are not numbers but text strings - albeit made up of four numeric characters. Changing the formatting of such cells after the event will not change text values into numbers. (You generally wouldn't want it to.)
How to proceed? Take your pick:
o In a new column, enter =VALUE(Xn) and fill it down the column. You will now have numbers and can format them as you wish. You could even copy them back over the original values, using Paste Special... and pasting Numbers but not Formulas.
o In a new column, enter ="00"&Xn and fill it down the column. You will now have six-character text values. Again, you could copy these back over the original values, using Paste Special... and pasting Text but not Formulas.
I trust this helps.
Brian Barker
cell->format->number. In the format code bar near the bottom
input cell format as "000000.0000" without the quote marks.
You can adjust the zeros after the decimal point to suit.
Joe Conner, Poulsbo, Washington, USA
Already tried that, it doesn't work. I tried a custom format 000000 but still only get 4 digits.
cell->format->number. In the format code bar near the bottom
input cell format as "000000.0000" without the quote marks.
You can adjust the zeros after the decimal point to suit.
Eugenie (Oogie) McGuire
Desert Weyr, LLC - Black Welsh Mountain Sheep http://www.desertweyr.com/
LambTracker - Open Source SW for Shepherds http://www.lambtracker.com
Paonia, CO USA
Just change the properties to 'Text'. It'll preserve the format and
you can still do math functions using the cell.
I'm tearing my hair out here.
Don't!
I have a spreadsheet and the data was originally entered as 4
digits. I need to pass it to a database system that requires 6
digits. I've tried formatting with 2 leading zeros but I still
cannot get the number to properly show up as 00<number>. Any ideas?
You have two options here.:
The simplest is to change the column in Calc to text and then concatenate the require number of leading zeros; in a new cell =concatenate("00", <cell with values>)
The better solution may to change the column definition in the database table to use the data type varchar(6) or int. Varchar(6) allows the use of a variable number of characters up to 6 characters length. Int assumes the data is originally integer and all remain an integer. Normally the allowed integer is much large the 999,999.
Sorry, but this is rather confused. You talk of changing the "properties"; do you mean the cell format or something else? Surely the questioner's problem is the reverse of what you describe: that his values are already text and thus do not respond to changes in Number format.
You can change the cell format to Text only if it is not already Text. But if the questioner's values were numbers formatted as Number, he would not have his problem: just setting the format to 000000 would achieve what he needs. In any case, if a number formatted in this way has leading zeroes, changing its format to Text would not - as you claim - preserve this format. The value would stay as a number (even in a text-formatted cell), but would lose its leading zeroes, returning to a default numeric format.
It's unlikely anyway that he needs to carry out calculations with these values: it's only numbers used as labels - such as postal codes and telephone numbers - that need leading zeroes, not numbers used as values.
Brian Barker
Oogie are you still thinking of tearing out hair or have you gotten a
solution from the responses so far?
If you're still in the hair tearing stage perhaps you might find the
following useful...
In my experiments copying data from Calc and paste appending to a Base
table:
1. If source data is a number formatted with leading zeros and destination
column is VarChar then leading zeros are preserved
2. If source data is a number formatted with leading zeros and destination
column is Integer then leading zeros are not preserved
3. If source data is text with leading zeros and destination column is
VarChar then leading zeros are preserved
4. If source data is text with leading zeros and destination column is
Integer then leading zeros are not preserved
So experiment 1 and 3 produce the results you want, leading zeros preserved.
If none of the above gets you where you need to be then perhaps you could
provide the following information that should aid in troubleshooting:
1. What database program is the spreadsheet data being loaded into?
2. Are you able to inspect the table structure to see the data type of each
column that you are interested in? If yes, what is the data type of each of
those columns?
3. What is the data type of each of the spreadsheet columns you want to
import?
To see the data type of a value in a cell enter the formula
=CELL("TYPE",<address>) in a nearby cell. The <address> should refer to the
cell that needs to be inspected. Is the result of the formula "l" or "v"?
The first result is a lower case letter L the second is much more obvious,
a lower case v. <address> needs to be a cell reference, e.g. B5, not the
literal <address> that I've written here. The actual formula would look
like =CELL("TYPE",B5) to find out the data type in cell B5. Repeat the
formula for each column that is loaded into the database. Will want to know
what type of data is already in each column.
I'm tearing my hair out here.
.....
Oogie are you still thinking of tearing out hair or have you gotten a
solution from the responses so far?If you're still in the hair tearing stage perhaps you might find the
following useful...
....
A number NEVER has leading zero's. This has nothing to do with the fact that it can be displayed with leading zero's, if using the format functionallity of Calc.
Below is a good advise to determine if a field is a text value, or a number value
To preserve leading 0's in a spreadsheet type the number with an apostrophe before it. For example, instead of typing 1234, type '001234. That will preserve the leading 0's.
If I don't care about formatting in the spreadsheet, I'll add the leading 0's in the code that does the passing. Typically I read the cell, convert to string, pad with the appropriate number of 0's, then write to the database.
-Bill
OK an update:
The original spreadsheet is an export of a .XLS file from a Foxbase database on a Windows machine. Based on what I got I thought the data were stored as 4 digit numbers but in the database they are really 6 or more text characters.
I needed to get it into an SQLite Database on an Android tablet via LibreOffice on a Macintosh. I do that by converting the .XLS to a .CSV file in LibreOffice. Once I have a good .CSV file I create update statements for the SQLite database by giving it the table name, primary key and update values. When I bring the data in to LibreOffice it assumes they are numbers and then the various issues with the leading zeros. I need the leading zeros because the linkages of a record to another record in the database on the final update require the leading zeros.
Once the data are in SQLite then that file is put on the Android tablet. Data are added and modified via the Android system.
Then I need to take the data out of the Android, into the Mac. I just move the entire SQLite Database to the Mac. Then I create an export file in .CSV format from the SQLite Database via a customized Query. That file then goes to the person with Windows machine. They run an import process on the file to bring in the new records, link them as appropriate and then those incorporate the changes into the Foxbase database.
On the Foxbase system the data are stored as text strings but because they are all digits when it gets imported to LibreOffice they get interpreted as numbers. That's why I couldn't get the adding back of the leading zeros to work at all. And the transfers back and forth resulted in all the linkages being broken.
The system is a sheep registration system talking to my own sheep management system. The links are from a new lamb to its parents based on registration number so it has to be correct.
What has finally worked is the following workflow:
Get .XLS file from the Windows computer. Get someone on a Windows machine to save that file as a .CSV on the Windows machine.
When I do the import of the .CSV file into LibreOffice instead of allowing Standard on the import set the required fields to be text.
That preserves the leading zeros that already exist in the file from the Foxbase system.
Create my update statements for the SQLite Database per normal, run them, move the database to the Android, collect the data as required and then move the database back to the Mac.
Do the required Select statement that creates a table that I then export as .CSV file
Open that in LibreOffice and verify the text strings are still text. Save it as a .XLS file
Send that to the Windows machine. There it is used as input the the Foxbase system.
I've tried it with one flock with 84 2014 lambs and it worked. Nor ready to test with the next flock of 156 new lambs.
Eugenie (Oogie) McGuire
Desert Weyr, LLC - Black Welsh Mountain Sheep http://www.desertweyr.com/
LambTracker - Open Source SW for Shepherds http://www.lambtracker.com
Paonia, CO USA
I think you need to take a closer look at the import process of a CSV file into LibreOffice.
I do get a 'Text import' wizard, and if i select 'Quoted field as text',
and under field specify 'Text' for the desired columns (columns 2,3)
When i import this CSV file:
a;b;c
1;2;3
"0001";"0002";"0003"
0001;0002;0003
I do get this in LO (column separated given as semi-colon for readability (notice the space to indicate wheter a value is left aligned or right aligned:
a;b;c
1;2 ;3 ;
0001 ;0002; 0003;
1;0002 ;0003 ;
When i look at the returnvalues if the ISNUMBER() function for these nine fields:
a;b;c
TRUE;FALSE;FALSE
FALSE;FALSE;FALSE
TRUE;FALSE;FALSE
Everything as expected.
- the values between '"' are all TEXT
- the columns (2 and 3)are also text.
- in text-values the leading zero's are maintained