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 :
and
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
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
as number with no decimal placesand
to_char(100 * (PRV_SLS-PRV_EXP)/NULLIF(PRV_SLS,0) ,'fm9990.00')||'%'
to forrmat number as percentage with 2 dicimal placesit 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
yes correct
but no way to format the number as : 999,999,999 as number instead of text ?
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
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
ASKER
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
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.
I would export numbers only, and put all formatting on the excel side.
ASKER
No , I changed to number , then when I format in excell it comes like this
ASKER
i still have a problem with decimal
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 ?
(CASE WHEN grouping_level IN(1,3) THEN round(100 * CUR_SLS-CUR_EXP,2) ELSE NULL END) AS CUR_PROF,
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 ?
ASKER
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
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.
then you can't do what you want
Neither Toad nor Oracle has any control over how Excel will format the data.
ASKER
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
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
ASKER
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.
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.
ASKER
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.
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.
ASKER
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 ?
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.
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.
ASKER