Re: [libreoffice-users] Can't get leading zeros in Calc

On Sat, May 31, 2014 at 7:34 PM, Oogie McGuire <oogiem@desertweyr.com>
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: users+unsubscribe@global.libreoffice.org
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