Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Convert number value in text field to number but still keep text value
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Convert number value in text field to number but still keep text value
 Login/Join
 
Platinum Member
posted
In database, I have a column which has the format is varchar2, data of this column can be text or number. When I export the report to excel, cells which have data as the number have the green triangle at the top left corner of those cells indicate that they are format as text. Other cells which have data as text don't have this green triangle. Is there anyway that when I export the report to excel, it can recognize these values are number, convert them to number and eliminate these green triangle.

This message has been edited. Last edited by: hainguyen,


WebFOCUS 7.7.03
Windows, All Outputs
 
Posts: 125 | Registered: June 17, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Are you exporting this from SQL Server?


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
My database is Oracle.


WebFOCUS 7.7.03
Windows, All Outputs
 
Posts: 125 | Registered: June 17, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
I'm not familiar with Oracle. Is there an option to export your data to a .csv file? I use this option in SQL Server and when I open the file in Excel, it converts numbers as text to numbers.


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
It's the way Excel react with multiple data type in the same column.

In Excel (which as to be set to each computer) go to :
File/Options/Formulas and uncheck : "Numbers formatted as text or preceded by an apostrophe"

It may resolve the case.


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2191 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hallway: Look like there is no CSV format. However, I need to export it to Excel for my users.
MartinY: This solution almost solves my issue. There is only one thing, when I do Sum for the range of cells which contains number, it returns 0 but if I do '+' or '-', it run just fine. Don't know why this happen. My excel is 2010.


WebFOCUS 7.7.03
Windows, All Outputs
 
Posts: 125 | Registered: June 17, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You may want to do some research on the SUMIFS function in Excel, looks as though you may be able to use that for your sum.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 144 | Registered: November 09, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Well, I can't tell my users to use SUMIFS instead of SUM. I think there should be any setting in Excel let me do that. But as my research, SUM will not work with "number formatted as text".


WebFOCUS 7.7.03
Windows, All Outputs
 
Posts: 125 | Registered: June 17, 2013Reply With QuoteReport This Post
Master
posted Hide Post
Well, personally I would split up the incoming data into two columns with a define, so the numeric data ends up in one column in Excel and the text in another. Then the users could do the SUM on the numeric data - or you could do it yourself in the report. And if they don't like that approach I'd just invite them to do better themselves (I'm using parliamentary language here .... Internally I'm thinking of something far less polite.)


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
The only additional question I would pose is do you even need to display the non-numeric data in that column?

If so then the solution George provided is probably going to be your best bet if you want to avoid any additional hassle for the end-user.

If you do not need the non-numeric data to display why not create a view in the Oracle db for your data-set that limits to only numeric data in that column? Then from there you could create your synonym off that view and report off that.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 144 | Registered: November 09, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
OK, that column's name is RESULT, it will show the results of differest TESTs. Some TEST's results are number : 0.23, 4.3... But others are text : <0.111, YES, NO. And the challenge thing is they will do calculation, SUM... on the result which are the number to do some analyze. There is old application which is not WebFocus can do that when it exports to Excel, I just try to do the best to duplicate that application so we can replace it. But it seems there is still some limit we can do. Maybe, I will just tell them just convert it using Excel. Thanks all for your suggestion. I will close the case here.


WebFOCUS 7.7.03
Windows, All Outputs
 
Posts: 125 | Registered: June 17, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Convert number value in text field to number but still keep text value

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.