Issue 23078

Summary: calc should open delimited text files no matter the file extension.
Product: Calc Reporter: ericinterplascom <eric>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: cmoulin, issues, marc.galmard, Mathias_Bauer
Version: OOo 1.1   
Target Milestone: ---   
Hardware: PC   
OS: Windows 98   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description ericinterplascom 2003-12-02 05:10:05 UTC
From: "Sander Vesik"> Honestly, I don't presonaly see any reason why one would
want to name a> csv file with a .xls extension or why having special support for
such is> useful. BUtmaybe others do - youmight want to file an RFE in issuezilla

Well, on the server, you create a tab delimited text file - an data export if
you will. And you want your users to be able to open it up.  Well, you could
make a true Excel file, a true Quattro file, or a true Calc file (to much XML
for me) but that's lots extra work.  You simply create one common format - a
delimited text file.  Then you let the users workstation pre-decide (based on
already setup extension association) which program will handle those text
delimited files.

So you say C:>START C:\JUNK.XLS and depending on whether .XLS is registered to
Quattro or to Excel, both will gladly parse the file in as proven here:

> >>
In fact:
> >>
> >>C:>soffice -calc c:\junk.xls  - opens in Writer
> >>C:>excel c:\junk.xls - opens up in Excel
> >>C:>qpw c:\junk.xls - opens up in Quattro

.XLS just happens to be associated to something on a users Windows workstation,
whether it be Excel, Quattro, or Calc.  .CVS isn't associated to anything except
Excel by default (if Excel is installed).  Calc doesn't automatically register
.CVS, neither does QP.  .XLS is the ticket for association launching is
concerned, however it could be other extensions. That's why I'd like Calc to do
the same "trick" Excel and QP does.  If the file is not consistantly delimited,
then yes, let Writer open it up.
-Eric Wood
Comment 1 frank 2003-12-05 12:53:48 UTC
Hi Bettina,

1 4 u

Frank
Comment 2 frank 2004-02-11 14:17:14 UTC
*** Issue 25346 has been marked as a duplicate of this issue. ***
Comment 3 mike2468 2004-07-01 14:42:59 UTC
I would like us to use OpenOffice in our company, but the lack of this feature
is stopping us. We have many tab delimited files produced by other programs and
they do not have a .csv extension. The files can not be renamed or copied to
.csv as we work in a highly regulated environment and our outside auditors do
not allow this.

Could you provide a mapping table of some form for the user to enter their own
file extensions? For example, a user could tell OpenOffice to treat all .rst
files as CSV files.

Thanks

Mike
Comment 4 ooo 2005-03-02 17:26:57 UTC
Note that there is a similar issue 8967.
Comment 5 brucebowler 2005-03-03 18:27:04 UTC
What I'd like to see is a system where if the format of the file cannot be
determined with absolute certainty, the user is presented with a dialog asking
the user what the file is and how it should be opened.  OOo must not make
assumptions about the contents of the file.  
Comment 6 Regina Henschel 2005-07-30 13:19:09 UTC
*** Issue 52635 has been marked as a duplicate of this issue. ***
Comment 7 frank 2006-04-07 14:35:35 UTC
*** Issue 63478 has been marked as a duplicate of this issue. ***
Comment 8 terrynorth 2006-11-10 06:45:51 UTC
Could this issue be extended to all platforms and operating systems, please?
Comment 9 bobharvey 2007-03-23 23:37:00 UTC
There are some very good ideas here - I like the one about a user-specified file
extension table.  That would be better than a second file-open menu entry.  If,
in fact, one could save the column definitions against it so all of them open in
the same format every time it would be one up on the opposition!

I keep falling over this nonsense myself - if I have a delimited file I want to
open in calc I do not want to have to jump through hoops.
Comment 10 Mathias_Bauer 2007-04-19 18:23:42 UTC
The additional dialog exists already but it isn't used as OOo in fact knows the
type of the file - it's a text file! At the end, every tab delimited file is
one. For heavy Calc  users it's unfortunate that text files by default go to Writer.
But even if we showed a dialog for each text file to be opened - nobody would
really like that. Dialogs on opening files can be a PITA.

In OOo 2.2 we already support that every xls file will be loaded in Calc, even
if it isn't an Excel but a tab delimited file. This should bring Calc on par
with Excel in this regard.

