Problem when exporting exporting from CALC to a fixed length csv

classic Classic list List threaded Threaded
30 messages Options
Next » 12
berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Problem when exporting exporting from CALC to a fixed length csv

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

I hope this will show up correctly now.
berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

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

So how do I tell LibreOffice to adjust its columns without leaving a space in front???
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

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


--
For unsubscribe instructions e-mail to: [hidden email]
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

Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
Hi,

On 05/30/2012 02:27 PM, berritorre wrote:

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

--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
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

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by Andreas Säger
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.
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv



On 2012-05-31 07:15, berritorre wrote:

> 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.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825p3986844.html
> Sent from the Users mailing list archive at Nabble.com.
>
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

--
For unsubscribe instructions e-mail to: [hidden email]
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

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

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.
m.a.riosv m.a.riosv
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
El 30/05/12 21:15, berritorre escribió:

> 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.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825p3986844.html
> Sent from the Users mailing list archive at Nabble.com.
>

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.

--
For unsubscribe instructions e-mail to: [hidden email]
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

Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
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


On 5/30/2012 2:21 PM, berritorre wrote:

> 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.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825.html
> Sent from the Users mailing list archive at Nabble.com.
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

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.

On 5/30/2012 8:01 PM, Paul D. Mirowsky wrote:

> 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
>
>
> On 5/30/2012 2:21 PM, berritorre wrote:
>> 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.
>>
>> --
>> View this message in context:
>> http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

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


--- On Thu, 31/5/12, Paul D. Mirowsky <[hidden email]> wrote:

From: Paul D. Mirowsky <[hidden email]>
Subject: Re: [libreoffice-users] Problem when exporting exporting from CALC to a fixed length csv
To: [hidden email]
Date: Thursday, 31 May, 2012, 1: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.

On 5/30/2012 8:01 PM, Paul D. Mirowsky wrote:

> 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
>
>
> On 5/30/2012 2:21 PM, berritorre wrote:
>> 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.
>>
>> -- View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>

-- For unsubscribe instructions e-mail to: [hidden email]
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


--
For unsubscribe instructions e-mail to: [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

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

--- On Thu, 31/5/12, Tom Davies <[hidden email]> wrote:

From: Tom Davies <[hidden email]>
Subject: off-list  Re: [libreoffice-users] Problem when exporting exporting from CALC to a fixed length csv
To: [hidden email]
Date: Thursday, 31 May, 2012, 12:40

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


--- On Thu, 31/5/12, Paul D. Mirowsky <[hidden email]> wrote:

From: Paul D. Mirowsky <[hidden email]>
Subject: Re: [libreoffice-users] Problem when exporting exporting from CALC to a fixed length csv
To: [hidden email]
Date: Thursday, 31 May, 2012, 1: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.

On 5/30/2012 8:01 PM, Paul D. Mirowsky wrote:
> 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
>
>
> On 5/30/2012 2:21 PM, berritorre wrote:
>> 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.
>>
>> -- View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>

-- For unsubscribe instructions e-mail to: [hidden email]
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


--
For unsubscribe instructions e-mail to: [hidden email]
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

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by Paul D. Mirowsky
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.
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
Hi :)
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 :)



--- On Wed, 30/5/12, berritorre <[hidden email]> wrote:

From: berritorre <[hidden email]>
Subject: [libreoffice-users] Re: Problem when exporting exporting from CALC to a fixed length csv
To: [hidden email]
Date: Wednesday, 30 May, 2012, 20:15

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.

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

--
For unsubscribe instructions e-mail to: [hidden email]
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


--
For unsubscribe instructions e-mail to: [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by Andreas Säger
Hi :)
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 :) 


--- On Wed, 30/5/12, Andreas Säger <[hidden email]> wrote:

From: Andreas Säger <[hidden email]>
Subject: [libreoffice-users] Re: Problem when exporting exporting from CALC to a fixed length csv
To: [hidden email]
Date: Wednesday, 30 May, 2012, 19:35

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


-- For unsubscribe instructions e-mail to: [hidden email]
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


--
For unsubscribe instructions e-mail to: [hidden email]
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

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

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
Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
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


On 5/31/2012 10:14 AM, berritorre wrote:

> 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.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Problem-when-exporting-exporting-from-CALC-to-a-fixed-length-csv-tp3986825p3987052.html
> Sent from the Users mailing list archive at Nabble.com.
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by berritorre
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: [hidden email]
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

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by Paul D. Mirowsky
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:

 1 12 123 1234 12 12345              12345test_column 01   test_column 04        test_column 05   
 2  2   2   22  2 22222              22222test_column 02   test_column 05        test_column 06   
 3 33  33  333  3  3333               3333test_column 03   test_column 06        test_column 07   
 4  4 444    4 44   444                444test_column 04   test_column 07        test_column 08   
 5 55   5 5555  5    55                 55test_column 05   test_column 08        test_column 09   

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.

berritorre berritorre
Reply | Threaded
Open this post in threaded view
|

Re: Problem when exporting exporting from CALC to a fixed length csv

In reply to this post by Andreas Säger
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. ;-)

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:
1234
0001
0002
0010
0999

but must be
1234
   1
   2
  10
 999

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.
Next » 12