Problem when exporting exporting from CALC to a fixed length csv

users
#1

I need to generate a fixed-length ascii/text file from a table that I have.
Fixed length ascii seems to be the format to import in a software my client
uses.

So I was fiddeling around and hat a lot of problems. Excel would export to
.prn just as I need it, but only for for 255 characters per line. I have a
few thousand columns in my spreadsheet with quite a few columns with more
than one character.

In LobreOffice I found the option to export to csv with fixed length and
actually this would be what I am looking for. However, Libreoffice seems to
add a space to separate columns, which just doesn't fit with fixed-length
ascii, as they shouldn't have separators/delimiters. I can't just replace
the spaces with "nothing", as there need to be spaces in the file to fill up
the columns when in a column there can be for example up to 5 characters,
but sometimes there are less.

So here would be a csv file with delimiter:

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

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

I hope this will show up correctly now.

#2

I just did some additional testing and it actually seem to be a formatting
problem with the LibreOffice Tables!

Before I had minized the columns to the width of each column (highlighted
and doubleclicked the columns so they would adjust). But somehow they never
adjust perfectly. There is always a little space in front.

So what I did was increasing the width of some columns a little bit and when
you export the file to fixed-width csv, this is what comes out:

So how do I tell LibreOffice to adjust its columns without leaving a space
in front???

#3

Spreadsheets are horrible text editors.
Try this one instead: http://csved.sjfrancke.nl/

#4

Hi,

I just did some additional testing and it actually seem to be a formatting
problem with the LibreOffice Tables!

Before I had minized the columns to the width of each column (highlighted
and doubleclicked the columns so they would adjust). But somehow they never
adjust perfectly. There is always a little space in front.

So what I did was increasing the width of some columns a little bit and when
you export the file to fixed-width csv, this is what comes out:

So how do I tell LibreOffice to adjust its columns without leaving a space
in front???

--
View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825p3986826.html
Sent from the Users mailing list archive at Nabble.com.

I think the fixed length inserts a space for reading and to help delimit
the column. All columns are x characters wide with a leading space.

Most programs I have seen that import from various text/csv type files
allow one to specify the delimiter when importing including using TAB,
semicolon, etc.

Looking up the .prn file extension indicates that is most commonly
generated when using "Print to text" and allows printing of the document
without needing software capable of opening the original file.

#5

Thanks for your response.

I have tried CSVed before. Actually, I don't really want to work with the
csv files. Actually doesn't even need to be a csv file. Any ASCII text file
probably does.

It just has to have the correct format. So my client sends me a data map
where he specifies the exact width for each column. E.g.
Variable 1: Positions 1-4
Variable 2: Positions 5-6
Variable 3: Positions 7-10

My software generates csv files with ";" as delimiter which works perfect
for importing in Calc or Excel. But then I have to follow my clients
requests and the document has to have exactly the required format. So what I
do is I add a row at the top with the max. number of characters for this
specific variable, e.g. if it would be variable 1 from the example above,
the first line would look like this: "1234". This is how I guarantee the
length.

I found a software that is generating .sdf files from csv files (called
Convert). But actually I would like to do it directly from a spreadsheet
software, to avoid another software. Libreoffice almost does it right. It
just takes the visible width of the column as the width to export and not
the max. characters in a column.

When I adapt the column width manually to the minium which still allows to
see all characters Libreoffice exports correctly!!

So I would just need to find a way to avoid this little visible space on the
left of the cell. Excel adapts the columns correctly and doesn't add space
to the left, but minimizes the column to exactly the width necessary to show
all characters, but then Excel doesn't allow to export fixed field csv
files.

#6

Hi.
What systems are you running. I seem to be (or was) doing exactly what you want from our accounting system. The accounting system outputs delimited file with ; and I use a perl script to pad the data to fixed length. All automated by a cron job.
steve

#7

Hi!

I am running on Windows.

I am sure that with a script this could be solved easily. Actually I have
found some Excel-Macros that are supposed to do this as well. And I have
found a solution with an additional software. But as this is something any
assistent/intern here should be able to do, I thought it would be great if
the spreadsheet software does it already and LibreOffice is basically there.
Problem is the strange behaviour it has.

I find both facts fairly odd:
--> Using the visible column width for the export width
--> Adding a space to the left when the column is minized (it should
actually just fit the necessary size).

But I guess both is somehow intended behaviour. Otherwise I could just write
a bug report.

#8

When saving as CSV there is an option to edit filter settings, one is fixed column with. (Click the option and follow saving).

In the help.
"
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.
"

Miguel Ángel.

#9

The following is the output of a Calc test document.
Clicked on upper left block to select entire sheet.
Right click on top of column and selected <Format cells>
Font tab - font to fixed length font, Letter Gothic.
Border tab - spacing to font set at zero (0)
Clicked on upper left block to select entire sheet.
Menu: <Format><Column><optimal width>Add 0"
The results copied here show that there is definitely 2 spaces being added after each column. Although you can't see it, go to the end of the last column and you can backspace twice.
It is not accurate to say that Calc uses the width of the column.

