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

Oracle Applications Export to Calc instead of Excel
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
luvr
Newbie
Newbie


Joined: 11 Mar 2004
Posts: 4

PostPosted: Thu Mar 11, 2004 5:09 am    Post subject: Oracle Applications Export to Calc instead of Excel Reply with quote

In our organisation, we use Oracle Applications, which provides an "Export" option to export queried data to Microsoft Excel. This "Export" action downloads the data to the local PC as a text file with a MIME type of "tab-separated-values."
In the Windows registry, MIME type "tab-separated-values" is defined to correspond to the ".tsv" extension, and to the Microsoft Excel executable.
Consequently, whenever we select the "Export" option from Oracle Applications, the data arrives in a temporary ".tsv" file, which automatically gets imported into Microsoft Excel as soon as the download is completed.

Now, as a part of our evaluation of OpenOffice as an alternative to Microsoft Office, we are trying to get the ".tsv" file to open up OpenOffice.org Calc, instead of Microsoft Excel.

I thought that this would be a simple matter of replacing the command that processes the file type with something like the following in the registry:
Code:
[HKEY_CLASSES_ROOT\tab-separated-values\shell\open\command]
@="\"C:\\Program Files\\OpenOffice.org1.1.0\\program\\soffice.exe\" -calc \"%1\""

However, even though I did include the -calc argument string in the registry value, OpenOffice.org keeps insisting that it should load the file into Write instead.

Is there any way to tell OpenOffice.org to load the file into Calc, and not into Write?

Note that we cannot change the MIME type, and we cannot change the file format either, since the files are generated by the Oracle Applications software package, and we have no control over the way that the "Export" action works.
Back to top
View user's profile Send private message
Guest






PostPosted: Sat Mar 13, 2004 6:56 am    Post subject: Re: Oracle Applications Export to Calc instead of Excel Reply with quote

luvr wrote:
In our organisation, we use Oracle Applications, which provides an "Export" option to export queried data to Microsoft Excel. This "Export" action downloads the data to the local PC as a text file with a MIME type of "tab-separated-values."
In the Windows registry, MIME type "tab-separated-values" is defined to correspond to the ".tsv" extension, and to the Microsoft Excel executable.
Consequently, whenever we select the "Export" option from Oracle Applications, the data arrives in a temporary ".tsv" file, which automatically gets imported into Microsoft Excel as soon as the download is completed.


No-one appears to be addresing this issue. First of all, the problem is with association of files, and there is no need to play around in the registry. There are a number of programs to deal with asociation of files. One is aptly called "Associater". Another way, depending on your platform, is to look into the Window directory. There you might still see an old remnant" Winfile.exe". The "File" dropdown menu includes "Associate". However, Winfile will handle only small filenames. So create such a file, associate, then all such will follow, even long filenames.

That said, it is simple to associate these files with a dedicated program like Excel; do it, and it happens. The problem with OOo is that there appears to be a singular program which then calls one or another aspect through an additional parameter, or "switch" (-calc in this instance.) Any attempt to associate seems to call the generic program, and any attempt to redirect to through an additional parameter fails.

A program called "whatfmt" will show each file extension type, and some information. It doesn't show files of type "tsv". Another, called "CSVDb" will allow a lot ofmanipulations with delimiters, but brings in a "tsv" type into a single cell. It will spread a CSV over several cells as it should, and would then allow change of delimiter and other handy operations. The "tsv" type seems ot be out of reach.

So, the problem appears to be the manner in which OOo starts up and switches to one part of the program or another. Since we (I at least) have not a single idea on how or why this is done, there appears to be no suitable solution to the dilemma. I tried simply creating a text tab-delimited file, then associating that and still got a failure, and I wind up thrashing about in the dark. Sorry, OOo people, but trying to avoid any sort of similarity to other programming practices isn't paying off here.

David.
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Sat Mar 13, 2004 9:20 pm    Post subject: Reply with quote

I assume that you wish .tsv to be opened automatically in CALC (CALC can certainly open TSV files for you and they will work as expected).

