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