Apache OpenOffice (AOO) Bugzilla – Issue 23078
calc should open delimited text files no matter the file extension.
Last modified: 2017-05-20 11:29:52 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
Hi Bettina, 1 4 u Frank
*** Issue 25346 has been marked as a duplicate of this issue. ***
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
Note that there is a similar issue 8967.
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.
*** Issue 52635 has been marked as a duplicate of this issue. ***
*** Issue 63478 has been marked as a duplicate of this issue. ***
Could this issue be extended to all platforms and operating systems, please?
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.
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.
Hi Niklas, these RFEs are in your ownership.
*** Issue 18365 has been marked as a duplicate of this issue. ***
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.
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.
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.
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.
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?
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.
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.
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.
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.
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.
@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
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.
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!
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.
Reset assigne to the default "issues@openoffice.apache.org".