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

2014-06-10 Thread Oogie McGuire
 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: users+unsubscr...@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



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

2014-06-03 Thread William Drago
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 00number

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: users+unsubscr...@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



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

2014-06-01 Thread Alan B
On Sat, May 31, 2014 at 7:34 PM, Oogie McGuire oog...@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 00number


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+unsubscr...@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


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

2014-05-31 Thread Oogie McGuire
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 
00number

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: users+unsubscr...@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



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

2014-05-31 Thread Brian Barker

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 00number. Any ideas?


Yes. If the values you have are numbers, then formatting them (as 
something like 00) 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 =00Xn 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: users+unsubscr...@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



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

2014-05-31 Thread Joe Conner


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

Any ideas?

cell-format-number.  In the format code bar near the bottom
input cell format as 00. 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: users+unsubscr...@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



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

2014-05-31 Thread Oogie McGuire
Already tried that, it doesn't work. I tried a custom format 00 but still 
only get 4 digits.


On May 31, 2014, at 6:17 PM, Joe Conner joeconner2...@gmail.com wrote:

 cell-format-number.  In the format code bar near the bottom
 input cell format as 00. 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: users+unsubscr...@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



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

2014-05-31 Thread ZP
On Sun, 01 Jun 2014 01:11:36 +0100
Brian Barker b.m.bar...@btinternet.com 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 00number. Any ideas?
 
 Yes. If the values you have are numbers, then formatting them (as 
 something like 00) 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 =00Xn 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: users+unsubscr...@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



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

2014-05-31 Thread Jay Lozier


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

On Sun, 01 Jun 2014 01:11:36 +0100
Brian Barker b.m.bar...@btinternet.com 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 00number. 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 00) 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 =00Xn 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
jsloz...@gmail.com


--
To unsubscribe e-mail to: users+unsubscr...@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



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

2014-05-31 Thread Brian Barker

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 00number. Any ideas?


Yes. If the values you have are numbers, then formatting them (as 
something like 00) 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 =00Xn 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 
00 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: users+unsubscr...@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