Decimal digits not showing in Excel using export to file

Toad Data Point & Toad Intelligence Central
1

Hello everyone,

when I export data to Excel using the “export to file” option, the decimal digits are not showing in Excel. The data itself is there, I can format the cell/column in Excel to standard and the digital digits are being displayed then.
Exporting to an Excel instance, everything is fine.
The data in the database however is in varchar, therefore I have to use to_number(data) in my query. In the result set everything looks o.k., copy and paste from there to Excel also is fine.
Unfortunately, automation scripts use the export to file functionality.
Playing around with the TDP options, i.e. grid numbers did not solve the problem.
Anyone else seeing this behaviour?

Best wishes,
JochenR

2

I see what you mean. I tried to find a work around for exporting to excel but could not come up with one. So I entered CR108465 to see what we could do.

Can you export to html or csv instead? Would these formats work for you? Or you can use the export wizard and there are many more export types that might be of use.

Debbie

3

If number format is General, no decimal will show. Please try set it to Standard() or Number() in TDP options page.

4

Unfortunately this does not help. Tried alle the settings. But I found a workaround. See posting above.
Thanks,
JochenR

5

Thanks for the quick reply. I found a workaround. I am using an Excel-file with macros (xlsm) now. Utilizing VBA I format the respective columns appropriatly. Since you set up a CR I am confident that we will see an Toad solution in one of the next releases.

Thanks a lot,
JochenR

P.S. I am using Toad for around 10 days now, and I already realized that it is a very powerful and versatile tool , though only scratching at its surface so far .

6

I see what you mean. I tried to find a work around
for exporting to excel but could not come up with
one. So I entered CR108465 to see what we could do.

Can you export to html or csv instead? Would these
formats work for you? Or you can use the export
wizard and there are many more export types that
might be of use.

Debbie

Hi Debbie,

Do you have any idea whether or not this will make it into the 3.3 release? We really need to upgrade to 3.3 in production, but many of our reports require decimal output (Lat, Long, Money, etc.) and must be exported to Excel.

Converting all of our reports to use XLSM and a VBA macro to correct the formatting will be far too time consuming to be practical.

-N

7

My understanding of the issue is that the formatting issue only occurs when you have a varchar column that is used as number. I wouldn’t think that would be a very common use case.

When exporting to file we set the format by looking at the datatype of the column. Since it is vrachar we do not use a number format.

When exporting to Excel instance, Excel formats off of the cell value. These are two different methods.

We looked at the code and we cannot fix this as it is not a bug. What we plan to do is offer to allow the user to set the format string in the export wizard. This is an enhancement and will not make it into the next release.

Is it common for numbers to be stored in varchar columns? If so can you provide use case for this so I can better understand?

Debbie

8

Here is a Excel reference on some manual ways to work with numbers in text columns. Perhaps this may help.

http://office.microsoft.com/client/helppreview.aspx?AssetId=HP012167619990&lcid=1033&NS=EXCEL&Version

Debbie

9

I have the same issue using an Oracle table where my value is stored as NUMBER(28,10), so this not on caused by using varchar.

10

Hi

I’m having the same problem with decimal (16,2) format since Toad Version 6.0… Before the 6.0 it was ok…

BR

Dejan

11

Do you have any idea whether or not this will make it into the 3.3 release? We really need

to upgrade to 3.3 in production, but many of our reports require decimal output (Lat, Long,

Money, etc.) and must be exported to Excel.

N.,

I would skip Toad Data Point 3.3 and go directly to 3.6

  • Greg
12

Decimals in excel, old problem !

Toad create excel files with unformatted columns,

so, i always export data to an existing and formatted empty file.

If you use the “group execute” option, don’t go on 3.6 !