Page 1 of 1

[Solved] CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 3:55 am
by jackrcook
I have been importing a CSV file of financial data weekly for a couple of years quite successfully.
Suddenly all is fine except the column containing "$1234,56".
This comes in as text now. What needs to be tweaked so that I do not have to manually convert these into values?
Thanks. in advance for any help.

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 6:45 am
by RusselB
In the text import dialog ensure that the Detect Special numbers box is checked.
With OpenOffice this box is unchecked by default, and will go back to the unchecked status upon the closure of the import dialog.
With LibreOffice this option is checked by default, but the setting is remembered for the next time the dialog opens.

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 12:01 pm
by Villeroy
"$1234,56" is a number with comma as decimal delimiter and a dollar sign as currency symbol. There is no way to get this special number interpreted since all "dollar locales" use the point as decimal separator.
Select the imported text column.
Apply some US or Australian number format to the selected cells and replace commas with points.

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 12:09 pm
by robleyd
Australian number format

We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 12:10 pm
by Villeroy
Alternatively:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2;",";MID(1/2;2;1));"$";""))
inserts the right decimal separator, removes the "$" and gets the numeric value.

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 12:13 pm
by Villeroy
robleyd wrote:
Australian number format

We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.


If you ever get a European file with comma decimals, choose a matching locale and you get the right numbers with points. But there is no matching locale with both comma and "$".

Re: CSV imports as text rather than numbers

PostPosted: Tue Jan 15, 2019 12:18 pm
by Villeroy
Example:

2.1.19;€13.897,99
3.1.19;€12.345,99

copy the two lines, paste special, choose "German(Germany)", semicolon as column separator and "special numbers".
The result will look like this with an Australian locale:

2/1/19 13897.99
3/1/19 12345.9

and like this with a US locale
1/2/19 13897.99
1/3/19 12345.9

Re: CSV imports as text rather than numbers

PostPosted: Wed Jan 16, 2019 4:55 am
by jackrcook
Villeroy wrote:"$1234,56" is a number with comma as decimal delimiter and a dollar sign as currency symbol. There is no way to get this special number interpreted since all "dollar locales" use the point as decimal separator.
Select the imported text column.
Apply some US or Australian number format to the selected cells and replace commas with points.

This "." was a miss type on my part. It was a period not a comma.

Re: CSV imports as text rather than numbers

PostPosted: Wed Jan 16, 2019 6:09 am
by RusselB
That's what I was thinking when I made my original post.
Follow my earlier advice and everything should be good.
If not, by all means post back with an example of what you get along with the settings in the text import dialog

Re: CSV imports as text rather than numbers

PostPosted: Wed Jan 16, 2019 8:06 pm
by jackrcook
Thanks for all the help. I played with the Text import options and found a set that worked.
See the attachment. Imports as numbers again.