Bug 67673 - Pasting data into Calc - Text import dialog ignores column type
Summary: Pasting data into Calc - Text import dialog ignores column type
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: x86-64 (AMD64) Linux (All)
: medium minor
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2013-08-02 14:57 UTC by Marcel Samek
Modified: 2016-09-21 02:30 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marcel Samek 2013-08-02 14:57:23 UTC
I copy data consisting of two columns in a text editor and paste it into calc. The two columns are separated by spaces and the first column is text and the second is numeric. Like this:

DDDDD 3
DDDDU 4
DDDDd 13
DDDDu 4
DDDUD 27
DDDUU 10
DDDUd 36
DDDUu 16
DDDdD 26
DDDdU 36

When I perform the paste into Calc, it pops up the Text Import dialog. The dialog allows me to select a column and specify a data type.

I select the first column and specify a type of Text.

I perform the import and everything goes fine.

When the I examine the imported data, the first column cells are all "number" and not text as I had specified.

Since I had specified that the first column is text in the import dialog, I would expect that all the cells in the first column would have a text type.

              
Operating System: Windows (other)
Version: 4.1.0.4 release
Comment 1 Cor Nouws 2013-08-02 19:21:01 UTC
Hi Marcel,

Thanks for writing your report. However...

(In reply to comment #0)

> When the I examine the imported data, the first column cells are all
> "number" and not text as I had specified.
> 
> Since I had specified that the first column is text in the import dialog, I
> would expect that all the cells in the first column would have a text type.

The formatting is Number/Default.
It has been like that for ages: at least in LibreOffice 3.3.0 it already is like that. So I guess it's simply intended behaviour. Because also when you just type 'apple' in an empty default cell, it is text, but the formatting still is Number/Default.

Best regards,
Cor
Comment 2 Marcel Samek 2013-08-02 20:01:50 UTC
Hi Cor,

Thanks for the quick response, but your answer does not make sense to me. Yes, you are correct, the formatting on the pasted data is Number/Default - if that was simply the default behavior I would have no problem with that.

However, when I did the paste, Calc popped up a panel that specifically asked me what type the columns were and I specified that the column was text. I did not ask for this panel - Calc chose to pop it up when I did the paste.

So, if Calc is going to pop up a panel that asks me what type the column is, and then chooses to ignore my input, that looks, smells, and tastes like a bug.

Either Calc should not have the option to specify the column format on the import panel, or it should not ignore it. Either way, something needs to change.

Marcel


(In reply to comment #1)
> Hi Marcel,
> 
> Thanks for writing your report. However...
> 
> (In reply to comment #0)
> 
> > When the I examine the imported data, the first column cells are all
> > "number" and not text as I had specified.
> > 
> > Since I had specified that the first column is text in the import dialog, I
> > would expect that all the cells in the first column would have a text type.
> 
> The formatting is Number/Default.
> It has been like that for ages: at least in LibreOffice 3.3.0 it already is
> like that. So I guess it's simply intended behaviour. Because also when you
> just type 'apple' in an empty default cell, it is text, but the formatting
> still is Number/Default.
> 
> Best regards,
> Cor
Comment 3 Cor Nouws 2013-08-02 20:11:54 UTC
Hi Marcel,

- pls respond in the Web-interface. to prevent unnecessary quoting -

Maybe there is some confusion on 'Number/Default' which is the 'setting' that allows to insert numbers, dates, text etc. and that it then is interpreted.

If you really insist on changing something here, then pls go to some die hard spreadsheet experts and discuss it with them ;)
Cheers,
Cor
Comment 4 Marcel Samek 2013-08-02 20:42:34 UTC
Hi Cor,

I am not convinced that we are talking about the same thing. So let me as you this:

1) Copy the following lines and paste them into a text editor. Then copy the same lines from the text editor. (If you copy/paste directly from a web page it will behave differently, but that is a different issue).

DDDDD 3
DDDDU 4
DDDDd 13
DDDDu 4
DDDUD 27
DDDUU 10
DDDUd 36
DDDUu 16
DDDdD 26
DDDdU 36

2) Paste into a new worksheet in Calc.

Calc immediately pops up a modal panel labeled Text Import. 

3) On that panel select "Space" as a separator. Now in the field display you will see two columns. The first containing text strings and the second containing numbers.

4) Click on the header for the first column. The column type pulldown will say "Standard"

5) Select "Text" from the pulldown.

6) Hit OK to import the data into the spreadsheet.