test_column-A1 test_column-B25 Test_column-C50 Test_column-D75
test_column-A2 test_column-B26 Test_column-C51 Test_column-D76
test_column-A3 test_column-B27 Test_column-C52 Test_column-D77
test_column-A4 test_column-B28 Test_column-C53 Test_column-D78
test_column-A5 test_column-B29 Test_column-C54 Test_column-D79
test_column-A6 test_column-B30 Test_column-C55 Test_column-D80
test_column-A7 test_column-B31 Test_column-C56 Test_column-D81
test_column-A8 test_column-B32 Test_column-C57 Test_column-D82
test_column-A9 test_column-B33 Test_column-C58 Test_column-D83
test_column-A10 test_column-B34 Test_column-C59 Test_column-D84
test_column-A11 test_column-B35 Test_column-C60 Test_column-D85
test_column-A12 test_column-B36 Test_column-C61 Test_column-D86
test_column-A13 test_column-B37 Test_column-C62 Test_column-D87
test_column-A14 test_column-B38 Test_column-C63 Test_column-D88
test_column-A15 test_column-B39 Test_column-C64 Test_column-D89
test_column-A16 test_column-B40 Test_column-C65 Test_column-D90
test_column-A17 test_column-B41 Test_column-C66 Test_column-D91
test_column-A18 test_column-B42 Test_column-C67 Test_column-D92
test_column-A19 test_column-B43 Test_column-C68 Test_column-D93
test_column-A20 test_column-B44 Test_column-C69 Test_column-D94
test_column-A21 test_column-B45 Test_column-C70 Test_column-D95
test_column-A22 test_column-B46 Test_column-C71 Test_column-D96
test_column-A23 test_column-B47 Test_column-C72 Test_column-D97
test_column-A24 test_column-B48 Test_column-C73 Test_column-D98
test_column-A25 test_column-B49 Test_column-C74 Test_column-D99

#10

Feel like idiot

e-mail strips end spaces.
Dohhh!!!

Generate the same data in calc, save it as a cvs with fixed format. I'm pretty sure when you open it with either Notepad or Writer (don't forget to change it to a .txt document for writer) you'll get same results.

#11

Hi :slight_smile:
Don't worry.  Almost everyone uses an imperfect email client and the list can't always cope with all the foibles.  So, we have all fallen foul of some nasty trap that is outside of our control.  No apology is necessary but it's nice to see.

Nicely handled!
Many thanks and regards from
Tom :slight_smile:

#12

Hi :slight_smile:
Dohhh!  That wasn't off-list after all!
Apols and regards from
Tom :slight_smile:

#13

Hi Paul!

I am not sure if I can follow.

In my few tests Libreoffice exportet interpreted the fixed length as the
width of the cell showing in CALC.

Unfortunately for me CALC somehow gives a little space to the left when
presenting the data and thus in the export there is a space. When I manuall
adapt the width and get rid of the little white space in front of my content
it exports just fine, without the unnecessary space. But with about
1000-2000 columns manually adapting the width is not an option.

In your case it probably added the spaces at the end because your data in
the cells is formatted as text (left aligned) while mine are number (right
aligned). So I don't see much difference.

When you open up the width of the cell, Libreoffice adds many spaces. I'll
do some testing with OpenOffice and with the newest version of LibreOffice
today.

I'll also try to follow your instructions above.

#14

Hi :slight_smile:
Isn't there an option for "tab delimiter"?  Would that help?  I'm not sure it would tbh but it might be what 'they' are looking for. 
Regards from
Tom :slight_smile:

#15

Hi :slight_smile:
Aaargh!!  Why not Emacs or Vi?  or Gedit or SciTe?  I'm quite enjoying Gedit at the moment but used to prefer SciTe. 
Regards from
Tom :slight_smile:

#16

Hi Andreas,

As I said, it is not a problem about editing the text file.

I need to export the data of a spreadsheet into a certain ASCII format. The
file has 1000-2000 columns and 350 rows. I don't see how a text editor can
help with this. But maybe I just don't know thos editors well enough.

Best regards,
Holger

#17

My response was partially to the statement quoted by Miguel Angel
"
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.
"
The test disproved that "/The width of a data field in the exported text file is set to the current width of the corresponding column. " as it did add 2 spaces /after each column. Whether this is added after or before except 1st column, or a combination is a programmatically unknown to me.

In essence, by doing what I did, there should be no spacing around any edge of the text as an offset of the column width and since the text is always supposed to be fixed, it cannot be misconstrued as variable of column width. 10 cpi is 10 cpi.

If you run the same test with numbers, am curious as to answer.
Would advise 1st test as number without decimal or comma or any special character and of equal length. Would not want multiple behavior to interfere at first. If it also creates 2 additional spaces, it is very important information.

Hope this helps

#18

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

#19

Hi Paul!

So I did some more testing with the latest version and I still persist:
LibreOffice uses the visible width of the column to determine the fixed
width!

Here is what I did:
- Followed your instructions on how to format the cells (had done this
before already!).
- Included some text columns
- Marked everything and double clicked the column header row to adjust the
width of the columns automatically to their content.
- Made one of the columns with numbers and one of the columns with text
wider to show the effect, left the others with the automatically adjusted
width:

So what happens is the following: LibreOffice adds 1 space to the left of
each cell containing numbers and it adds actually 3 spaces to the right of
each cell containing text!

Here a screenshot. Marked with a red circle are the "problematic" areas.
Those spaces in the cells Libreoffice seems to export as well, which would
be OK if adjusting the columns automatically would adjust correctly, but
there are always those spaces.

http://nabble.documentfoundation.org/file/n3987100/libreoffice.png

#20

Hi Andreas,

I thought I had posted loads of trivial example data. What could be a
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.

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.

E.g. it can't be:

but must be

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.