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

Set Default Text Separator
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
StupidScript
General User
General User


Joined: 01 Mar 2007
Posts: 15

PostPosted: Wed Mar 30, 2011 9:32 am    Post subject: Set Default Text Separator Reply with quote

When I paste text into a spreadsheet, I need the same text separator, all the time. I need a quotation mark ("). I also need the same field delimiter. I need a tab (\t).

In OO.org 2.4, once I selected the tab delimiter and the quotation mark separator one time, those choices remained active for all subsequent actions. Even after the computer was rebooted, the tab delimiter and quotation separator choices I had made earlier remained as the defaults.

How can I establish those same defaults in OO.org 3.3? It seems like I need to select them every time I paste something, and I cannot discover a setting in Customize... or Options... that allows me to pre-define them so that the tab delimiter and quotation mark separator are the default settings for every paste.

Thanks for any help.



Moderation probe1: moved to CALC section
Back to top
View user's profile Send private message
StupidScript
General User
General User


Joined: 01 Mar 2007
Posts: 15

PostPosted: Tue Apr 12, 2011 10:06 am    Post subject: Reply with quote

*bump* Anyone? Is there a way to set the default import delimiters and whatnot? Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Apr 12, 2011 10:34 am    Post subject: Reply with quote

Paste your text lines with tabs and quotes into a text editor.
Save the text in a dedicated directory.
Collect all similar text files with tabs and quotes and the same name extension in that directory.

File>New>Database...
[X] Connect to existing database
Type: Text
Specify the directory, the name suffix and the delimiters for all similar files to be dumped in that directory.
[X] Register the database.
Save the database

Open a spreadsheet, hit F4, drag some table from the left pane into the sheet.
menu:Data>Define...
Select "Import1", hit [More Options] and check the "insert" and the "formatting" option, hit [Modify] and [OK].

Format the cells anyway you want, add formulas adjacent to the import range, charts, conditional formattings, data pilots, whatever you want to do in the context of the import range.

Save the file as a template (File>Templates>Save...).

######### End of setup # Let's use it now ########

Paste new incoming data into Notepad.exe or whatever and save in that directory.

Create a new document from the template [Ctrl+Shift+N]

Hit F4, drag the new table anywhere over the existing data, confirm to replace the data.
See how your adjacent formulas, charts, conditional formattings adjust to the newly imported data.

If you always work with the same text file, replacing old data, you use a fixed document instead of the template, replace the old text file with a new one and refresh the import range (menu:Data>Refresh)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
StupidScript
General User
General User


Joined: 01 Mar 2007
Posts: 15

PostPosted: Tue Apr 12, 2011 12:07 pm    Post subject: Reply with quote

Wow. Thank you for your instructions, but that's definitely overkill.

In 2.4, here's what I did:

1) Launch Calc
2) Copy all from new, unsaved file in text editor
3) Paste into Calc (defaults to empty spreadsheet)
4) Save the spreadsheet

Since the defaults were set, I needed to do nothing else.

So, at this point, can we say that there is NO way to define default delimiters within the program?

Nothing in any of the config files?

I appreciate your explanation, but this does not set default delimiters ... it takes less time just to manually select the tab and quotation mark delimiters during pasting!

Various 3.x "upgrades" to various OO.org behaviors seem arbitrary, like this default delimiter issue and, for another example, using the Data => Sort... mechanism defaults to the LAST column heading in the selection, rather than the FIRST, which 2.4 used, so now I also need to change what I do in THAT sequence, each time.

Maybe I meant "capricious" instead of "arbitrary" ... certainly not "better".

I really do appreciate you taking the time to document all of those steps, but I'm looking for something ... more ... um ... default, if it exists. Thank you.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Apr 12, 2011 2:03 pm    Post subject: Reply with quote

Quote:
Nothing in any of the config files?

I told you how to set all kinds of defaults in a configuration file for one directory of text similar files. The Base document is a configuration file.
You will get by far more features for less effort. You will never again fill out that text import dialog. Hit F4, drag&drop your table into preformatted documents (Writer as well).
If you use only one text file and one spreadsheet document you open the spreadsheet, call Data>Refresh and have the data with all the spreadsheet features updated.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ftack
Moderator
Moderator


