[Solved] CSV imports as text rather than numbers
[Solved] CSV imports as text rather than numbers
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.
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.
Last edited by jackrcook on Thu Jan 17, 2019 3:17 am, edited 1 time in total.
Open Office 4.1.13 ~on Mac OS 12.6
Re: CSV imports as text rather than numbers
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: CSV imports as text rather than numbers
"$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.
Select the imported text column.
Apply some US or Australian number format to the selected cells and replace commas with points.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: CSV imports as text rather than numbers
We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.Australian number format
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: CSV imports as text rather than numbers
Alternatively:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2;",";MID(1/2;2;1));"$";""))
inserts the right decimal separator, removes the "$" and gets the numeric value.
=VALUE(SUBSTITUTE(SUBSTITUTE(A2;",";MID(1/2;2;1));"$";""))
inserts the right decimal separator, removes the "$" and gets the numeric value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: CSV imports as text rather than numbers
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 "$".robleyd wrote:We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.Australian number format
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: CSV imports as text rather than numbers
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
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: CSV imports as text rather than numbers
This "." was a miss type on my part. It was a period not a comma.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.
Open Office 4.1.13 ~on Mac OS 12.6
Re: CSV imports as text rather than numbers
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
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
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: CSV imports as text rather than numbers
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.
See the attachment. Imports as numbers again.
- Attachments
-
- CSV_Import_Setttings.pdf
- (99.32 KiB) Downloaded 215 times
Open Office 4.1.13 ~on Mac OS 12.6