Hi I’m using TOAD 9.7.2.5 when i export to excel instance month and day are swapped around. If i use the ‘export to file’ option i am able to set the locality through TOAD export to file option.
How do i set the Locality in TOAD so that when i export to excel instance it works…
In the ‘excel instance’ option, we send data to excel as variants,
which means that excel decides how to handle it and you can just change the date
format in excel.
Not sure about what you are doing. I don’t use the export option. I use save
as feature for the grid(output) either by right clicking the grid or from the
menu.
Either way you have several options at that point. If it is not a big deal u can
save as an excel file and it uses preset column specs for your data. However if
you save as a delimited file (text), it saves it as is. Then when you open the
file in excel you will need to specify the format of each column (unless it does
not matter, sometimes it does).
This way takes more time but is more accurate.
Hope this helps.
Gene
On Thu, Mar 24, 2011 at 7:54 PM, ozmike wrote:
Message from: ozmike
Hi I’m using TOAD 9.7.2.5 when i export to excel instance month and day
are swapped around. If i use the ‘export to file’ option i am able
to set the locality through TOAD export to file option.
How do i set the Locality in TOAD so that when i export to excel instance
it works..
Historical Messages
Author: ozmike
Date: Thu Mar 24 17:54:42 PDT 2011
Hi I’m using TOAD 9.7.2.5 when i export to excel instance month and day
are swapped around. If i use the ‘export to file’ option i am able
to set the locality through TOAD export to file option.
How do i set the Locality in TOAD so that when i export to excel instance
it works..
__
_______________________________________
–
Gene L. Bradley Jr.
Software Developer I
Information Systems & integration
Jackson State University
1400 J R Lynch Street
P.O Box 17750
Jackson MS, 39217
ph 601.979.1042
fax 601.371.9146
email gbradley@jsums.eduSaveAs was renamed to Export, so it’s the same feature.
Thanks …
I guessed as much, it would be exporting as variants…but if i format a column in excel…to
date DMY - its too late and date values have been swapped. So i guess the answer is no…export to excel instance can’t choose date format?
Export to file can choose date…(but this is very inconveinent for me as we have a document management system which intercepts the file save - export to instance is great…except for the date issue…
I would think that export to instance should be able to when export to file can! why isn’t it on the tab…?
Could you pre format the date in your query so that it comes out right on the
other side (i,e, you turn it around so that when export turns it around, it will
be correct?
On Mon, Mar 28, 2011 at 1:21 AM, ozmike wrote:
Message from: ozmike
Thanks …
I guessed as much, it would be exporting as variants..but if i format
a column in excel..to
date DMY - its too late and date values have been swapped. So i guess the
answer is no..export to excel instance can't choose date format?
Export to file can choose date..(but this is very inconveinent for me as we
have a document management system which intercepts the file save - export to
instance is great..except for the date issue...
I would think that export to instance should be able to when export to file
can! why isn't it on the tab..?
Historical Messages
Author: ozmike
Date: Sun Mar 27 23:21:08 PDT 2011
Thanks …
I guessed as much, it would be exporting as variants..but if i format
a column in excel..to
date DMY - its too late and date values have been swapped. So i guess the
answer is no..export to excel instance can't choose date format?
Export to file can choose date..(but this is very inconveinent for me as we
have a document management system which intercepts the file save - export to
instance is great..except for the date issue...
I would think that export to instance should be able to when export to file
can! why isn't it on the tab..?
__
Author: Jeff Smith
Date: Fri Mar 25 06:05:33 PDT 2011
SaveAs was renamed to Export, so it’s the same feature.
__
Author: gene.l.bradley
Date: Fri Mar 25 05:59:14 PDT 2011
Not sure about what you are doing. I don't use the export option. I use
save as feature for the grid(output) either by right clicking the grid or
from the menu. Either way you have several options at that point. If it is
not a big deal u can save as an excel file and it uses preset column specs
for your data. However if you save as a delimited file (text), it saves it
as is. Then when you open the file in excel you will need to specify the
format of each column (unless it does not matter, sometimes it does). This
way takes more time but is more accurate. Hope this helps. Gene On Thu,
Mar 24, 2011 at 7:54 PM, ozmike toadoracle@questmail.info > wrote:
Message from: ozmike Hi I'm using TOAD 9.7.2.5 when i export to excel
instance month and day are swapped around. If i use the 'export to
file' option i am able to set the locality through TOAD export to file
option. How do i set the Locality in TOAD so that when i export to excel
instance it works.. _______________________________________ Historical
Messages Author: ozmike Date: Thu Mar 24 17:54:42 PDT 2011 Hi I'm using
TOAD 9.7.2.5 when i export to excel instance month and day are swapped
around. If i use the 'export to file' option i am able to set the
locality through TOAD export to file option. How do i set the Locality in
TOAD so that when i export to excel instance it works.. __
_______________________________________ -- Gene L. Bradley Jr. Software
Developer I Information Systems integration Jackson State University 1400 J
R Lynch Street P.O Box 17750 Jackson MS, 39217 ph 601.979.1042 fax
601.371.9146 email gbradley@jsums.edu
__
Author: John Dorlon
Date: Fri Mar 25 04:30:33 PDT 2011
In the ‘excel instance’ option, we send data to excel as variants, which
means that excel decides how to handle it and you can just change the date
format in excel.
__
Author: ozmike
Date: Thu Mar 24 17:54:42 PDT 2011
Hi I’m using TOAD 9.7.2.5 when i export to excel instance month and day
are swapped around. If i use the ‘export to file’ option i am able
to set the locality through TOAD export to file option.
How do i set the Locality in TOAD so that when i export to excel instance
it works..
__
_______________________________________
–
Gene L. Bradley Jr.
Software Developer I
Information Systems & integration
Jackson State University
1400 J R Lynch Street
P.O Box 17750
Jackson MS, 39217
ph 601.979.1042
fax 601.371.9146
email gbradley@jsums.eduExcel stores the value as a number, so if it is more than just formatting, then
the actual value is getting changed? I’ve never heard of that before.
If that’s what’s happening, there is definitely a problem. You
should be able to adjust the date format in excel, and it shouldn’t be
changing the date value (format OK, value no). I don’t have time to spend
on it now, will look at it deeper soon.
When we write to file, we can use specific datatypes, (numbers, strings,
whatever), but when we write to excel instance, we are using COM and have no
choice but to use variants.
Yeah hi yes could write code to pre format but that defeats the point , my question is how to set locality when exporting to excel…instance
Hi Jordan
Thanks for the response when i export to excel instance,
i have a date type column in toad 2/12/2010 (2nd of dec aussie format)
it goes to excel is shows 12/2/2010 (wrong) then i format the column in excel to dd-mmm-yyyy
i get 12-feb-2010 (wrong). Funny thing is if the date is 28/2/2010 it leaves it as is but does not format it when i format column.
I have found a work around. When you format column - excel defaults the date format to English (australia) which is correct. if i change to english (us) and format MM/DD/YYYY column it fixes the problem. 12/2/2010 -> 2/12/2010 (correct), and 28/02/2011 stay as 28/12/2011, not sure why the export instance dosen’t pick up locality…weird …but i’ve worked it out…
Got in trouble from a manager once because of this issue- when the dates were swapped around (told her send the bill to Bill (gates)!
We have a joke here - about toads (as we have alot of cane toads)
Why did the TOAD cross the road?
To visit his flat mate!
Jordan Just as an aside , you could detect the if it was a Date column in the table and format it as DD-MMM-YYYY text, this would not be ambiguis to excel…
would need some sort of option in toad export to excel instance. eg.
Format all dates as text in safe format
DD-MMM-YYYY, MMM-DD-YYYY etc…
That seems to work here, and I think you are right about it being unambiguous.
I’ll put it in today’s beta. can you try it out and tell me if it solves your
problem?