I need help please! I often transfer data and dates from Toad to Excel using Automation Designer apps from American format Oracle db - but I use Danish date format and Danish Excel so the date format I want to use is "dd-mm-yyyy". However, the dates look wrong and are sometimes distorted after transfer to Excel. In Toad Automation Designer you can only choose "send month" as "fully spelled out" or "3 letter abbreviation" - not like "dd-mm-yyyy" which is annoying since I can't correct the output in Excel even not after choosing "date format" in Excel (all is "standard" in Excel until you choose other format - although some of the dates looks OK in that case). If I convert dates in TOAD to strings I can solve it but then the dates won't sort correctly even if I choose date format in Excel. Maybe I can solve the problem by programming a solution in Excel VBA but I would rather solve the problem in Toad. How can I solve this? Thanks!
(If any TOAD employee reads this - please consider changing the "send month" option to also also include the choice " "dd-mm-yyyy" format and so on. Thanks!).
P.S. The funny thing is that if I use copy and paste from Toad to Excel the dates are fine - but that isn't a solution since it would be a lot more time consuming and then I can't transfer a lot of tables simultaneously - the reason why I use "Automation Designer".
I see you are using the "Excel Instance" format. Have you tried "Excel File"? In that case, we write the file ourselves and have a lot more control. In that format, you can specify the date format exactly how you want.
When you use the "Excel Instance" format, Toad talks directly to Excel through Excel's API. In that case, we can only send dates as variants (in which case Excel thinks they are numbers) or strings (in which case Excel should convert them to dates). Up to now, either the 3 letter or fully spelled out options has worked for everyone. There was some problem with using numeric dates, but to be honest, I don't remember what it was right now.
Thanks but I have to name the Excel files after the transfer so unfortunately I can't use your solution. I think my problem is due to the fact I need the Danish date format in Danish Excel version - if you have a database in your native format and use that for Excel dates as well I imagine you don't have my problem at all. Like you say "Up to now, either the 3 letter or fully spelled out options has worked for everyone" - so I guess I'm the only Dane with an American format database (not my choice) who also use Automation Designer.
Why do you have to name the files after export? Did you know that you can use variables in the filename?
For example, you could name it c:\%USER%_%DB%.xlsx so that the file would be named after the user and database that you exported from. Right-click in the filename box and choose "Variables" to see a list of the built-in choices. If you need more, you can create more and give them values from external sources. (such as a list of table names). You should be able to fully automate this. Let me know how you are naming the files and I'll help you do that automatically.
Regarding date formats:
I could probably add the ability to specify the language for the month codes (or fully spelled out) but I think you can control it with your windows settings.
Every Oracle database stores dates the same way. As numbers with decimals. It is your environment that decides how they are displayed. In this case, the language that the date format is showing in comes the language defined in your windows regional settings.
Thanks again! I'm busy now but I'll test your proposed solutions asap.