Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

number format in toad

hi
sometimes i would like to export the result of my query from toad to ms excell
there , i have to every time to format the numbers , where always coming as currency format
i used those 2 formulas :
 to_char(cur_sls,'999,999,999') cur_sls

Open in new window

as number with no decimal places
and
to_char(100 * (PRV_SLS-PRV_EXP)/NULLIF(PRV_SLS,0) ,'fm9990.00')||'%'

Open in new window

to forrmat number as percentage with 2 dicimal places
it is ok in the result itself , but when exported to toad , i found that i need to again convert the strings to number
any way to direct convert as number
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NiceMan331
NiceMan331

ASKER

and what about the percentage with 2 decimal places
that's what the "2" is for in the round


if you append the '%' at the end, then you're turning the value into text again.

If you want % to show up in the results, but still be numeric, then let Excel do the formatting for you
yes correct
but no way to format the number as : 999,999,999 as number instead of text ?
no,  commas aren't numeric, commas are text

like percentage, if you want non-numeric characters in the representation and it should still be considered numeric, then let Excel do the formatting for you
ok , finally , and i don't know if this regards to toad or to excell it self
when i export to excell , and select format as comma , it becomes accounting format with currency symbol
any way to change it to instead of another step in excell
you're exporting text and excel is doing its best to interpret it.

I would export numbers only, and put all formatting on the excel side.
No , I changed to number , then when I format in excell it comes like this
i still have a problem with decimal
 (CASE WHEN grouping_level IN(1,3) THEN round(100 * CUR_SLS-CUR_EXP,2) ELSE NULL END) AS CUR_PROF,

Open in new window


in the data view of toad it displayed as 2.65
when exported to excell it comes  3
then i tried to formatted with comma in the toolbar of excell it comes 3 $
then should format cell to select no currency sympol with 2 decimal places to show again 2.65
should this posted as "excell" topic also ?
I am a topic advisor, I can add the excel topic if you want.

Did you set your numeric format to display 2 decimal places?

User generated image
yes , when doing formatting like this it is ok
but i want to save this step , because my query has alot of columns
and each column will have different format
in conclusion , i don't want to use formats of excel
>>>in conclusion , i don't want to use formats of excel

then you can't do what you want

Neither Toad nor Oracle has any control over how Excel will format the data.
When I'm copying data from any source to excell , the numbers stay general number format , except toad , when I copied from it to excell it coming with current format
>>> copying data from any source to excell , the numbers stay general number format

do that then.

copy to excel instead of creating a new file.

Don't export as "Excel File"  instead start Excel and then choose export as "Excel Instance"

When I do File, 2.65 it is formatted as 3,  when I export to an Instance it is formated as 2.65
one last thing,  are you choosing the "general format"  when you export?
When I do that, the excel file works,  if it doesn't for you, then try the Instance method.

or, you may have to contact Dell and put in a request for a new Toad feature


User generated image
I'm not sure I have this option , I will check it.  It may work , or , last question : any setting in the registry of oracle such as date format , is there any setting of numbers there ?
Those settings apply to your NLS_NUMERIC_CHARACTERS setting which controls your grouping and decimal characters.  (In the US  commas between thousands and millions and a period to indicate the decimal point.)

Plus, even if there was a formatting setting, that would mean the your numbers would be converted to text in order to satisfy the format.  So you wouldn't want to use such a thing even if it did exist.
So I will check that option in my toad
I'm using 12.7.1.11.

I don't know what version added the "general" option.  
I think it's been there for a while but I honestly don't use it much so I could be wrong.
the option is there , but still same issue
i think excell option , if you don't mind , could you added to excell topic
by the way , i just see the report section of toad , it seemed wonderfull tools
i want to learn about it
it will save a lot of time of exporting to excell
how i can learn about it ?
it's unlikely an Excel person can tell you how to get toad to send data differently.

If toad created the file, then excel can't do anyting because toad is in control.

Once the data is in toad, you already know how to format it.

If you use the export to excel instance option you might have some options with vba that would detect the new worksheet being added and format the data for you.

But, both the general option on file create and export to instance work for me.
If toad isn't doing what you want you will probably need to talk to Dell.
The toad staff is very responsive.