I’ve been using Toad for Sql Server Freeware for over 5 years now and only after getting the Enterprise edition and upgrading to 5.6.0 have I noticed this. When I export results to an excel instance all calculated fields with a result other than 0 will show to 5 decimal places regardless of the result. For instance, I have a report which calculates an employee’s age, years of service and days of service. The age and years of service involves more calculations beyond datediff(), while service days is only datediff(). In the Results Set the age, years of service and days of service will show as 55, 30, 10983 respectively. However when I export to an excel instance the results will show as 55.00000, 30.00000 and 10983, respectively. This happens with all decimal fields, 2 of which have decimal(12,4) and decimal(12,2) datatypes, and all calculations regardless of the result or the datatype in the table. This gets rather annoying when I have to reformat all those fields in excel everytime. Is there a setting in Toad to change the way these are formatted when exported? The desired outcome would be if the results in Toad are 55, 3.1, and 21.488 that’s how the results would show in excel instead of 55.00000, 3.10000 and 21.48800.
Can you check your number settings at Options | Environment | Grid | Data Type Formatting, if number format is General ( G), it should not show the decimals.
it was set to Standard (), but changing it to General (G) it does the inverse and would show 55.00000, 3.10000 and 21.48800 as 55, 3 and 22. Switching between the different formats it doesn’t seem to have a total solution but instead an all-or-nothing whereby ALL the fields have no decimels when exported into excel or ALL fields have a fixed number of decimels, regardless of the actual number. I even tried to do a custom with no success.
That’s true. Since your column is defined as decimal instead of int, the format will applied to all decimal fields.
That’s true. Since your column is defined as decimal instead of int, the format will applied to all decimal fields.
Is there any way to fix this? it is becoming increasingly annoying to have to reformat every single field after exporting result to Excel.
Jbranski !
Though I understand your complaint, I respectfully disagree, I do not see this
as a problem and I want that level of precision.
I adjust the who column not the individual cell, which I am sure you know how to
do, OR I copy and paste the result window to a predefined excel worksheet with
paste values.
Hank Freeman
Senior Systems, Database/Data Warehouse Architect
678.414.0090 my cell Primary
Hank.Freeman50 (Skype)
Jbranski !
Though I understand your complaint, I respectfully disagree, I do not see this
as a problem and I want that level of precision.
I adjust the who column not the individual cell, which I am sure you know how to
do, OR I copy and paste the result window to a predefined excel worksheet with
paste values.
Hank Freeman
Senior Systems, Database/Data Warehouse Architect
678.414.0090 my cell Primary
Hank.Freeman50 (Skype)
