[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #11 from Jonny Grant  ---
Thank you for your message.

Yes, the solution is to imported (using "Detect special numbers" ON), it's
rather manual, not automatic as users would expect. That's fine, I'm a software
engineer, I am used to changing settings. It's better just leave it on by
default (perhaps behind an advanced) button as that is what users expect.

There is another issue, loading  as English(UK) with "Detect special numbers"
ON, LibreOffice oddly changes the currency from USD to GBP. Around the world we
often open CSV files, they may contain dollars, yuan, euros or pounds. We don't
want LibreOffice to silently change the currency, that's a bug.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #10 from ady  ---
(In reply to Jonny Grant from comment #9)

> sort by the nominal value. Is that another "enhancement" request?

Let's recap.

If we cannot replicate the problem, there is no way to improve the case.

You see it sorted in one way, but I see it sorted as numbers (aka as you
expected).

Miguel was not able to repro the reported behavior either, and he closed the
report as NAB.

I mentioned the possibility of re-opening the report again.

I already suggested to reach the help of other users at
https://ask.libreoffice.org>. Have you?

IDK what else you would expect at this point in this report. As a simple user
as I am, if you present a bug report that I could replicate, I will contribute
in any way I can.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #9 from Jonny Grant  ---
(In reply to m_a_riosv from comment #5)
> They are not importer as numbers, but text. Doesn't matter that the cell
> format is standard.
> 
> I think the situation is solved with one of the next steps, to get column
> values as numbers:
> 
> 1. Select as locale 'English US' in the Text import window.
> or
> 2. Click on the header of column with values and select US English for the
> column type, in the Text import window.
> 
> Without the above steps, just with 'Enable natural sorting', the sorting is
> done correctly. It is there precisely for this kind of cases. Maybe Gnumeric
> has this option enable as default.

Perhaps it's an "enhancement"? to do what users expect, (match as Gnumeric
already does), it's logical that strings containing numbers, would be sorted by
their nominal value.

I saw in "Format Cells" that those fields were detected as "Currency". So it is
surprising again, that given a currency amount is a number.. it wouldn't sort
by the nominal value. Is that another "enhancement" request?

> 
> There is not a bug.

Reminds me of people characterising an issue as "a feature, not a bug".

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #8 from ady  ---
(In reply to Jonny Grant from comment #7)

> Yes, indeed I can change to English(US) and it loads, although oddly it
> swaps the $ USD to be a £ GBP symbol! that's a bug -- but depends if anyone
> wants to fix it?


Your reports are not reproduced by anyone ATM. Please go to ask.libreoffice.org
and try with other users to either replicate your problem, or search for
workarounds, or both.

As for this report being closed, if you can prove that there is really a bug as
you described in comment 0 that can be replicated by others, please feel free
to re-set this as unconfirmed. But, please, be sure that you have clear steps,
clear actual results, clear expected results, and that others can reproduce the
same problem.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #7 from Jonny Grant  ---
Thank you for the replies.

Note, this works without any clicking or changes in Gnumeric
Note, it does not work in Google Sheets
Does anyone have a copy of Excel to try with?


When I consider as a Software Engineer, yes, I can try all these ways to do it
myself on load.

When I consider from the point of view as user, we just expect it works. Yes,
indeed I can change to English(US) and it loads, although oddly it swaps the $
USD to be a £ GBP symbol! that's a bug -- but depends if anyone wants to fix
it?

I see it is already closed, so feels like no one agrees to match Gnumeric
behaviour

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #6 from ady  ---
(In reply to m_a_riosv from comment #5)
> They are not importer as numbers, but text. Doesn't matter that the cell
> format is standard.

I want to be clear.

Following the steps that I described (i.e. using "Detect special numbers"), the
columns as Standard and locale as English USA (in the upper field of the import
dialogue), the values are imported as numeric formatted as currency, not as
text.

The numeric values can be checked by Value Highlighting and by the ISTEXT()
function. They are also aligned to the right by default, without having to set
it manually.

Having the locale as English USA helps in identifying the dot/period as decimal
separator and the $ as currency, when it is used with "Detect special numbers".

I have the option for "Natural sort" disabled for the sort dialogue.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

m_a_riosv  changed:

   What|Removed |Added

 Status|UNCONFIRMED |RESOLVED
 Resolution|--- |NOTABUG
 CC||miguelangelrv@libreoffice.o
   ||rg

--- Comment #5 from m_a_riosv  ---
They are not importer as numbers, but text. Doesn't matter that the cell format
is standard.

I think the situation is solved with one of the next steps, to get column
values as numbers:

1. Select as locale 'English US' in the Text import window.
or
2. Click on the header of column with values and select US English for the
column type, in the Text import window.

Without the above steps, just with 'Enable natural sorting', the sorting is
done correctly. It is there precisely for this kind of cases. Maybe Gnumeric
has this option enable as default.

There is not a bug.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #4 from Jonny Grant  ---
(In reply to ady from comment #3)
> (In reply to Jonny Grant from comment #2)
> 
> > ^ see the numbers are sorted by nominal value, the larger are at the top.
> 
> That's exactly how I get it, sorted by value, the larger at the top, using
> either 7.6.3.2 or a recent 24.8 alpha on Windows.
> 
> Maybe someone else using some recent Dev version on Linux might be able to
> reproduce.
> 
> Maybe you have to review the options tab in the Sort dialogue.
> 
> BTW, are you using the sort dialogue? Or instead, are you pressing on any
> specific icon that immediately sorts?

There is a Sort button. If I do that and click options I can enable "natural
sort" which it then does work.

Or clicking menu "Sort" -> "Sort Descending"

> 
> Perhaps it would be better to post on ?

I did think that, but I could reliably reproduce it in 7.6.4.1 On ubuntu.
It's odd it works for you.

Gnumeric it works without clicking any special options, it determines it is a
currency and a number, so sorting works perfectly.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #3 from ady  ---
(In reply to Jonny Grant from comment #2)

> ^ see the numbers are sorted by nominal value, the larger are at the top.

That's exactly how I get it, sorted by value, the larger at the top, using
either 7.6.3.2 or a recent 24.8 alpha on Windows.

Maybe someone else using some recent Dev version on Linux might be able to
reproduce.

Maybe you have to review the options tab in the Sort dialogue.

BTW, are you using the sort dialogue? Or instead, are you pressing on any
specific icon that immediately sorts?

Perhaps it would be better to post on ?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #2 from Jonny Grant  ---
Perhaps this has been fixed recently? Could you share your full version
information.

Imported (using "Detect special numbers" ON) as:


Sort descending by column B:


test4   $9951.00
ts5t$995.00
afesf   $995.00
test3   $4360.00
test3   $436.00
test3   $3360.00
test1   $336.00
test3   $336.00
test2   $3.00


^ See the numbers are not sorted by nominal value.

What I expected:

test4   $9951.00
test3   $4360.00
test3   $3360.00
ts5t$995.00
afesf   $995.00
test3   $436.00
test1   $336.00
test3   $336.00
test2   $3.00

^ see the numbers are sorted by nominal value, the larger are at the top.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 159647] CSV import detect $ numbers

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=159647

--- Comment #1 from ady  ---
Imported (using "Detect special numbers" ON) as:

test1   $336.00
test2   $3.00
test3   $436.00
test3   $4,360.00
test3   $336.00
test3   $3,360.00
ts5t$995.00
test4   $9,951.00
afesf   $995.00


Sort descending by column B:

test4   $9,951.00
test3   $4,360.00
test3   $3,360.00
ts5t$995.00
afesf   $995.00
test3   $436.00
test1   $336.00
test3   $336.00
test2   $3.00


That is using a recent 24.8 alpha.

I admit that I don't understand the report. What is wrong (in whichever
version)?

-- 
You are receiving this mail because:
You are the assignee for the bug.