[Solved] CSV imports as text rather than numbers

Discuss the spreadsheet application
Post Reply
jackrcook
Posts: 226
Joined: Sun May 25, 2014 11:08 pm

[Solved] CSV imports as text rather than numbers

Post 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.
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
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: CSV imports as text rather than numbers

Post 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.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Post 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.
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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: CSV imports as text rather than numbers

Post 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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Post by Villeroy »

Alternatively:
=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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Post 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 "$".
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Post 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
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
jackrcook
Posts: 226
Joined: Sun May 25, 2014 11:08 pm

Re: CSV imports as text rather than numbers

Post 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.
Open Office 4.1.13 ~on Mac OS 12.6
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: CSV imports as text rather than numbers

Post 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
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.
jackrcook
Posts: 226
Joined: Sun May 25, 2014 11:08 pm

Re: CSV imports as text rather than numbers

Post 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.
Attachments
CSV_Import_Setttings.pdf
(99.32 KiB) Downloaded 175 times
Open Office 4.1.13 ~on Mac OS 12.6
Post Reply