So, my question is this: What is the expected behavior of my having selected "Text" from the pulldown? I have been laboring under the assumption that making that choice tells calc that those are "Text" cells. If that is not the case, then I do not understand what the purpose of that "column type" pulldown is.
Comment 5 Cor Nouws 2013-08-02 20:50:59 UTC
(In reply to comment #4)

> I am not convinced that we are talking about the same thing. So let me as
> you this:

You initial explanation was very clear. I did exactly that :)
Comment 6 Marcel Samek 2013-08-02 20:53:27 UTC
(In reply to comment #5)
> (In reply to comment #4)
> 
> > I am not convinced that we are talking about the same thing. So let me as
> > you this:
> 
> You initial explanation was very clear. I did exactly that :)

So, could you please do me a favor and reply to the question at the end of my last comment: What is the expected behavior of changing the "column type" on the panel that pops up? What does that option do?

Thanks,

Marcel
Comment 7 Cor Nouws 2013-08-02 20:54:58 UTC
(In reply to comment #6)

> So, could you please do me a favor and reply to the question at the end of
> my last comment: What is the expected behavior of changing the "column type"
> on the panel that pops up? What does that option do?


Mark the second column as text, and see what it does...
Comment 8 Marcel Samek 2013-08-02 20:59:23 UTC
> 
> Mark the second column as text, and see what it does...

It does exactly what it should do. It changes that column to text values and if, after the import, I right click on a cell in the second column and say Properties, it tells me its a text cell.

However, if I do the same thing on the 1st column, it does not change the cells to  text cells, it keeps them as number cells.

Try it. Mark both columns as text and after the import check the cell properties. If have tried it numerous times and it does not work for that first column.
Comment 9 Cor Nouws 2013-08-02 21:12:38 UTC
Because that is needed to make text from a number.

Pls see my comment #3.
Comment 10 Marcel Samek 2013-08-02 21:25:02 UTC
(In reply to comment #9)
> Because that is needed to make text from a number.
> 
> Pls see my comment #3.

However, in one case it is setting the format of the cell and in the other it is not. Why is Calc setting the format of the cell for column 2 and not for column 1?

That is inconsistent. For one column Calc does what the user has asked it to do, but for another Calc silently ignores the users input?

If selecting "Text" for column sets the format for the cells in column2 to text, then it should do the same for column 1.

If selecting Text is going to continue being ignored on Column 1, then it should be removed from the pulldown for that column.
Comment 11 Marcel Samek 2013-08-02 22:05:57 UTC
I have experimented a bit more with mixed data in the columns and now I understand what is happening:

When you select a column and mark it as "Text", Calc goes through and identifies all the cells that have numberic data in them and changes the format to "Text", but it leaves all the cells that have non-numeric data alone and leaves them as "Number".

The result is that the spreadsheet now has a column with mixed data in it.

The reason that this behavior is annoying and why I consider it a bug is this:

When I imported the text column, in the import column I marked it as text and thought I was done. It was only later than I realized that sorts on that column were not sorting that column properly. They were in a bizzare order and I started digging into the problem.

The way the current functionality is set up means that the "column type" setting in the import dialog is essentially useless because you are not guaranteed that it will actually set the column type. After the data has been imported, you have to go an set the format of the cells again.

If you mistakenly believe that the column type setting in the import panel actually sets the column type, then none of your data sorting will work properly for text data.

Best regards,

Marcel
Comment 12 Cor Nouws 2013-08-03 17:12:47 UTC
Hi Marcel,

I can understand the feeling because of this behaviour.
There are more topics with the default cell and how it works. If you are interested: take a look at the discussion round automatic date/number recognition and the work that our hero Eike did recently, to make that less intrusive and more flexible at the same time.

For the rest: see my comment 3 ;)

Regards,
Cor
Comment 13 Urmas 2013-08-10 10:48:04 UTC
Needless to say, Excel does it right.
Reopening.
Comment 14 Cor Nouws 2014-11-04 21:05:27 UTC
Maybe one of the commenters here could be so kind to test with the new possibilities in Tools > Options > Calc > Formula .. Detailed calculation settings.. Custom Convert Text to Number?
(In any case in 4.3 and 4.4)
(I think it does not change the behaviour, but one never knows..)
Comment 15 QA Administrators 2015-05-06 14:15:17 UTC Comment hidden (obsolete)
Comment 16 Ed 2015-05-06 19:09:13 UTC
Okay, I'm at a bit of a loss as to why I've been asked for comments. I use Linux, not Windows and I'm not using the same version Office but I can say in ver 4.2.8.2, the behaviour is the same at reported by the submitter. As far as I am concerned, the bug is confirmed and it make no sense to me, to offer the option to to set a column formatting and then ignore the user's request simply because that column doesn't contain non-text data. If the user specifies a specific format, Calc should not examine the data but simply comply.
Comment 17 tommy27 2015-05-24 16:02:02 UTC
status NEW because of confirmation in previous comment.
reverted version to earliest where the bug was reported (4.1.0)

please tell if problem persists in latest 4.3.7 and 4.4.3 releases
Comment 18 QA Administrators 2016-09-20 09:46:19 UTC Comment hidden (obsolete)
Comment 19 Ed 2016-09-21 02:30:15 UTC
This is not a regression issue. Behaviour is the same in ver 3.3.0, never-the-less, I might be expecting something different from TEXT than from STANDARD. Maybe it doesn't do anything because the first column is already TEXT.