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

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