Bug 101696 - EDITING: Auto-conversion for dates needs disable option for scientific work
Summary: EDITING: Auto-conversion for dates needs disable option for scientific work
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL: http://news.softpedia.com/news/one-in...
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-08-24 10:36 UTC by Simon Phipps
Modified: 2016-10-22 15:05 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Simon Phipps 2016-08-24 10:36:28 UTC
Per this article:
http://news.softpedia.com/news/one-in-five-scientific-papers-on-genes-contains-errors-because-of-excel-507582.shtml

a mechanism to disable auto-conversion of dates on a per-sheet basis is needed by scientific users of Calc.

[Note: Entering this bug in response to Twitter report https://twitter.com/lcomparat/status/768338320187326464 and not my own usage]
Comment 1 V Stuart Foote 2016-08-24 13:36:46 UTC
This already exists!

"Scientist" users need simply set a cell format as "Text" (Format -> Cells -> Numbers tab: Text) for the target Column/Row or Sheet. 

No conversion/corruption of the data being pasted will occur.

Also when opening to import TSV or CSV, the default column import format is "Standard"--a standard import assigns all fields as "Number" with "General" format--and again no conversion to a "Date" format will occur.

And after "Standard" import, the cell formats for the whole sheet can be set as "Text" and the cell type protected.

Alternatively, the import cell format for the columns of the TSV or CSV can be forced to "Text" (rather than "Standard") from the import dialog if preferred--a simple drop down list selection while completing the import dialog.

Suppose providing a user setting forcing the import dialog to "Text" format might be a valid UI setting that could help issue of carelessness of "Scientists" as described. 

And possibly a similar sheet setting for paste actions, i.e. to "paste into cells as 'Text' value" only should be possible.
Comment 2 m_a_riosv 2016-08-24 23:27:01 UTC
Sorry I can't resist to be politically incorrect.

Where are the 'scientific users'?

What is the scientific method used by people who don't know how to use their tools. If you want to use i.e. an atomic microscope, you need to study how it works and manage their software, what are their characteristics, accurateness on the results, etc. And excel/calc as any other software is a tool for them, so they should have learned how it works, and what are their limitations, set up the adequate data validations, etc, to use it properly.

Basic, first to use a tool, be sure if it is the right tool.

To manage a high quantity of data IMHO databases are better and safer tools than spreadsheets. But wait, it's a bit more difficult to begin with it without study.

Self sufficiency, explain why people thinks can do the job that must be done by people with the proper formation to manage data.

Worse, how can we be sure they produce the right raw data?, some 'scientists' profession where the results are not easily validatable, I think can fall, perhaps frequently that we can guess, in this kind of issues, but as always their are not the responsible but the 'postal service'.

Of course any software as everything has a lot for improvement, but that can't be a justification for our own mistakes.

Be an 'scientific'/'expert' on an area, don't save to be an ignorant in others.

So if you like the things well done, do what you know to do fine, or if don't want to learn how to do it fine, ask for help to who knows to do it fine.
Comment 3 Jean-Baptiste Faure 2016-08-25 09:20:23 UTC
(In reply to V Stuart Foote from comment #1)
> This already exists!
> 
> "Scientist" users need simply set a cell format as "Text" (Format -> Cells
> -> Numbers tab: Text) for the target Column/Row or Sheet. 

It does not work if you copy-paste something like 2310009E13 in a cell.
The easy mean to avoid any unwanted conversion is to paste the same string with a quote: '2310009E13

The unwanted conversion of strings like SEPT2 does not occur in Calc.

I propose to close this bug report as NotABug (or NotOurBug if you agree with me that the bug is between the chair and the keyboard ;-) )

Best regards. JBF
Comment 4 V Stuart Foote 2016-08-25 13:44:11 UTC
(In reply to Jean-Baptiste Faure from comment #3)
> It does not work if you copy-paste something like 2310009E13 in a cell.
> The easy mean to avoid any unwanted conversion is to paste the same string
> with a quote: '2310009E13
> 

Actually was not aware of that conversion of HTML source exponential notation being done, but if you Paste Special -> "Unformatted text" rather than simple Paste of HTML format--it goes into a cell as text. And if the exponential number source was not from an HMTL mark up--it is rendered as simple text when cells are formatted as text.

> I propose to close this bug report as NotABug (or NotOurBug if you agree
> with me that the bug is between the chair and the keyboard ;-) )
> 

Well it is an enhancement--but before closing out of hand, think we should see what some of the devs think about UI control forcing a sheet, column or row to Text-- Eike, Kohei, *?
Comment 5 Eike Rathke 2016-08-25 20:02:32 UTC
First, LibreOffice Calc does *not* convert MARCH1 or SEPT2 to date, contrary to what is said in the original report http://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7

It (and OpenOffice.org and ...) *did* convert MARCH1 to date (but not SEPT2, it could had happened with SEP2 though), but that was fixed for bug 41166 already with release 4.2.5

So whatever version they used to test things for a report published on 2016-08-23, it was too old, and the statement cited as "To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc." simply is wrong in this context.

Second, of course 2310009E13 is a number, but not 2.31E+13 as given in the report but 2.310009E+019 instead. Do not trust a scientific publication ;-)

This number recognition is *expected* from a spreadsheet application, yes even when pasting some copied data. If all data is to be treated as literal text then doing one of these helps, as already mentioned in earlier comments:

* applying the Text number format to all target cells before pasting
* pasting as unformatted text instead of HTML (or whatever the source format
  was) and in the text import dialog select Text as column type
* using a common proper data format for tabular text exchange: CSV or TSV, and
  again select columns to be Text in the import dialog

I don't think a "treat all pasted input as text" configuration option would help anything because those scientists wouldn't know that either.

The only thing that could help is detecting a conversion to number or date in between runs of mere text strings within one column and then nag the user about it. But such nag boxes quickly get annoying until ignored.
Comment 6 V Stuart Foote 2016-08-25 21:04:09 UTC
OK then, thanks Eike...

WONTFIX
Comment 7 Eike Rathke 2016-08-26 10:19:34 UTC
Not sure about the wontfix. Those little nag boxes to point out irregularities in cell content can be helpful. After an import or a paste a different/additional approach might be needed though to actually make the user review the data. A nag box 20 screens further down likely gets unnoticed.