[libreoffice-users] Re: Problem when exporting exporting from CALC to a fixed length csv

Hello,

As already stated, spreadsheet programs are horrible text editors.
This is my fixed length output of a spreadsheet copied from a text editor:
     000123      2012-05-13    0.57715one_char
     000124      2012-05-14    0.58904two_char
     000125      2012-05-17    0.86385three_char
     000126      2012-05-19    0.76092four_char

[I'll never understand why nobody is able to post some trivial example data]
Each line has 51 characters.

Getting rid of all the spaces is easy if *and only if* you know how a 
spreadsheet works and if you know exactly how you want to handle any 
varying field lenght.
Add a blank sheet and add one column where you concatenate all numbers 
as fixed lenght numeric strings with all text converted to fixed lenght 
text.

=TEXT($Data.A1;"000000")&TEXT($Data.B1;"YYYY-MM-DD")&TEXT($Data.C1;"0.00000")&LEFT($Data.D1;10)&IF(LEN($Data.D1)<10;REPT("_";10-LEN($Data.D1));"")

This gives the following set of lines:
0001232012-05-130.87391one_char__
0001242012-05-140.44086two_char__
0001252012-05-170.08597three_char
0001262012-05-190.60406four_char_

Each line has 33 characters:
6 for integers in field #1
8 for the dates in field #2
7 for the floats in field #3
10 for the varying characters in field #4, truncating more than 10 
characters, padding less characters with underscores.

Hope this helps,
Andreas


--
For unsubscribe instructions e-mail to: users+help@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