You can also register "scalc.exe" for any extension of your tab files in your
system so that doubleclicking them on the desktop will open them in Calc.

The only thing that now is different to Excel is that opening tab delimited
files with the file dialog will need a manual filter selection in Calc if the
file does not have the extension csv or xls.

So while there is something left we made a big step forward. I don't like the
idea of an extension table as filter configuration data is treated "read only"
for a reason.

Comment 11 bettina.haberer 2007-09-26 15:56:53 UTC
Hi Niklas, these RFEs are in your ownership.
Comment 12 smerkley 2008-10-30 20:04:57 UTC
*** Issue 18365 has been marked as a duplicate of this issue. ***
Comment 13 bobharvey 2010-08-01 14:55:30 UTC
I just tried this in 3.2.0 under ubuntu, and a text file called fred.csv opened
in calc just the way I would hope - via an import wizard about delimiters, and
then into separate cells.

The same file called fred.xls opened in calc in the same manner.

fred.csv was opened with the 'comma' delimiter selected by default.  for
fred.xls I had to turn it on by hand, otherwise each row became a single cell.

the same file called fred.txt opened in writer.

It does not look to me as though this problem has been completely run to ground
at any point in the last 7 years, and would bring your attention to the
suggestion from Mar 3 2005.
Comment 14 Mathias_Bauer 2010-08-19 17:07:36 UTC
IMHO this can't be implemented.

As I already wrote years ago, *every* text file is a "delimited" file, as every
line of text contains potential delimiters (spaces, commas, line ends etc.). So
your request is to open all text files in Calc by default. That doesn't make
sense, IMHO.
Comment 15 brucebowler 2010-08-19 18:04:21 UTC
To mba 

> So your request is to open all text files in Calc by default.

My original comment on this was "if there's doubt about how to open the file,
ask the user".  I realize that *could* be more of a problem for the users than I
had first thought.  

What I'd really like to see is if I'm in the "calc" subsystem of OOo and I say
"open file.txt", **DO NOT** open it the "writer" subsystem, stay in the "calc"
subsystem.  Without EXPLICIT actions on the part of a macro, excel *never* opens
a .txt file in word (yes, I know OOo isn't MSO, but if you want to get into
large corporations, you need to "behave" a little bit more like MSO in some cases).

I'll be the first to admit that I'm running a slightly older version of OOo
(3.1) using the RPMs that are supplied as part of Fedora 12. 
Comment 16 Mathias_Bauer 2010-08-20 16:05:57 UTC
I also thought that the "sub system" approach makes sense. When I talked to our
UX people about that some time ago, they didn't like it. Maybe time has changed
their minds?!

A fix to achieve would be not much complicated, IMHO.
Comment 17 hoserjoe 2010-08-21 07:36:50 UTC
Why is this issue still flopping about after 7 years?  It's such a simple
request: if I select Calc to open a text file, Calc should open it - not Writer.
 Not much intuiting is required.  We know what we want, and Calc should simply
carry out the instruction without trying to outsmart the user.  It should open
tab-delimited text files as instructed, just like Excel does.  Tab-delimited
text files are as common as bread and jam in the business world. What is the
reason for the endless foot-dragging?
Comment 18 Mathias_Bauer 2010-08-21 11:27:21 UTC
One reason why this issue still is "flopping" are comments like yours: you don't
know what you are talking about. 

The issue is not about how a document shall be opened if "Calc is selected".
That already works in OOo since years: select a file in your file explorer, use
"Open with" and select Calc, however that works on your system. Or use the
command line and either type "scalc $MYFILE" or "soffice -Calc "$MYFILE".

Here we are talking about OOo *guessing* which application should be used for a
file if it looks as if it might be a good idea to use Calc, but the user didn't
explicitly ask for Calc being used, as the "open file" dialog does not offer a
place where that could be done.

As IMHO it is impossible to differentiate between "delimited" and "not
delimited" text files (as all text files contain "delimiters", see my last
comment), guessing won't work. 

I still think that taking the document in the current window from where an "open
file" dialog is started into account could be an option. That means; if this is
a Calc document, the file being opened should be loaded into Calc, if possible,
Writer should be the second choice. 