Joined: 27 Jan 2003
Posts: 3102
Location: Belgium

PostPosted: Tue Apr 12, 2011 11:54 pm    Post subject: Reply with quote

There nevertheless is good argument to save the user's settings in that dialog, at least for the current session. If a user occasionally need to insert a couple of text data, then what is the point of having him enter the settings he just entered minutes ago over and over again? In the same realm, a file-open dialog could always stick tot he same directory. What an irritation would it be to be forced to have to tell the program each time again "no, the files of my current project are stored *there*".
_________________
--
GNU/Linux (Ubuntu)


Last edited by ftack on Wed Apr 13, 2011 12:19 am; edited 1 time in total
Back to top
View user's profile Send private message
ftack
Moderator
Moderator


Joined: 27 Jan 2003
Posts: 3102
Location: Belgium

PostPosted: Wed Apr 13, 2011 12:19 am    Post subject: Reply with quote

The issue has been filed in 2007, but nothing has been done about it: https://openoffice.org/bugzilla/show_bug.cgi?id=77950
_________________
--
GNU/Linux (Ubuntu)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 13, 2011 12:38 am    Post subject: Reply with quote

Unlike MS Office, which is storngly focussed on a programmatic approach, this one uses a more administrative approach. Where MSO suggests VBA code or very sophisticated tools and options, this one is based on templates, styles and links.

Yes, of course there are good reasons to save dialog settings. Version 3.3 stores (most of) the text import options for csv files but not for paste-special nor text-to-columns.
That leads us to another solution of this "problem". First tip is the same: Paste into a text editor and save to a file. OOo 3.3 keeps the import settings when you open the file.
Quote:
If a user occasionally need to insert a couple of text data, then what is the point of having him enter the settings he just entered minutes ago over and over again? In the same realm, a file-open dialog could always stick tot he same directory. What an irritation would it be to be forced to have to tell the program each time again "no, the files of my current project are stored *there*".

If the user needs to do that occasionally, the misbehaviour is no big deal. It simply does not deserve any more time to be wasted than filing a bug report.

The configuration I suggested can be implemented for many different flavours of text tables so you can store more than one set of import options. Then you won't have to call a file-open nor import-text dialog again.

1. All you have to browse is [Data Source Name]>[Tables]>[Table Name]
2. Now you can drag the whole stuff into your preformatted stuff or you can apply an ad-hoc filter and/or sort order before you drag stuff into your Calc/Writer document.
3. Import ranges are dynamic like no other spreadsheet feature. The references in your formulas and charts shrink and grow with the resizing import range on every update. SUM(A1:A99) becomes SUM(A1:A100) when the import range grows by one row.
4. Adjacent formula ranges do resize as well.
Price*VAT in X2:X99 spreads over X2:X100
The sophisticated chart you have created once does not need to be adjusted at all. It refers to the modified row set.
5. It may be not that important, but once you understand how and why to store query definitions in the Base configuration, you may solve a lot more frequently asked questions from the spreadsheet forums. Queries can be used exactly like tables (drag&drop). They can return a table in a defined order of rows and columns, with modified column names, with predefined filter conditions or even prompting for filter conditions without a single line of macro code. And yes, a query on a text file can skip duplicate rows.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 13, 2011 12:48 am    Post subject: Reply with quote

ftack wrote:
The issue has been filed in 2007, but nothing has been done about it: https://openoffice.org/bugzilla/show_bug.cgi?id=77950


Quote:
Description From oreg 2007-05-30 16:08:54

The process for "Insert->Sheet From File..." could be improved:

The "Text Import" dialog should remember selections from the last import.
Currently you have to adjust them over and over again for each file.

This is not true anymore. It has been fixed for files in v3.3.

Which leads us to another method to save text import options:
Paste in your text editor, save as file.
Get a saved spreadsheet document.
menu:Insert>Sheet from file... with "Link" option. Import once, refresh on file open or in menu:Edit>Links
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
StupidScript
General User
General User


Joined: 01 Mar 2007
Posts: 15

PostPosted: Thu Apr 14, 2011 10:07 am    Post subject: Reply with quote

