Can't get leading zeros in Calc

classic Classic list List threaded Threaded
12 messages Options
OogieM OogieM
Reply | Threaded
Open this post in threaded view
|

Can't get leading zeros in Calc

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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
>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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

JOE CONNER JOE CONNER
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by OogieM

On 05/31/2014 04:34 PM, Oogie McGuire wrote:
> 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?
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

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

OogieM OogieM
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

Already tried that, it doesn't work. I tried a custom format 000000 but still only get 4 digits.


On May 31, 2014, at 6:17 PM, Joe Conner <[hidden email]> wrote:

> 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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

ZP ZP
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by OogieM
On Sun, 01 Jun 2014 01:11:36 +0100
Brian Barker <[hidden email]> wrote:

> At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
> >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
>
>

Just change the properties to 'Text'.  It'll preserve the format and
you can still do math functions using the cell.

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc


On 06/01/2014 01:02 AM, ZP wrote:

> On Sun, 01 Jun 2014 01:11:36 +0100
> Brian Barker <[hidden email]> wrote:
>
>> At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
>>> 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.

>> 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
>>
>>
> Just change the properties to 'Text'.  It'll preserve the format and
> you can still do math functions using the cell.
>

--
Jay Lozier
[hidden email]


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by ZP
At 13:02 01/06/2014 +0800, Zonly Ponly wrote:

>On Sun, 01 Jun 2014 01:11:36 +0100 Brian Barker wrote:
>>At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
>>>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.
>
>Just change the properties to 'Text'. It'll preserve the format and
>you can still do math functions using the cell.

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  


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Alan Boba Alan Boba
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by OogieM
On Sat, May 31, 2014 at 7:34 PM, Oogie McGuire <[hidden email]>
wrote:

> 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>
>

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.

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

On 1-6-2014 19:03, Alan B wrote:
> On Sat, May 31, 2014 at 7:34 PM, Oogie McGuire <[hidden email]>
> wrote:
>
>> 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


>
> 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.
>



--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Windows10 / LibreOffice  (latest?)
wdrago wdrago
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by OogieM
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

On 5/31/2014 7:34 PM, Oogie McGuire wrote:

> 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
>
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

OogieM OogieM
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

In reply to this post by Alan Boba
> 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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Can't get leading zeros in Calc

On 10-6-2014 16:21, Oogie McGuire wrote:

>> 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.
>

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








--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Windows10 / LibreOffice  (latest?)