Apache OpenOffice (AOO) Bugzilla – Issue 1834
Q-PCD affiliate: Make insertion of external spreadsheets easier and more clear
Last modified: 2013-08-07 15:15:02 UTC
Ladies and Gentlemen, I have text files that contain data which are separated with spaces and tabs like: README TXT 315 4.096 09.04.01 18:41 04.10.01 A readme.txt LICENSE TXT 6.066 8.192 17.07.01 15:37 04.10.01 A license.txt I want to insert the text files into a spreadsheet so, that the data which is separated by spaces are then in separate fields. I mean column A contain README and LICENSE and an other column contain 315 and 6.066 and an other column contain 09.04.01 and 17.07.01. Normaly I have to go to Insert -> External Data -> and choose the file with the ...-button. In the appearing window (Text Import) I choose the sepatator options and click the ok-button. The windows closed and the former (External Data) appeared and contain the url to the file (file:///G:/test.txt). (I don't know if this is the correct format on a windows machine). The other box (Availible tables/ranges) is empty and the ok-button is disabled. I can only click to cancle or help. How can I import external data from a text file? Best regards Marcus Daub m.daub@web.de
*** Issue 1835 has been marked as a duplicate of this issue. ***
Hi, this isn't working with text files. Works only with spreadsheet and HTML files that contain objects (ranges/sheets) with a name or an ID. PJ->FT: We really need to think about a redesign of the file open dialog (and the file types that are offered) because lot of people seem to stumble at this point. I'm also not too happy with the help item offered for this function. Regards, Peter
Priority was much too high. P1 would mean that we lost Calc completely.
->enhancement
Please open those files via "File open" - "Text CSV" filter.
*** Issue 2164 has been marked as a duplicate of this issue. ***
*** Issue 2646 has been marked as a duplicate of this issue. ***
Closed. The external data dialog cannot be used for this stuff. Hopefully the ambiguous dialog will be redisigned soon. Regards, Peter
*** Issue 3996 has been marked as a duplicate of this issue. ***
*** Issue 4410 has been marked as a duplicate of this issue. ***
*** Issue 4598 has been marked as a duplicate of this issue. ***
*** Issue 5184 has been marked as a duplicate of this issue. ***
*** Issue 5218 has been marked as a duplicate of this issue. ***
*** Issue 6624 has been marked as a duplicate of this issue. ***
may I select ALL for both 'OS' and 'Platform'? also 'Version' could be ALL if available or 1.0.0/1 Quote: >-- Additional Comments From pj@openoffice.org 2002-04-08 05:24 PDT -- >Closed. The external data dialog cannot be used for this stuff. >Hopefully the ambiguous dialog will be redisigned soon. >Regards, Peter Judging from the no. of problems here, I'm reopening as enahancement suggestion.
Hi Lars, this Issue was closed by the feature guys. So this will not be discussed any longer even if you reopen it. So I close it. This Dialog is not intended to use for CSV Import. Frank
closed
Hi, in the one hand there is a work around for this problem. Use insert sheet to insert a local csv. in the other hand I reopen it, because it should be possible to use a csv file as external data, because it is given on some webpages and updated there. Sascha PS: Falko, you get it like discussed before.
my attention was more on the "ambiguous dialog"; so in order to prevent you from getting more issues about this the idea is to document the function and "design" the dialog better (it's ok to not be able to import csv that way, if that's made clear by the app.!)
Team -- My own personal need is limited to import of HTML files containing table data. (Issue #6624 for this was marked as a duplicate of this one, and then separated again) <observation> I have not been able to get the "OK" button to become active for **ANY** type of file entered into this dialog. Using File->Open (from Calc) and selecting an HTML file causes the file to be opened in Writer. Using New->Sheet and specifying an HTML file creates a new, empty sheet. </observation> <opinion> The help for this dialog would lead the casual user to believe that this dialog is indeed broken. My vote would be to either take whatever actions are necessary to make the functionality match the documentation, or to improve the documentation (if indeed this dialog could be considered to be "working as desired"). </opinion> I am running version 1.0.1 under Win98. PS: Thanks for a wonderful system.
(Copied from Issue #6624 - "cannot import external HTML tables") If you rename your html file AND REMOVE THE EXTENSION ENTIRELY, then the dialog will read the file, and apparently auto-intepret the contents. Two options are then presented in the "Available tables/ranges" field, "HTML_all" and HTML_tables". After selecting either of these, the OK button becomes enabled. (See attachment "calc_bug2.jpg" to see this) Sadly, clicking OK simply entered the text "The link could not be updated." in cell A1. So close, yet so far away...
*** Issue 7117 has been marked as a duplicate of this issue. ***
Hi, just my own opinion - not being able to import "plain text" CSV in an already existing spreadsheet is a very bad, bad idea. I dont know if you really use calc, but I do ... I use it to visualize data generated by my simulation system. This data is CSV. I need the data inserted into predefined spreadsheets for further calculations and examination. Always creating a new spreadsheet for import and copying data works, but is real pain. So PLEASE allow CSV import into existing spreadsheets as external data in future releases !
Confirming, this functionality is one of the more commonly used when dealing with data exchange in MS Office (I do courses and consulting on Ms Office, I've seen numerous applications for this simple tool). Opening a CSV as a separate document isn't used so often as incorporating it to an existing sheet.
Hi All, to point it out, just use Insert Sheet from the menu bar and select from file. This should solve your problem. Best regards Frank
*** Issue 8727 has been marked as a duplicate of this issue. ***
*** Issue 11317 has been marked as a duplicate of this issue. ***
Is this issue closed? I regard this issue as a suggestion to enhance the software and not as a request for support that should be asked for in the mailinglists. My concern is that many users get space delimited spreadsheet files with the extension PRN, and want to open it as MS Excel does. If you try to File-Open it in Calc, the Writer takes over and opens it as a simple text document. If you do as suggested to another person and try to open the files in OOo Calc just using the File-Open dialog and choosing Text CSV as filter, this do not work either due to the fact that then only CSV- files will be visible. The Insert-External Data method with CSV- filter seems to work until the user wants to click the final OK. The Insert-Insert Sheet-From file with the Text CSV-filter works but it's quite cumbersome. It seems to me the only reasonable fix is to change the type extension for all yours file from PRN to CSV, although CSV is only one of the many delimited file types. For the average user this would be very unfamiliar and inconvenient. In windows it's default the user can't see the file extension. Why not change the way files are opened? If the user in Calc tries to File-Open an "unknown" file, the program should at least give some import options instead of open it in Writer.
*** Issue 11730 has been marked as a duplicate of this issue. ***
*** Issue 12115 has been marked as a duplicate of this issue. ***
To the feature guys: Judging from the number of dupes and votes for this bug, and voices of users brave enough to express their opinion, this functionality is vital. You probably have no slightest idea about what your program is used for, what are typical use scenarios. Please, listen to the users. Don't ignore what the users say. Apple did that with their calc product, Improv, while Microsoft carried out studies to find typical use cases. Where's Improv now, and where's Excel? Those who do not learn from history are doomed to repeat it. Here's an interesting exeprt for you from Joel Spolsky's book, User Interface Design for Programmers <http://www.joelonsoftware.com/uibook/chapters/fog0000000065.html>: "In the days of Excel 1.0 through 4.0, most people at Microsoft thought that the most common user activity was doing financial what-if scenarios, where you do things like change the inflation rate and see how this affects your profitability. When we were designing Excel 5.0, the first major release to use serious activity-based planning, we only had to watch about five customers using the product before we realized that an enormous number of people just use Excel to keep lists. They are not entering any formulas or doing any calculation at all! We hadn't even considered this before. Keeping lists turned out to be far more popular than any other activity with Excel. And this led us to invent a whole slew of features that make it easier to keep lists: easier sorting, automatic data entry, the AutoFilter feature which helps you see a slice of your list, and multi-user features which let several people work on the same list at the same time while Excel automatically reconciles everything. While Excel 5 was being designed, Lotus had shipped a "new paradigm" spreadsheet called Improv. According to the press releases, Improv was a whole new generation of spreadsheet, which was going to blow away everything that existed before it. For various strange reasons, Improv was first available on the NeXT, which certainly didn't help its sales, but a lot of smart people believed that Improv would be to NeXT as VisiCalc was to the Apple II: it would be the killer app that made people go out and buy all new hardware just to run one program. Of course, Improv is now a footnote in history. Search for it on the web, and the only links you'll find are from very over-organized storeroom managers who have, for some reason, made a web site with an inventory of all the stuff they have collecting dust. Why? Because in Improv, it was almost impossible to just make lists. The Improv designers thought that people were using spreadsheets to create complicated multi-dimensional financial models. Turns out, if they asked people, they would discover that making lists was so much more common than multi-dimensional financial models, and in Improv, making lists was a downright chore, if not impossible."
removing me from CC List Frank
*** Issue 12333 has been marked as a duplicate of this issue. ***
*** Issue 12493 has been marked as a duplicate of this issue. ***
*** Issue 13569 has been marked as a duplicate of this issue. ***
The issue which I originally raised (import from an HTML file) has been resolved in the OO 1.1 Beta. I am using the Windows distribution on Win2000. If everyone on copy with CSV needs could test against the beta, perhaps this issue can be closed. Richly deserved Thanks are due to all the developers involved in this project...
*** Issue 9448 has been marked as a duplicate of this issue. ***
Since this problem comes up again and again, I think the best would be to show a "Load All" entry in the list box of the External Data dialog, whenever there are no specific data ranges in the file.
Hi, hope this is'nt beating the subject to death I have two files with extension of .csv; one is comma seperated, the other tab seperated. The first file contains rows of equal number of columns, the second file contains 4 "header" rows before the tab seperated data rows appear. From within Calc the first file can be opened and the text import dialog is shown i.e. works as anticipated. The second file however is opened as a document - aaargh. vi the file and remove the header lines and "voila" it is imported as expected. (it does'nt make a difference if they are renamed to .txt) Interestingly, (even though I know from the above comments that this is not actually supported) if you attempt to insert the "bad" file as external data Ooo displays the file in the text import dialog - which is what I want. Then, on the same theme, I have tried to connect to a web server to retrieve as external data, an html page that will be created by the server i.e the html document does not exist as a file. I get requested for the proxy user name and password - (does this indicate that Ooo is attempting to connect to the web server), the dialog to select the html tables is shown and can be selected and the ok button is not "greyed" out. However Calc shows "The link could not be updated". If though the external data source is referenced through its file system path ie as a static html page the tables are retrieved. Is it not intended to be able to retrieve a page from a url that references a web server? Ta, and thanks for a great product. Ian Stuart
I've tested with OpenOffice 1.1Beta. The problem is _not_ solved for CSV and other text-based files. To sum things up to this point: The user is able to do the following: 1) Select Insert/External Data, 2) Click "..." 3) Select a CSV or other text-based file 4) Setup text import options 5) Click OK in the "Text Import" dialog But is unable to do the folliwing: 6) Load the data by clicking "OK" in the "External Data" dialog , because there is no such thing as data ranges in text files and the "insert external data" dialog is unable to just load all data as MS Excel does. Daniel Rentz suggested a solution - a "Load All" button that loads all available tables/ranges and in case they don't exist, all the raw tabular data. In my opinion such a button isn't needed (clicking "OK" should load all data if no tables/ranges are selected from the list IMO), but even that would be a satisfactory solution. There's a workaround Sascha Ballach suggested - one can select "Insert/Sheet", then "From File". This workaround works, but only if the user wants to put data on a separate sheet, otherwise he/she needs to Cut/Paste the data and Delete the temporary Sheet. Besides, this is highly counter-intuitive and a "dirty hack", especially for users migrating from MS Office who usually import external data directly onto selected sheet. It's almost impossible for the ordinary user to find out this workaround when he/she needs one-time text import. The bug was closed multiple times because interface design people don't think that external data dialog should be used in this way (to import text files where there are no ranges defined). I work as a trainer and have performed numerous MS Excel courses and consultations. From my experience I can tell that importing text data directly into specified cells on the current Sheet is actually the _most_ common scenario when dealing with data import in MS Excel. This bug may have a difficult solution for technical reasons (like rearchitecture of external data support in Calc), but then the bug should have its target set to "Future", instead of being closed forever. Please, OpenOffice team, take into account the number of duplicate bugs, number of votes, the way this feature is implemented in MS Excel, and our (the users) voices, and reconsider your opinion on throwing this bug away. The issue is real.
*** Issue 15441 has been marked as a duplicate of this issue. ***
*** Issue 18223 has been marked as a duplicate of this issue. ***
*** Issue 18259 has been marked as a duplicate of this issue. ***
*** Issue 18404 has been marked as a duplicate of this issue. ***
*** Issue 19176 has been marked as a duplicate of this issue. ***
Re-targeted and re-assigned to Bettina.
*** Issue 19955 has been marked as a duplicate of this issue. ***
Hi everybody! I also do work that involves entering columnar data (space-separated) into a spreadheet using the "fixed column width" option. Insert / External Data / ... / Text CSV brings up the same dialog with Insert / Sheet and you can perform the same selections on the text (like Insert Sheet). It is therefore strange (to say the least) that this selection path won't let you push the final "OK" button and enter the data starting at the cell you are pointing at. I had _accidentaly_ stumbled into the "Insert Sheet" workaround in the past (but I keep forgetting about it every time I need it...). Please add my voice (...err... my text box) to those wanting to use Insert External Data (rather than insert sheet). Nevertheless, the job gets done!
*** Issue 21690 has been marked as a duplicate of this issue. ***
*** Issue 21878 has been marked as a duplicate of this issue. ***
Here comes a suggestion for a solution in OO.o 2.0: 1. Instead of Insert / Sheet... there will be the two entries Insert / New Sheet(s) and Insert / Sheet(s) from file At selection of the entry 'New Sheet(s)' the upper part of the current dialog 'Insert Sheet' will appear (that dialog will be splitted into two dialogs) and allow inserting sheet(s) at the concerning position. At selection of the entry 'Sheet(s) from file' the lower part of the 'Insert Sheet' dialog comes up allowing inserting sheet(s) from file, File type 'Spreadsheet' defaulted. 2. The menu entry Insert / External Data... will be renamed to ' Insert / Link to External Data' for a more clear differentiation. Hi Falko, please take over this issue for writing the specification. Thank you.
FT: Accepted and taken over. Note: since I'm very busy with writing specs for 2.0 until end of 2003 I will touch this issue earliest beginning 2004.
Modified title
*** Issue 19749 has been marked as a duplicate of this issue. ***
*** Issue 24181 has been marked as a duplicate of this issue. ***
*** Issue 24334 has been marked as a duplicate of this issue. ***
*** Issue 24465 has been marked as a duplicate of this issue. ***
*** Issue 25345 has been marked as a duplicate of this issue. ***
*** Issue 26310 has been marked as a duplicate of this issue. ***
*** Issue 26753 has been marked as a duplicate of this issue. ***
*** Issue 28472 has been marked as a duplicate of this issue. ***
*** Issue 28540 has been marked as a duplicate of this issue. ***
I have read all of the comments on this issue and am still unclear on where it stands. I feel that this functionality, the ability to import data in the form of a CSV text file into a spreadsheet, as EXTREMELY important, so much so that I feel strongly that this issue should be filed as a bug and not an enhancement. I use spreadsheets very rarely, but pretty much every time I've ever had to use one has been to import external data from a CSV text file. Is there currently absolutely no way to do this? If so, the ability to do so is so hidden that it is, for all intents and purposes, not there. Furthermore, and the truly odd thing in my opinion, is that the function seemed to be working properly before the failure. When I was asked to specify the external data document to insert, The dialogue box presented gave me a preivew of my data in the spreadsheet format, with each data field in a seperate cell, etc. This gives the impression that the function is actually working, in that it will actually import the external data. I don't understand how, once getting this far, it is much more difficult of an issue to actually insert the data into the cells. To reiterate, the functionality as presented in Spreadsheet seems fine. The user is presented with a dialogue box that seems to be confirming the proper behavior of the function, ie. importing an external data document in CSV format. The function then fails to do so. In my opinion, this is most definitely a BUG and should be classified as such. It is also an extremely important function that should definitely be a part of the program, not as an enhancement, but as a basic feature. jamie.
FT->ES/DR: We (DR and I) decided not to write a full spec (for time reasons) and not to split any dialogues. Here is what we will do in 2.0 to ease the problem(s) stated in this issue: 1.) Add a new menu entry in "Insert"menu: "Sheet from file..." / "Tabelle aus Datei..." This entry will be located right underneath the already existing entry "Sheet..."/"Tabelle..." 2.a) When "Insert - Sheet..." is chosen, nothing changes compared to Calc 1.1.x. and the standard "Inser Sheet" dialogue opens. 2.b) When "Insert - Sheet from File..." is chosen, a "File open" dialogue opens (same as the user would choose "Browse" button from "Insert Sheet" dialogue). Once the user chose a file from it this file will be inserted in the "Insert Sheet - From file" box and the "OK" button of "Insert Sheet" dialogue will turn active. 3.) Rename the current menu entry "Insert - External Data..." to "Insert - Link to External Data..."/"Einfügen - Verknüpfung zu externen Daten..." FT->EM: Please review new strings and hand this issue over to DR for implementation. FT->DR: Once EM agreed to the string changes/additions please implement. Thx.
The strings are fine. Go ahead with the implementation.
accepted
*** Issue 29795 has been marked as a duplicate of this issue. ***
*** Issue 30030 has been marked as a duplicate of this issue. ***
UI changes summary 1) new menu entry in "Insert" menu: ENGLISH: "Shee~t from file..." GERMAN: "Ta~belle aus Datei..." 2) changed menu entry in "Insert" menu: ENGLISH: "E~xternal data..." -> "Link to E~xternal data..." GERMAN: "E~xterne Daten..." -> "Verknüpfung zu e~xternen Daten..." 3) changed menu entry in "Insert" menu: GERMAN: "D~iagramm" -> "~Diagramm" ('D' was unused and is better visible then lowercase 'i') 4) New slot definition (for slot .uno:InsertSheetFromFile) ENGLISH: "Tabelle aus Datei einfügen" GERMAN: "Insert Sheet from File"
fixed in src680/dr20
*** Issue 31248 has been marked as a duplicate of this issue. ***
reopened
back to QA
fixed, now in CWS dr21
checked feature in CWS dr21 -> works as defined by Falko changes in automated calc tests (Update and Level1) are already provided in OOo CVS (deactivated until the integration into the master)
*** Issue 34540 has been marked as a duplicate of this issue. ***
closed because fix integrated in OOo1.9m54
*** Issue 35480 has been marked as a duplicate of this issue. ***
*** Issue 37151 has been marked as a duplicate of this issue. ***
*** Issue 39185 has been marked as a duplicate of this issue. ***
*** Issue 48961 has been marked as a duplicate of this issue. ***
*** Issue 59811 has been marked as a duplicate of this issue. ***
*** Issue 69139 has been marked as a duplicate of this issue. ***
*** Issue 69199 has been marked as a duplicate of this issue. ***
*** Issue 91607 has been marked as a duplicate of this issue. ***
To answer comment in issue #91607 - what's wrong with doing it via "Insert sheet from file..."? Everything is wrong with it: - it is illogical - no one would ever think of using it to connect to a clearly external resource, that is refreshed periodically just made available as a .csv not .html, - there is even no mention in the file dialog that you could even type URL-s there! It wouldn't have occurred to me, and I'm in IT since 1989. This is a crude workaround at best. I don't know why this resistance to making OO's UI smoother and working better by allowing all kinds of external resources to be properly inserted from their proper menu entry. Why don't make it easier for users?
This issue should be re-opened!
Oh, and BTW - this method (insert the remote .csv through "Insert->Sheet from file..." does not allow automatic refreshing of the data. Inserted file is downloaded and inserted once, no link appears in Edit->Links, no way to refresh it other than manually.
*** Issue 101154 has been marked as a duplicate of this issue. ***