It could be a surprise for old users, but it's nothing one couldn't get used to.
Maybe that's what you meant when you talked about "selecting Calc for opening
the document", but that is an inappropriate description of the situation that
only distracts from the real issue.

OTOH: consequently, "csv" files then would be opened in Writer by default if the
"open file" dialog is called from a Writer document, otherwise the behavior
would be completely inconsistent. Is that what we want?

BTW: at least all text files with "csv" and "xls" extension are opened in OOo
with Calc since quite some time (OOo 2.x IIRC). That will solve that issue for
many cases already. 
Comment 19 hoserjoe 2010-08-22 02:51:43 UTC
mba, I think your response indicates something more deep than a simple
misunderstanding.  To me it appears that the problem is "calc should open
delimited text files no matter the file extension"; however, to you it appears
to be something much grander and hugely convoluted.  I'm not sure why you keep
interjecting the issue of "guessing" into the issue.  There is really no
guessing involved at any level.  Calc should not be trying to guess anything. 
If I want Calc to open a tab-delimited text file (generated by my database or
from an html table), no guessing is needed.  Even though it's a table, it
arrives on my desk as a text file. I know what's in the file.  No
"differentiation" is required.  I simply want to load it into a spreadsheet and
get to work. I should be able to do this with two mouse-clicks. Like Excel does.
Comment 20 bobharvey 2010-08-22 11:02:27 UTC
Sounds like linguistic confusion abounds here.

If a user is running calc, they do not necessarily see the file-open dialogue 
as general to OpenOffice, but specific to Calc.  Someone with that world view 
would not expect file-open to cause the file selected to be opened in writer.  
The 'principle of least astonishment' should surely apply.

The file-open dialogue should not be involved in any guesswork, it seems to 
me.  If a text file is selected using file-open from within calc, how is that 
ambiguous? The delimiter wizard can be invoked for all such files.  No guessing 
need be invoked.  The delimiter wizard could even have an 'open in writer' 
button to appease those who like the present system.
Comment 21 mike2468 2010-08-23 09:39:49 UTC
It's amazing that this still hasn't been fixed.

As I wrote 6 years ago, we have many tab separated ASCII data files that do not
have a .csv extension. We would like to open them in calc. We CAN NOT rename
them, change the extension, copy them etc. We work in a highly regulated
pharmaceutical environment and this just isn't allowed! I know that may seem
weird to some people, but that's the way things work when you are dealing with
data where a small mistake or mix up can cost millions or even kill someone.

Many of these files have a .rst extension. Now if I'm in calc and do a File |
Open on one of these .rst files, it should open it in calc. 

If I'm in writer and do a File | Open then the .rst file should be opened in
writer. What is simpler or clearer than that?! It's logical. It's how all modern
programs work. It's how MS Office works!

I think all this sub-system stuff about OOo trying to guess what file it is and
then open the file in the "correct" program is crazy and a fundamental design
flaw. It may have seemed clever originally, but you have to assume that the user
has a brain! If I open a csv file in writer it should open in writer and not
switch to calc.

Comment 22 Mathias_Bauer 2010-08-23 10:19:51 UTC
I agree with the last two comments: if an "open file" dialog is opened in a Calc
document window, Calc should get the preference for text files, otherwise
Writer. As I wrote, I made this suggestion quite some time ago, but this was
rejected by our UX people.

But that isn't what this issue is about: here it was requested that "delimited"
files should be opened in Calc *always*. And I have to repeat that this is
wrong, at least without a clear definition what kind of "delimited" files are
meant to be treated this way.

@hoserjoe: we are not talking about *tab delimited* files only, the request is
about "delimited" files in general (see summary of the issue). And I also have
to repeat that - taken the plularity of delimiter options in  Calc into account
- every text file is a "delimited" file, because they all contains line ends,
spaces, commas etc. So finding out which combination of delimiters qualifies for
Calc preference *is* guess work.
 
Even in case we limit that to tabs, commas and line ends: even in "real
delimited" files it's possible that not all lines have the same number of
delimites. How many lines with a different number of delimiters do we accept
before we don't use Calc by default? One line? Ten? 5 percent? Again it's guess
work. 

