OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How do you save as Fixed length with n characters per column

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
colingbradley
General User
General User


Joined: 23 Jun 2012
Posts: 5
Location: London, U.K.

PostPosted: Sat Jun 23, 2012 6:49 am    Post subject: How do you save as Fixed length with n characters per column Reply with quote

I cannot see how you can set the column width in number of characters.
Without that, it seems impossible to have an accurate fixed length output file.

I copied an Excel file with column widths set in characters but when using Oo I get several fields misplaced, always +1 character for the longer width columns.

What is the answer to this dilemma?
_________________
Experience is what you get when you hoped to get money
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jun 23, 2012 7:06 am    Post subject: Reply with quote

http://csved.sjfrancke.nl/
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
colingbradley
General User
General User


Joined: 23 Jun 2012
Posts: 5
Location: London, U.K.

PostPosted: Sat Jun 23, 2012 11:22 am    Post subject: Reply with quote

OK, downloaded and managed to set up the column widths and save as a fixed length.
Now, I need to keep the setup as a template.
As far as I can see, the CSV editor has no native format to enable that to happen.

So I am no further fwd.
I need to be able to set up a template with fixed column widths so I can change the data and know that the start position will always be the same.

It looks like a need to write some code but what a palaver.

I have several of these to set up.

There must be a better way.
_________________
Experience is what you get when you hoped to get money
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jun 23, 2012 5:10 pm    Post subject: Reply with quote

Calc is a calculator in the first place. It is not a database nor text editor.

Ensure that each cell text has the right length (Data>Validation? conditional formatting?)
Save as delimited text but do not specify any delimiter.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
colingbradley
General User
General User


Joined: 23 Jun 2012
Posts: 5
Location: London, U.K.

PostPosted: Mon Jun 25, 2012 1:25 am    Post subject: Reply with quote

Calc (or so I thought) was very much like Excel.
Excel and Calc can be used for setting up templates when giving the users a model of how to prepare data so that it can be exported in an approved format and then imported into the ERP system.
Accounting people use it in this way in business.
preparing budgets, accrual journals or applying company credit card transactions and so on.
The use of Calc or Excel as a calculator is a given but that is not what most of my clients use it for.
Data can be prepared and exported/imported and also to extract data from the ERP business and accounting database to enable final reports.
Agreed, that is when calculations are needed.
Anyway, thanks for the suggestion but from my latest version of Calc, there is Data/Format/Conditional formatting which has nothing to do with establishing a fixed number of ascii characters.
When saving As csv, there is an option to save as fixed length which automatically removes the option of delimiters.
What remains is the seemingly insurmountable problem of setting a column width to a specific number of characters.
I use the Save As fixed with what i have but depending on the width of the columns of what was 50 characters in Excel, I usually get 50 or 51.
Perhaps I will have to fine-tune the number if millimeters.
I will try that in the absence of any other suggestion.
Thanks,
Colin
_________________
Experience is what you get when you hoped to get money
Back to top
View user's profile Send private message
colingbradley
General User
General User


Joined: 23 Jun 2012
Posts: 5
Location: London, U.K.

PostPosted: Mon Jun 25, 2012 4:32 am    Post subject: re: http://csved.sjfrancke.nl/ Reply with quote

I downloaded it but it has no means of saving the format that I can see.
I need to be able to provide templates for the users.
If you upload a csv file, it allows you to make some specification setups such as the column width but then what?
The only option I could see was to Save As fixed length but not to save as template.
This is an interesting but otherwise unsuitable app.
But, thanks for sharing.
Colin
_________________
Experience is what you get when you hoped to get money
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jun 25, 2012 5:14 am    Post subject: Reply with quote

Villeroy wrote:
Calc is a calculator in the first place. It is not a database nor text editor.

Ensure that each cell text has the right length (Data>Validation? conditional formatting?)
Save as delimited text but do not specify any delimiter.

So the text editor does not help and boybawang posted only to drop his spamspam spam.

Quote:
Anyway, thanks for the suggestion but from my latest version of Calc, there is Data/Format/Conditional formatting which has nothing to do with establishing a fixed number of ascii characters.


At least it can highlight wrong text lenghts.

So the text editor could not help and a database is too complicated although it could nail down the problem.

Exporting delimited text without delimiter works well.

I wonder why the ERP system can not handle column delimiters and why text files are required to communicate with that system and why spreadsheet applications are required suite to edit text files.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
colingbradley
General User
General User


Joined: 23 Jun 2012
Posts: 5
Location: London, U.K.

PostPosted: Mon Jun 25, 2012 5:53 am    Post subject: Reply with quote

Please don't get annoyed.

I use the Save As csv and specify my own delimiters if I am doing data transfer of millions of records from one business system to another.
I love Calc for that, big fan.

I use the Excel TAB format when the text may have commas in the descriptions. This is probably the most used for setting up templates as most users have a copy of Excel.

When the ERP system (Microsoft Dynamics NAV) needs to import a file via a File handling mechanism other than the Dataports (which can deal with any format), I must state the exact start of the field and the exact length.
The folder that holds the files is populated by an automated web application with details of web sales. The files must end in .txt and be exactly as per the specification.

My need was for a simple tool to enable me to test the fixed length functionality of the .txt files in the folder by using Calc. The reason I did not use Excel is that the total string length was over 1,000 characters and the Text option in excel wraps the string.
I have now changed the method and have maximum string lengths of less than 1,000 characters.

btw, when I used the tool suggested and opened a .csv file, there was no indication of the column width. all it indicated was 1 for each column.

Cheers,
_________________
Experience is what you get when you hoped to get money
Back to top
View user's profile Send private message
floris_v
Moderator
Moderator


Joined: 12 Jul 2007
Posts: 4791
Location: Netherlands

PostPosted: Mon Jun 25, 2012 8:28 am    Post subject: Reply with quote

Villeroy wrote:

So the text editor does not help and boybawang posted only to drop his spamspam spam.


Taken care of.
_________________
LibreOffice 3.6.3; OOo 3.4.1 on Windows Vista
Join the Official community forum - in several languages, including Nederlandstalig forum
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group