There is a section in the developer guide which is likely to be the starting point .(.../DevelopersGuide/OfficeDev/OfficeDev.htm#1+1+5+1+Loading+Documents)...
Quote:

ExtendedTypeDetection
Based on the registered types, flat detection is already possible, that is,. the assignment of types, for example, to a URL, on the basis of configuration data only. Tlat detection cannot always get a correct result if you imagine someone modifying the file extension of a text document from .sxw to .txt.. To ensure correct results, we need deep detection, that is, the content has to be examinedThe com.sun.star.document.ExtendedTypeDetection service performs this task. It is called detector. It gets all the information collected on a document and decides the type to assign it to. In the new modular type detection, the detector is meant as a UNO service that registers itself in the OpenOffice.org and is requested by the generic TypeDetection mechanism, if necessary.

To extend the list of the known content types of OpenOffice.org, we suggest implementing a detector component in addition to a filter. It improves the generic detection of OpenOffice.org and makes the results more secure.

Inside OpenOffice.org, a detector service is called with an already opened stream that is used to find out the content type. In case no stream is given, it indicates that someone else uses this service, for example, outside OpenOffice.org). It is then allowed to open your own stream by using the URL part of the MediaDescriptor. If the resulting stream is seekable, it should be set inside the descriptor after its position is reset to 0. If the stream is not seekable, it is not allowed to set it.
Back to top
View user's profile Send private message
Guest






PostPosted: Sun Mar 14, 2004 9:34 am    Post subject: Reply with quote

[quote="dfrench"]I assume that you wish .tsv to be opened automatically in CALC (CALC can certainly open TSV files for you and they will work as expected).
/quote]

How please? I'm curious now. I run Calc, then Open from there a tsv (tab dilineated) file. It opens a new OOo word processor file, containing the data, instead; it does NOT open into the calc program. Even if it did (and again it does not), this is still some distance from an ssociation from within, say, a file manager program.

David.
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Sun Mar 14, 2004 11:12 am    Post subject: Reply with quote

Quote:
I run Calc, then Open from there a tsv (tab dilineated) file.
My .tsv files open directly in calc. There is are a lot of similar posts about difficulties with .csv files which may help.

Quote:
How please?
Building filters and extending the detection capability is beyond my experience. I was just pointing out the route. You might also try the developers' mailing list dev@api.openoffice.org. Subscribe through api.openoffice.org.
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Sun Mar 14, 2004 4:28 pm    Post subject: Reply with quote

I am not sure how to do this automatically, but if you want to open a csv file or a tsv this is what you need to do:

- Open Calc
- go to FILE > OPEN
- In the FILES OF TYPES combo box select the "text csv (*.csv, *.txt)" entry. (You will have to scroll down a bit )
- Then select the file you want to open.
- A dialogue box will open asking you for somemore inofmation. (Click on the tab delimiter.)

This should work...

nom
Back to top
View user's profile Send private message
Guest






PostPosted: Sun Mar 14, 2004 7:36 pm    Post subject: Reply with quote

nom wrote:
I am not sure how to do this automatically, but if you want to open a csv file or a tsv this is what you need to do:

- In the FILES OF TYPES combo box select the "text csv (*.csv, *.txt)" entry. (You will have to scroll down a bit )
- Then select the file you want to open.
- A dialogue box will open asking you for somemore inofmation. (Click on the tab delimiter.)
nom


Thank you, but the file is "tsv", not "csv", and that would incorporate first renaming the file. ALso, the original post claims that the file is run automatically in Excel, or in OOo if the association isa altered.

The problem is twofold:
OOo will not open a "csv", or "tsv" file into calc from calc.
There is no apparent way to associate a csv (or tsv) file with calc, so that it would open automatically into that program from a file manager (double-clicking the file.)

Either way, it opens into the word processor. It is apparently an association problem due to the unusual way this program is developed. At least, that's the problem as I see it presently.

David.
Back to top
luvr
Newbie
Newbie


Joined: 11 Mar 2004
Posts: 4

PostPosted: Mon Mar 15, 2004 1:15 am    Post subject: Reply with quote

Anonymous wrote:
Thank you, but the file is "tsv", not "csv", and that would incorporate first renaming the file.
Not sure here; are you implying that a "csv" file would correctly get opened in Calc? If so, then this will not solve my problem, since Oracle Applications insists on outputting tab-separated values, with a "tsv" extension.
Note that I can open both "csv" and "tsv" files manually in Calc - i.e., after I start up Calc, I can successfully do an import of either a "csv" or a "tsv" file.
Anonymous wrote:
Also, the original post claims that the file is run automatically in Excel, or in OOo if the association is altered.
That's correct - the original registry entries automatically open the file in Excel once it is downloaded. After I modify the registry, as I documented in my original post, OpenOffice will get invoked, but it will open the file in Write, not in Calc.
Anonymous wrote:
Either way, it opens into the word processor. It is apparently an association problem due to the unusual way this program is developed. At least, that's the problem as I see it presently.
Yes, that's apparently what is happening here, as far as I can tell.

--Luc.
Back to top
View user's profile Send private message
Guest






