[Solved] CSV imports as text rather than numbers

Discuss the spreadsheet application

[Solved] CSV imports as text rather than numbers

Postby jackrcook » Tue Jan 15, 2019 3:55 am

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.5 ~ Mac OS 10.14.1 Mojave running on an iMac
jackrcook
 
Posts: 209
Joined: Sun May 25, 2014 11:08 pm

Re: CSV imports as text rather than numbers

Postby RusselB » Tue Jan 15, 2019 6:45 am

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4936
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: CSV imports as text rather than numbers

Postby Villeroy » Tue Jan 15, 2019 12:01 pm

"$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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26572
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Postby robleyd » Tue Jan 15, 2019 12:09 pm

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
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2570
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: CSV imports as text rather than numbers

Postby Villeroy » Tue Jan 15, 2019 12:10 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26572
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Postby Villeroy » Tue Jan 15, 2019 12:13 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26572
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Postby Villeroy » Tue Jan 15, 2019 12:18 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26572
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CSV imports as text rather than numbers

Postby jackrcook » Wed Jan 16, 2019 4:55 am

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.5 ~ Mac OS 10.14.1 Mojave running on an iMac
jackrcook
 
Posts: 209
Joined: Sun May 25, 2014 11:08 pm

Re: CSV imports as text rather than numbers

Postby RusselB » Wed Jan 16, 2019 6:09 am

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4936
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: CSV imports as text rather than numbers

Postby jackrcook » Wed Jan 16, 2019 8:06 pm

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 13 times
Open Office 4.1.5 ~ Mac OS 10.14.1 Mojave running on an iMac
jackrcook
 
Posts: 209
Joined: Sun May 25, 2014 11:08 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 32 guests