[libreoffice-users] 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: 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