Bug 103000 - Text paste into spreadsheet clears cell formatting if in Text Import dialog Column type set to Date (YMD)
Summary: Text paste into spreadsheet clears cell formatting if in Text Import dialog C...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: All All
: low minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Paste
  Show dependency treegraph
 
Reported: 2016-10-05 06:53 UTC by plasticassius
Modified: 2019-02-24 06:42 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
example (210 bytes, text/plain)
2016-10-23 07:18 UTC, plasticassius
Details
example (85 bytes, text/plain)
2016-10-23 07:19 UTC, plasticassius
Details

Note You need to log in before you can comment on or make changes to this bug.
Description plasticassius 2016-10-05 06:53:58 UTC
User-Agent:       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Build Identifier: Version: 5.1.4.2 Build ID: 1:5.1.4-0ubuntu1 CPU Threads: 4; OS Version: Linux 4.4; UI Render: default;  Locale: en-AU (en_US.UTF-8)

This didn't happen in V4.

Reproducible: Always

Steps to Reproduce:
1.Copy text table with C-C from another app.
2.Paste into spreadsheet, ok the import box.
3.Cells pasted over no longer have their formatting (C-1).


Expected Results:  
Cells should retain formatting applied to them with C-1 (example: particular date format). It is irritating if you use paste a lot.




Reset User Profile?No
Comment 1 Jean-Baptiste Faure 2016-10-06 19:57:59 UTC
Please could you be more specific? 
- what are C-C (ctrl+C ?) and C-1 ?
- what do you mean by "another app" ? Do you mean another LO module (like Writer or Impress) or another office software ?
- I do not understand which formatting is lost in the past operation: is it the formatting of the source text (the text that is copied and pasted) or is it the formatting of the cell set before the paste operation.

- did you try paste special?

Best regards. JBF
Comment 2 plasticassius 2016-10-07 09:03:47 UTC
C-C = ctrl+C (copy), C-1 = ctrl+1 (format cells)

another app = leafpad, epsilon, xsel (ctrl+C used to copy pain text without formatting) not another LO module

ctrl+1 was used to format a whole column of cells to a particular date format (ie: Date MM-DD HH:MM)

ctrl+C was used to copy unformatted text from another app

ctrl+V was used to Text Import to Calc

format changes to Number General in pasted cells, version 4 preserved MM-DD HH:MM format

ctrl+sht+V runs Paste Special which runs Text Import to Calc and produces the same result

test produces same result in Version: 5.2.2.2
Build ID: 1:5.2.2-0ubuntu1~xenial0
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
Locale: en-AU (en_US.UTF-8); Calc: group

it is the formatting of the cell set before the paste operation that is lost.

Thanks for your interest, Peter
Comment 3 Jean-Baptiste Faure 2016-10-22 21:33:20 UTC
Thank you for your answer. From your comment #2 it seems that the issue depends on the text to be copy-pasted. Could you attach an example of such a text?

Best regards. JBF
Comment 4 plasticassius 2016-10-23 07:18:32 UTC
Created attachment 128183 [details]
example

tab seperated, date column modifies format of cell it is pasted into
Comment 5 plasticassius 2016-10-23 07:19:27 UTC
Created attachment 128184 [details]
example

date column modifies format of cell it is pasted into
Comment 6 Xisco Faulí 2016-10-23 15:36:30 UTC
In my case, if I open p2.txt in gedit or leafpad and then I copy the content to Calc, the cells look like ###. However, if a press Ctrl+1 the format code is DD/MM/YY HH:MM

Version: 5.3.0.0.alpha0+
Build ID: 8974b0fafb18f9dd3f2c0e175a3255b80e4c249e
CPU Threads: 4; OS Version: Linux 4.2; UI Render: default; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group
Comment 7 plasticassius 2016-10-24 07:13:01 UTC
Yes, ### means that the cell size is too small to display the contents. The point is that the formatting is not preserved during the paste. The paste set yours to DD/MM/YY HH:MM. If you had set it to something else before the paste (ie: YYYY-MM-DD HH:MM), you would see that it is not the same after. Pasting plain text over cells that already had been formatted would preserve that formatting in previous versions, now it is reset.

The format you are seeing (DD/MM/YY HH:MM) is likely calc's best guess at what a date should look like. If you check the cell format after the paste, it likely shows Number General rather than Date at all. If you had set the cell's format to Date, that formatting would have been lost.
Comment 8 Jean-Baptiste Faure 2016-10-25 07:02:57 UTC
Not reproducible for me with both master and 5.2.4.0+ version built at home under Ubuntu 16.04 x86-64.

Please, could you give a try to current LO 5.2 ?

Best regards. JBF
Comment 9 Buovjaga 2016-10-26 16:25:04 UTC Comment hidden (obsolete)
Comment 10 plasticassius 2016-10-27 08:08:21 UTC
I can confirm problem exists in

Version: 5.2.3.1
Build ID: 01ec8f357e651ca9656837b783cf7e6a32ee4d92
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
Locale: en-AU (en_US.UTF-8); Calc: group

Procedure:

select column A
set format to Date YYYY-MM-DD HH:MM
open p2.txt and copy contents
select cell A3
paste & set Column type to Date (YMD)
cell A3 format is now Date DD/MM/YY HH:MM

This is different than in LO 4 since the format of the cells
have now changed.
Comment 11 Buovjaga 2016-11-01 10:42:16 UTC
(In reply to plasticassius from comment #10)
> I can confirm problem exists in
> 
> Version: 5.2.3.1
> Build ID: 01ec8f357e651ca9656837b783cf7e6a32ee4d92
> CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
> Locale: en-AU (en_US.UTF-8); Calc: group
> 
> Procedure:
> 
> select column A
> set format to Date YYYY-MM-DD HH:MM
> open p2.txt and copy contents
> select cell A3
> paste & set Column type to Date (YMD)
> cell A3 format is now Date DD/MM/YY HH:MM
> 
> This is different than in LO 4 since the format of the cells
> have now changed.

I repro. Due to locale (Finnish), I get a different result DD.MM.YY HH:MM. However, I get this result in 4.4, 4.3, 4.1. What's more, I get this result even in 3.5.

Are you sure this is different from LibO 4? Please recheck: https://wiki.documentfoundation.org/Installing_in_parallel

Win 7 Pro 64-bit Version: 5.3.0.0.alpha1+
Build ID: 4b4abb73fcd7f2802e73102b3e7c30face8d309c
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; Layout Engine: old; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-10-31_02:54:50
Locale: fi-FI (fi_FI); Calc: group

LibreOffice 3.5.0rc3 
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Comment 12 plasticassius 2016-11-02 09:29:59 UTC
Yes I agree, it is the same for me also on version

Version: 4.4.7.2
Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600
Locale: en_US.UTF-8

Unfortunately, I can't very well replicate my previous setup with LO 4 since I had Ubuntu 14.04 at the time. However, it did indeed preserve the formatting in cells when I pasted unformatted text to it. Now I don't know how it could do that, but it worked that way for me for so long that I got used to it and assumed that must be the correct way for it to work.
Comment 13 QA Administrators 2018-07-16 02:42:12 UTC Comment hidden (obsolete)
Comment 14 Timur 2018-11-20 12:00:01 UTC
I failed to reproduce this in Windows on reported LO 5.1 and master LO 6.3+.
Maybe steps from Comment 10 are not precise enough. 
I get Text import dialog on paste, not clear if relevant. 
Xisco and Buovjaga, please test again.
Comment 15 Buovjaga 2018-11-20 21:16:10 UTC
(In reply to Timur from comment #14)
> I failed to reproduce this in Windows on reported LO 5.1 and master LO 6.3+.
> Maybe steps from Comment 10 are not precise enough. 
> I get Text import dialog on paste, not clear if relevant. 
> Xisco and Buovjaga, please test again.

Still repro with 6.2 beta1 (Win).

In the Text import dialog, you have to select the header that says "Standard" and change the column type from the drop down to Date (YMD)
Comment 16 Timur 2018-11-21 10:50:18 UTC
Yes, that's what I didn't do. 
If I skip "paste & set Column type to Date (YMD)" and just simply press OK o Text Import, all is fine and format is Date YYYY-MM-DD HH:MM. 
But than title is wrong, simple text paste is OK, but the function "Column type" is unclear. 
I don't see this as a bug because steps involve mutually unnecessary steps of setting format to Date YYYY-MM-DD HH:MM and also setting Column type to Date (YMD).

I'd rather turn this bug to Ducumentation to have explanation how this Column type set works. And after that we may open a bug for correct behavior. 
I see there was help page on "Text Import" in offline help, but I can't find it in online help. Help button on Text import doesn't work for me. 
I could find only https://help.libreoffice.org/6.2/en-US/text/scalc/guide/csv_files.html which doesn't explain options.
Comment 17 Eike Rathke 2018-11-21 13:38:50 UTC
I'd rather say that if a column was already formatted to type Date(+Time) like  MM-DD HH:MM  then when pasting a text that can be forced to type Date should not alter the format already applied, as happens with the first attachment in the second column when forced to Date (DMY), but not with the ISO type dates of the second attachement in the first column if it is not forced to Date (YMD). (Provided "Detect special numbers" is checked in both cases)
Comment 18 TBeholder 2019-02-24 06:42:57 UTC
In other words, everything is pasted into cells with its own format, if at all possible — unless you paste it into during cell editing or into formula bar rather than cell.

Seems to be the same, but appears in slightly different circumstances.
I select a fragment of text from one cell and pasting into another via primary buffer (MMB in Linux, support in Options-LibreOffice-View). But this pastes text with its old format, even though it was just a selection, not cell.
Format-Clear Formatting (Ctrl-M) resets to the cell's assigned old format.

IMHO not too bad, just make it controllable, especially in case of Text Import dialog, much like Paste/Special does.

That's most likely what causes another problem (I wanted to submit a bug, but now it looks like it would be a duplicate): there's no way to explicitly set the intended format, and the field's own format is circumvented by pasting, thus there's no way to avoid having pasted text like "00234E67" (hexadecimal value) converted (into 2.34000000000000E+69). Which is bad, because data is borked and Ctrl-M isn't going to fix this.