Villeroy, you seem quite well-informed about OO.org and obviously have deep insight into using templates. I appreciate your expertise.

Not to diminish the excellent suggestions you have been making, however my need is to continue using simple processes that I have used for years with this new version of OO.org which has, for some reason, removed the very simple features that made my processes simple.

Using 2.4, I could copy text from an unsaved text file, paste it into Calc, and my setting from the previous session would already be in place.

I only needed to copy, paste, save.

Again, with deep appreciation for the processes you have outlined, to suggest that those database templating processes are anything near as simple as my previous processes is to miss my point.

Simplicity, not power, is what I need.

Copy, switch apps, paste, save. 4 key combinations. No browsing. Simple.

Given your knowledge of OO.org, could you perhaps explain the rational for v.3.3 eliminating the default behavior of saving paste settings during and between sessions? I'm sure that if I understood more about WHY OO.org engineers deemed it important to remove that basic, common functionality I would have an easier time justifying the rather large changes to my simple processes that you have proposed.

As it stands, bless you for your detailed instructions, it is faster and easier to select "tab" and "quotation" from the paste dialog box ... it's just not AS fast as using v.2.4 to do identical work, where no such selection was required. 6 actions instead of 4.

Thank you for your continuing illumination of power-user techniques, and hopefully some clarification of this fundamental OO.org design change decision. (I would not call it a "bug", because it is obviously intentional, so there must be some usecase rationale.)

BTW, it may seem silly, to you, but this really IS a "problem". When I have 300 operations to execute, and the latest version of my key piece of software forces an extra 2 seconds into each operation, that's 600 seconds ... 10 minutes ... that I did not need to expend previously. Multiply that times the number of other people who need to change their own processes, and you get a serious chunk of man-hours that OO.org is now taking from the workday that it did not do, previously. I'm just saying ... in MY world, that is important.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 15, 2011 3:39 am    Post subject: Reply with quote

Quote:
Copy, switch apps, paste, save. 4 key combinations. No browsing. Simple.

All I suggest is that you do exactly the same with a text editor.
There are also excellent text editors for csv, such as http://csved.sjfrancke.nl/

Quote:
BTW, it may seem silly, to you, but this really IS a "problem". When I have 300 operations to execute, and the latest version of my key piece of software forces an extra 2 seconds into each operation, that's 600 seconds ...

Whatever you do with the pasted data, I bet I can do faster and with fewer clicks.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
StupidScript
General User
General User


Joined: 01 Mar 2007
Posts: 15

PostPosted: Fri Apr 15, 2011 10:21 am    Post subject: Reply with quote

Quote:
Whatever you do with the pasted data, I bet I can do faster and with fewer clicks.


Just ... wow.
Back to top
View user's profile Send private message
lstandish
Newbie
Newbie


Joined: 21 Nov 2006
Posts: 1

PostPosted: Mon Oct 17, 2011 8:28 am    Post subject: Reply with quote

I second StupidScript's suggestion: OpenOffice calc should remember the last-used field delimiter (like the older OO versions did) for PASTED data, or allow one to set a default. I don't want to have to save my data to a file before importing into my spreadsheet. Copy/paste is simpler and faster for the data I handle, which arrives via email.
Back to top
View user's profile Send private message
LGLisle
General User
General User


Joined: 28 Nov 2011
Posts: 5
Location: W-S NC USA

PostPosted: Mon Apr 16, 2012 12:48 pm    Post subject: Reply with quote

Villeroy wrote:

Whatever you do with the pasted data, I bet I can do faster and with fewer clicks.


That's nice for you, but doesn't answer the question.

I copy the text from a web page to paste into a spreadsheet, which I then manipulate.

I don't see how a text editor is going to do anything for me.
_________________
Using OO 3.3.0 on Windows 7/64
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Apr 17, 2012 6:32 am    Post subject: Reply with quote

Quote:
That's nice for you, but doesn't answer the question.

I have answered this a hundred times in 3 different ways.
Quote:
I don't see how a text editor is going to do anything for me.

A text editor stores raw data in plain text. A spreadsheet template can store preformatted areas. So you can import raw text data into your own preformatted cell range.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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
Goto page 1, 2  Next
Page 1 of 2

 
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