[Solved] Data imported from CSV, cells are text only

Discuss the spreadsheet application

[Solved] Data imported from CSV, cells are text only

Postby paulc2019 » Fri Nov 08, 2019 7:02 pm

Hi,

I'm importing a CSV of investment transactions into Calc.
In the CSV, every field is quoted in doublequotes, but once imported every cell is treated as text (I think this is indicated by the value being prefixed by ' when viewing it in the formula bar)
As a result I cannot use any formulae which involve the cost field, and trying to set the formatting to currency doesn't help either.

Example CSV line

"Business","Total Cost","Shares","Current price","State"
"AP Brands","£3,354.50","220","£19.71","Active"

How can I fix this?
Last edited by RoryOF on Fri Nov 08, 2019 8:44 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.3, OSX 10.14.6
paulc2019
 
Posts: 3
Joined: Fri Nov 08, 2019 6:55 pm

Re: Data imported from CSV, cells always formatted as text o

Postby RusselB » Fri Nov 08, 2019 7:23 pm

Welcome to the Forums.
In the Import dialog there is a checkbox for Detect Special Numbers.
In OpenOffice this checkbox defaults to being unchecked, so you need to check it each time in order for the numbers to correctly import.
OpenOffice 4.1.7 and LibreOffice 6.3.3.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.
User avatar
RusselB
Moderator
 
Posts: 6119
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data imported from CSV, cells always formatted as text o

Postby MrProgrammer » Fri Nov 08, 2019 7:24 pm

Hi, and welcome to the forum.

paulc2019 wrote:every cell is treated as text (I think this is indicated by the value being prefixed by ' when viewing it in the formula bar)
[Tutorial] Text to Columns Q41/A41

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Data imported from CSV, cells always formatted as text o

Postby paulc2019 » Fri Nov 08, 2019 7:29 pm

Thanks, that works great.

I'd actually tried ticking that earlier, but I had also ticked "Quoted field as text" (which was a bit dumb in hindsight as its exactly what I didn't want).

Just out of interest, is this something that can only be done during import?
After importing, my only option would be to remove leading ' from all cells before I could then format them as numbers?

Thanks
OpenOffice 4.1.3, OSX 10.14.6
paulc2019
 
Posts: 3
Joined: Fri Nov 08, 2019 6:55 pm

Re: Data imported from CSV, cells always formatted as text o

Postby RusselB » Fri Nov 08, 2019 8:09 pm

To do this after importing data use the Text to Columns under Data and specify the same column for the output
OpenOffice 4.1.7 and LibreOffice 6.3.3.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.
User avatar
RusselB
Moderator
 
Posts: 6119
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data imported from CSV, cells always formatted as text o

Postby paulc2019 » Fri Nov 08, 2019 8:20 pm

Very useful feature, thanks a lot for the help!
OpenOffice 4.1.3, OSX 10.14.6
paulc2019
 
Posts: 3
Joined: Fri Nov 08, 2019 6:55 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests