when importing a CSV file with an array formula, the array formula becomes something between a formula and text. discussed here: http://en.libreofficeforum.org/node/11459 (Thanks to Lupp for his help) sample csv file: "{=MATCH(1;ISERROR(A3:A8);0)}" title 1 2 3 4 "=a3+a6" "=1+a2" now import into calc. On the import dialog screen, I am selecting: separated by: coma, Text delimiter: double quote, from line: 1, character set: unicode (UTF 8 ) this is default language: Default- English (Canada) this is my default, all other check boxes blank. after import I see {=MATCH(1;ISERROR(A3:A8);0)} title 1 2 3 4 5 #VALUE however if you click on cell A1 "The curly brackets vanished in the formula bar and ... [t]he referenced range got emphasised with a coloured frame" (quoting the above mentioned thread, because they put it better than me) This suggests It is recognized as a formula, but the formula text is displayed is displayed, not the expected number 6. if you click in the input line and change the formula (for example, hit the space bar, then the backspace key) then hit ctrl+shift+enter the equation disappears and is replaced by the number 6. I am using LO 4.2.8.2 under Kubuntu 14.04 LTS Lupp confirmed on 5.0.0.5 and 3.6.5.2 on Windows 8
CSV format is "Comma Separated Value", it is designed to contain values not a to contain formula. Why you do not use a .ods file if you need spreadsheet functionality? https://en.wikipedia.org/wiki/Comma-separated_values https://tools.ietf.org/html/rfc4180
(In reply to Jean-Baptiste Faure from comment #1) > CSV format is "Comma Separated Value", it is designed to contain values not > a to contain formula. Why you do not use a .ods file if you need spreadsheet > functionality? > https://en.wikipedia.org/wiki/Comma-separated_values > https://tools.ietf.org/html/rfc4180 I fail to see the relevancy of this question in relation to the bug report. However I am generating the .csv file from another program, and it is much easier to generate a .csv than a .ods. Also, LO will import "regular" (i.e., not array) formulas properly, so somebody much higher on the food chain than me, at least at one point, thought this was desirable.
LibreOffice might be specified not to recognise any formulas imported from a text file as unformatteed text or opening of a csv. But (Check again, please, Jean-Batiste): {=MATCH(1;ISERROR(A3:A8);0)} as given in the report here is handled inconsistently whether pasted special as unformatted text with Ctrl+Shift+V copied from a text file or directly entered from the keyboard or contained in a csv file that was opened with Calc. It is treated as a text with 28 characters in its cell. It is edited as a formula of 26 characters without the curly brackets if edit mode is entered, and this in the formula bar and in the cell as well. The range contained also is highlighted then by a border as if a formula is edited. Leaving edit mode, however will not make it a working formula but simply restore the curly brackets. Please note again: The ordinary formulas imported enclosed in "text delimiters" from csv are recognised as formulas and evaluated as such from the beginning! They are not treated as text! This is inconsistent! I would suggest to change the subject of this bug into "Inconsistent treatment of array formulas if imported, inserted or entered as text using the curly brackets." Fixing the issue might, however, require to accept a pair of curly brackets around a formula as an alternative to the special key-grip Ctrl+Shift+Enter, and at the same time to not allow texts matching the pattern {=SomeThing}. This in the line with other strings starting with = or, alas, with - or + in specific cases.
title changed as suggested
to Jean-Baptiste Faure: I wanted to apologize if my response to your message came across as offensive. It was not intended that way. It was merely surprise. I admit that I am forcing the venerable .csv format to do things it wasn't really meant to do, but it seems to me that many other parts of LibreOffice and it's documentation implicitly acknowledge that it's not that rare.
Created attachment 123749 [details] Sample CSV with SUMPRODUCT() instead brackets. I think not always but in many situations you can involve the formula with the SUMPRODCUT() function, it is an array function so you don't need the brackets. And open the csv with it works fine for me.
rstill: regarding comment 6: I am setting to NEEDINFO. Change back to UNCONFIRMED, if you are not happy with the solution. Change to RESOLVED WORKSFORME, if you are happy.
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping-20161010
Created attachment 127920 [details] The archive mentioned in my Comment#10; Demonstration of the claims
(In reply to Xisco Faulí from comment #8) > Dear Bug Submitter, > > This bug has been in NEEDINFO status with no change for at least > 6 months. ... I am not the original reporter here. Nonetheless I think I understood the problem and can confirm it. A new description might get wordy and not much more clear than it was already tried. Thus I made a demo consisting of 4 files (of which only 3 are relavant. One is an simple ods containing ordinary formulae and an array formula as well. It was saved while the option 'Display' > 'Formulae' was switched on. From this comes the second file to whitch the relevant sheet of the first one was written using 'Save As...'. Now a new (untitled) ods document was created and the mentioned csv was used to insert a sheet there. Later it was save to "bug_tdf93973InconsistencyConfirm_1ReloadedFromCSV.ods" The mentioned files are packed into the attached zip archive. Start opening the last mentioned one and read the few lines of text there. You may open the other files then as needed.
Thanks, Wolfgang. Let's set to NEW.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
The link to late en.libreofficeforum . org in the original post is broken due to the shutdown of that forum (sigh). There is a (not searcheble) archive where you still find the thread under discussion: http://www.imaccanici.org/en.libreofficeforum.org/node/11459
Tested again with V5.4.2. on Win 10: Behaviour still as described in the original report. Emphasizing the inconsistencies: -1a- Non-array formulas are recognized as formulas on import. -1b- Array formulas are not. They are dysfunctional and shown as texts. -1c- 'Numbers' format of the respective cells remains 'General'. -2a- Going edit mode (F2, not the wizard) the mode is "formula-editing": Emphasizing references, no curly brackets. -2b- Escaping the edit: Formula is text again - with curley brackets. Still 'General' numbers format. -2c- The wizard doesn't regognize a formula at all.
Tested again with V6.1.3.1RC. Behaviour unchanged. (Please also read the final paragraph thoroughly.) I already included a suggestion with my demonstrations to solve the issue by creating an alternative way to enter a formula for array-evaluation. See file bug_tdf93973InconsistencyConfirm_1ReloadedFromCSV.ods contained in the .zip attached to Comment #10. This second way was to simply enclose the formula in curly brackets as it is displayed anyway after being recognised. Concerning the present case of importing formulae from a csv this might be accomplished by finding the output range for the top-left occurrence of the formula with a query for the range of its adjacent repetitions. I'm inerested in your comments on the question if filing that suggestion as a feature request (enhancement "bug") might be the better way to get the old issue resolved.
Dear rstill74, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Tested with V 6.3.3.1rc Behavioiur unchanged.
Created attachment 155322 [details] Example with directly entered text looking like array formula I don't know if the original reporter (rstill74) continues to observe this bug, but: I tested with V 6.3.3.1rc and the described behaviour was unchanged. Hoping to get things even more clear I attach a very simple example .ods made with the mentioned version where the text contents looking like a array formulae were directly entered using the keyboard. The effects are the same as with content read from a csv file.
Dear rstill74, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Array formulas can't be created by text import that way. Period. Not a bug. Won't fix.