PostPosted: Mon Mar 15, 2004 4:42 pm    Post subject: Reply with quote

luvr wrote:

Anonymous wrote:
Also, the original post claims that the file is run automatically in Excel, or in OOo if the association is altered.
That's correct - the original registry entries automatically open the file in Excel once it is downloaded. After I modify the registry, as I documented in my original post, OpenOffice will get invoked, but it will open the file in Write, not in Calc.
Anonymous wrote:
Either way, it opens into the word processor. It is apparently an association problem due to the unusual way this program is developed. At least, that's the problem as I see it presently.
Yes, that's apparently what is happening here, as far as I can tell.

--Luc.


I think that if you try "Associater", at least your registry manipulations will be unnecessary. With regard to OOo being not readily associated with these files, if a company rep doesn't step into the picture you may be just out of luck. I can see no ready way around this.

David.
Back to top
luvr
Newbie
Newbie


Joined: 11 Mar 2004
Posts: 4

PostPosted: Tue Mar 16, 2004 12:22 am    Post subject: Reply with quote

Anonymous wrote:
With regard to OOo being not readily associated with these files, if a company rep doesn't step into the picture you may be just out of luck. I can see no ready way around this.

Thanks for your reply; I, too, was beginning to realise that a ready solution would be unlikely. We will have to review the issue further wthin the organisation, and then we'll see how we go on.

--Luc.
Back to top
View user's profile Send private message
vbenares
Guest





PostPosted: Wed Mar 31, 2004 10:29 am    Post subject: Reply with quote

Can someone really import tab-separated files into calc? How are you doing it? My repeated attempts to Open the file land it in Writer. Attempts to Insert->External Data cause a window to pop up showing that the file will be correctly parsed, but clicking ok causes a short hang and focus back on the original external data window. Nothing goes into the spreadsheet.
Back to top
guest2
Guest





PostPosted: Mon Apr 12, 2004 11:35 am    Post subject: open tsv in Calc Reply with quote

I can make it cork by opening the file in Excell first and saving it as .xls.
Back to top
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Apr 12, 2004 12:59 pm    Post subject: Reply with quote

vbenares wrote:
Can someone really import tab-separated files into calc? How are you doing it?


Create a text file with
1 <tab> 2 <tab> 3
4 <tab> 5 <tab> 6
Save this file as test.tsv.

In OOo 1.1.0...
1. File --> Open.... (modal dialog appears)
2. Set "Files of type:" to say: Test CSV (*.csv,*.txt)
3. For file name, put test.tsv, even though "tsv" is not one of the suffixes. (You may need to rename the file test.txv if, for example, Linux file dialog box does not allow you to select the test.txv file.)
4. Click Open. (new Text Import dialog box appears)
5. Since all settings on the Text Import dialog box are already satisfactory, just click OK.

New spreadsheet appears with values from the tab separated file imported.

It is also possible to specify a tab seperated (or anything seperated) import via. a macro or via the api, because the FilterOptions
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+Filter+Options
allows this.

In one recent example
http://www.oooforum.org/forum/viewtopic.php?t=6987
someone wanted to export to a CSV, but using "@" signs as the delimiter instead of commas.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Apr 12, 2004 1:01 pm    Post subject: Re: open tsv in Calc Reply with quote

guest2 wrote:
I can make it cork by opening the file in Excell first and saving it as .xls.


If your Oracle application can launch any artibrary command, or maybe a batch file with a command, then you could call OOo with a macro url parameter that would activate a tiny macro (that you previously put into OOo) which would open the file, using any necessary FilterOption string to support a tab separated file.

If you look at this thread
http://www.oooforum.org/forum/viewtopic.php?t=4998
of document conversion examples, some of them show how to do a "conversion" from the command line that calls a small Basic macro in OOo.

A "conversion" simply consists of....
1. open a document using a supported import filter
2. save the document using a supported export filter

If you use a CSV import filter, you will need to specify an additional FilterOptions string parameter to the loadComponentFromUrl() method.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
amerj
Power User
Power User


Joined: 13 Apr 2004
Posts: 69
Location: Zurich, Switzerland

PostPosted: Wed Apr 14, 2004 5:17 am    Post subject: Oracle Applications Export to Calc instead of Excel Reply with quote

Lots of interesting links etc here, but I am now "overloaded" !
(I have a similar requirement to load a - CSV - text file into Calc).
Previous posts suggest calling a macro which would apply the CalcSheet filter .... but HOW is this invoked merely by opening a file ?

Sorry for dumn question, I'm new to OOo Embarassed
_________________
/Jeremy
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