Toad World® Forums

Export to Excel : Oracle CHAR fields should go to Excel text column as default

Now you have , in the export window to flag the option "Treat string fields as strings" which is not the default. I assume that if you have defined a field as CHAR is quite obvious that you want it as TEXT in Excel. If not done you have this effect

Oracle
SELECT '13865100000000027' CODE FROM DUAL;
In Excel 13865100000000000 (27 lost)

Oracle
code name
'007' Bond
Excel
7 Bond

Sorry, I'm not going to change the default values of some checkboxes based on the table that you are exporting from. Some people might not agree with your default choices anyway. The good news for you though, is if you export your table from Schema Browser, it will remember your choices if you export that table again.

Also you can set up jobs in Automation Designer that will remember your choices.

Hello, I understand your point of view , but I cannot see why some people (or in which occasion) a column of type character in Oracle should be desired as number in Excel, can we say that is 1 case out of 100 ?

If so this is why I ask the behaviour text->text as default

Thanks

GF

image001.jpg

Actually, the more I think about this, the more I think you are right. If I use the "Excel File" option instead of "Excel Instance", these fields are sent with string formatting. In the Excel Instance export format, we have to send everything to Excel as a variant. Then Excel decides how to format it based on content (unless of course, we send the data with a quote before it, to tell Excel "this is always a string".

The green marker that Excel adds is only present when you have numeric-looking data that has the quote before it.

The only downside I see of sending string fields as strings, is that it prevents you from formatting them as numbers (or dates, or whatever) in Excel later if you want to. And maybe for that, there is some way of selecting multiple values and removing the quote. Not sure about that.

I think your request makes sense. I just want to be very careful with changing it, as Export Dataset is a very highly used feature in Toad.