Problem when exporting exporting from CALC to a fixed length csv

users
#21

Hi. I have done some xslt filters for importing data and export to xml. I am not certain, but may be you could create an export filter to the text format you require.
steve

#22

OK, this comes from the Nabble interface and from your example data I come to
the conclusion that all data are integers.
A generic solution for up to 1024 columns may look like this:

Assumed data in Data.A1:AMJ999
A1000: =MAX(LEN(A$1:$A$999)) [this is an array formula to be entered with
Ctrl+Shift+Enter rather than Enter]
Copy&paste A1000 to B1000:AMJ1000 (drag&drop won't do what I want in this
case).
Now we have an additional row with the maximum lenghts of each data column
in A1:AMJ999.

Other_Sheet.A1: =IF(LEN($Data.A1)<$Data.A$1000;REPT("
";$Data.A$1000-LEN($Data.A1));"")&$Data.A1
Copy Other_Sheet.A1 to Other_Sheet.A1:AMJ999
For formatted numbers (dates, times, percent etc) replace $Data.A1 with
TEXT($Data.A1;"format_string") in the respective column.

Now all the cells are left padded with spaces having the same lenght as the
calculated maximum lenght.

Save that sheet as delimited text but with no column delimiter nor text
delimiter. Clear the respective export options.

#23

Am 31.05.2012 20:07, Andreas Säger wrote:

Assumed data in Data.A1:AMJ999
A1000: =MAX(LEN(A$1:$A$999)) [this is an array formula to be entered with
Ctrl+Shift+Enter rather than Enter]

Small but important correction: =MAX(LEN(A$1:A$999))

By the way: http://user.services.openoffice.org is a more user friendly place.
There you can search a huge knowledge database and tutorial section, edit your own posts, add screenshots, documents and formattings.

#24

Nice solution Andreas.
Steve

#25

Hi all!

I will give Andreas solution a try and also will check the other link.
Because I guess this goes out to everyone on the list everytime. I usually
don't like mailing lists. I prefer the forum style a lot more.

I had hoped that there is a simpler solution than go for formulas, because
with such a huge amount of formulas usually things slow down.

#26

Hi :slight_smile:
Don't worry!  Many of us are on the lists because we like to see what problems people run into and how that changes over time, also many of us like to learn a little when we hear answers or enjoy getting inspired by someone else's answer and find we are able to help, perhaps, unexpectedly.

I think possibly most of us are hear to learn in a more hands-on way than passive reading could offer.  I tend to fall asleep when i try to read any kind of manual however interesting or well written but the arguments in here are quite lively and stimulating.

Also it's not impossible to read subject-lines or take notice of who wrote in and just delete a shed load of emails without reading any of them.  Also many people subscribe using the "nomail" option and then subscribe to individual threads in Nabble or use GMANE.

Regards from
Tom :slight_smile:

#27

Hello berritorre

I have setmy email for fixed width in hopes that this test will be clearer.

The table below is a direct copy from Calc using the commands I spoke of. Letter Gothic typeface.
No override of default in formatting. (assuming it is defined on import)
The headers are a=column1 b=column2 c=column3 d=column4
column a = 1 character, column b = 6 characters, column c = 10 characters, column d = 15 characters.

a b12345 c123456789 d123456789d1234
1 a 1 this is
2 ab 10 this is a
3 abc 100 this is a test
4 abcd 1000 this is a testD
5 abcde 10000 this is a test
6 abcd 100000 this is a
7 abc 1000000 this is
8 ab 1000000000 this
9 a 1000000000 this

The results after export fixed width were copied from NotePad on Windows XP.
ab12345 c123456789 d123456789d1234
1a 1this is
2ab 10this is a
3abc 100this is a test
4abcd 1000this is a testD
5abcde 10000this is a test
6abcd 100000this is a
7abc 1000000this is
8ab 1000000000this
9a 1000000000this

I shall describe the results but I am hoping you will see what I see.

Headers and data in Column a and b have no space between them.
Headers in Column b and c have one space between them.
Data in Column b and c have two spaces between them.
Headers in Column c and d have one space between them.
Data in Column c and d have no space between them. (field forced to a numeric value and pushed right)
Data in Column d has added two spaces after field.

In any case, what it means is that Calc is unreliable as a fixed record length definition on export.

Sorry but must advise using a different program as filter for export.
I do not know if importing data to a Base file would export in fixed length or if it did, would be correct.

Paul

#28

Paul D. Mirowsky wrote

Sorry but must advise using a different program as filter for export.
I do not know if importing data to a Base file would export in fixed
length or if it did, would be correct.

Paul

Of course some database engine is by far more suitable, particularly when it
supports fields of fixed lenght characters. As a matter of fact, all text
tables are database data where each row has the same amount of fields, each
field is of one distinct type.
But people are too much addicted to spreadsheets.

#29

Hi Andreas,

I thought I had posted loads of trivial example data. What could be a

Actually if you are referring to:

"So here would be a csv file with delimiter:

As a fixed length ascii file it should look like this:
"

it didn't make it through to the list. However, when viewed from nabble
<http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-td3986825.html>

I see:

<quote>
So here would be a csv file with delimiter:

1;12;123;1234;12;12345
2;2;2;22;2;22222
3;33;33;333;3;3333
4;4;444;4;44;444
5;55;5;5555;5;55

As a fixed length ascii file it should look like this:

11212312341212345
2 2 2 22 222222
333 33 333 3 3333
4 4444 444 444
555 55555 5 55

But unfortunately, LibreOffice inserts some a space in front of each column.

1 12 123 1234 12 12345
2 2 2 22 2 22222
3 33 33 333 3 3333
4 4 444 4 44 444
5 55 5 5555 5 55

</quote>

Yes, as expected:

<Export text files
The Export text files dialog allows you to define the export options for
text files. The dialog will be displayed if you save spreadsheet data as
file type "Text CSV", and if the Edit filter settings check box is
marked in the Save As dialog.>
...
<Fixed column width
Exports all data fields with a fixed width.
The width of a data field in the exported text file is set to the
current width of the corresponding column.
Values are exported in the format as currently seen in the cell.
If a value is longer than the fixed column width, it will be exported as
a ### string.
If a text string is longer than the fixed column width, it will be
truncated at the end.
The alignment Left, Centered, and Right will be simulated by inserted
blanks.>

problem is that you read this in your email program and I am writing the
posts in Nabble, where I can format the example texts and the show nicely,
because I have formatted them with a fixed length font by using the option
"raw text". In your email program it might actually look all skewed and is
difficult to read.

Got it.

Your's is too trivial though. :wink:

Because it doesn't take into account that while there might be 4 digits
reserved for the column, the length of the data in the column might vary
from 1-4 digits. And here is the problem. There are spaces inserted to align
the digits into the column. In theory zeros could be added, but in this case
this is not possible. There must be spaces.

I guess that you are redefining the meaning of a .csv file. While there
are no ' absolute standards' that I am aware of, this is close:

https://www.ietf.org/rfc/rfc4180.txt
Also see:
<https://en.wikipedia.org/wiki/Comma-separated_values#Lack_of_a_standard>
<https://en.wikipedia.org/wiki/Delimiter-separated_values>

E.g. it can't be:

but must be

<quote>
E.g. it can't be:

1234
0001
0002
0010
0999

but must be

1234
   1
   2
  10
999
</quote>

I think Paul got my problem. LibreOffice shouldn't add any spaces in the
first place, as long as it is not told to do. Because the spaces actually do
not exist in the data and are only added for exporting the spreadsheet.

I think you are misunderstanding the tool and attempting to make it
something that it is not. You might be able to do this with a macro (as
suggested by Andreas, but I fail to see how this is Calc's fault/issue.

#30

...

I think Paul got my problem. LibreOffice shouldn't add any spaces in the
first place, as long as it is not told to do. Because the spaces actually do
not exist in the data and are only added for exporting the spreadsheet.

I think you are misunderstanding the tool and attempting to make it
something that it is not. You might be able to do this with a macro (as
suggested by Andreas, but I fail to see how this is Calc's fault/issue.

Sorry, forgot to add these links (they are the same as the info you will
see if you press F1/Help):

<http://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files>
<http://help.libreoffice.org/Calc/Importing_and_Exporting_CSV_Files>
<http://help.libreoffice.org/Common/Export_text_files>
<http://help.libreoffice.org/Common/Text_Import>