We could make it strict: if there is a text file where every line contains the
same number of tabs or commas, it's most probably something we should load into
Calc. But would that be enough to make a difference?

@mike2468: in the meantime, you can register ".rst" extension for scalc.exe
(Windows assumed) in your system. Now every double click on an ".rst" file will
open it in Calc.
Comment 23 brucebowler 2010-08-23 13:51:20 UTC
@mba, a couple of questions and a comment or 2...

Any idea *why* the UK folks rejected this idea?

What do we need to do to get past the "any delimited file" bit that some people
seem hung up on?  Do we need to open a new "bug"?

What I'm looking for is for calc to "behave" the same way excel does.  If I
file/open a file while I'm in excel, it will *always* open the file in excel
(baring some bizarre macro that may be running behind the scenes).  If excel
can't figure out the file format (ie it's not a native XLS file or a file format
like lotus or quattro that it knows how to deal with) it asks the user for help.
 I have the option of telling it "these are the delimiters" or "it's fixed width
and these are the columns on which to break the fields".  

There should be no "guessing" on the part of calc what to do.  Something along
the lines of (really coarse pseudo code).

if findfileformat(inputfile) == something_I_know then
     open it
else
     ask the user about the file
     open it using the user input
endif

If the else path is taken, and it looks like crap, then the "fault" lies with
the user, just like in the excel case.

Bruce
Comment 24 Mathias_Bauer 2010-08-23 14:43:03 UTC
I think initiating a discussion on discuss@ux.openoffice.org could help to get
the ball rolling.

ATM I don't have enough time to follow up there. Starting a discussion without
participating would be stupid and impolite as well. But as we are talking about
a change for version 3.4 the earliest (3.3 had its feature freeze already), we
aren't in a hurry. I can discuss that on the list after OOoCon. Fortunately this
change would require only very little coding work somewhere in the framework code. 
Comment 25 ericinterplascom 2010-08-23 15:15:10 UTC
mba wrote:
"- every text file is a "delimited" file, because they all contains line ends,
spaces, commas etc. So finding out which combination of delimiters qualifies for
Calc preference *is* guess work."

What's so evil about a little guess work?  I have to program IF statements all
the time.

If a file (say, .tab, .csv, .xls, .txt) is explicitly associated with calc, calc
is already doing some guess work to determine the file is not a valid Excel
file, nor any other spreadsheet type file, but a text file instead.  Therefor
the Text Importer dialog comes up.  

Right now, a tab delimited .CSV mismatch is what I'm having to use.

The problem is that I keep having to tell my users to select the TAB checkbox as
the delimiter because the Comma checkbox is always preselected.  OOo is getting
better - Writer doesn't always win anymore but the Comma delimiter checkbox does
to winning now.

What's wrong with calc inspecting the first two lines for three EXTREMELY common
delimiter scenarios in the real business world: 1. test for TAB chars and
pre-select the Tab checkbox, 2. test for Comma+Quoted chars and pre-select that
checkbox, 3. test for Comma chars and select that checkbox, 4. test for XML, 5.
Otherwise select fixed width and let that be the winner.

I'll try not reply back till 2017.  Sincerely though, thanks for you hard work
OOo team!
Comment 26 Mathias_Bauer 2010-08-23 17:08:53 UTC
I agree with your suggestions (but that's probably uninteresting, as I don't
have any stakes in Calc in particular ;-)), but that's something different than
the selection of the application we are talking about. Here we have to guess
wether the file contains delimiters at all (as the suggestion is to select Calc
in case there are delimiters), and this test will always fail for all text files
as all text files contain delimiters.

Type detection and file import filtering are separate processes in OOo, so
before Calc can get a chance to guess the best suggestion for the delimiters, it
needs to become selected as the application of choice for the file. I don't like
to introduce guess work here, IMHO a predictable behavior is better. 

That's the reason why I like the approach to choose Calc as the preferred
application for a file to be opened if the user currently works in Calc and
Writer if the user currently works in Writer. After that it would indeed be a
nice improvement if Calc would try to guess the delimiters instead of using a
default, even if this guess work might fail in some cases. It's only a
suggestion that the user can correct.
Comment 27 Marcus 2017-05-20 11:29:52 UTC
Reset assigne to the default "issues@openoffice.apache.org".