date format problem when exporting to Excel instance

Toad for Oracle
1

Hi all,

in Toad for Oracle 11.6.1.6 I execute this query:

select to_date(‘31.03.2013’,‘dd.mm.yyyy’) date_value from dual

In data grid I get date_value: 31.3.2013.

Then I go to “Export Dataset” and choose export format “Excel instance”. I uncheck all the checkboxes, press OK and get a new sheet in my currently open Excel 2010 file (xlsx). The problem is that my date_value is now formatted like this: 31-march-2013

This format does not correspond to any date formats that I have set in my windows control panel. Excel treats it as a string, not as a date, and I can’t do any date operations on this value in excel. I would like to get a date value in excel: 31.3.2013

I’ve also tried exporting it to an older version of Excel file (xls). I’ve tried changing date format in windows control panel. Nothing helped.

This used to work fine in Toad 10.

As far as I can see there is no option in Toad to set the date format when exporting to Excel instance. When exporting to Excel File there is an option to set the date format, and it works fine, but exporting to a File is not good for me for some other reasons (strings which contain only numbers are are converted to numbers in Excel and I don’t want that to happen).

Is there a way to get the correct date format in Excel instance export in Toad 11.6?

Any help is appreciated.

Thanks!

Dalibor

2

  1. Data grid’s date format is defined differently from any NLS setting (Database|Administer|NLS Parameters)-it is defined by Toad’s Option|Data Grids|Data … Display … “Date format”.
    So do not mix date format and date representation in toad grid-they are totally different.

  2. Do you know that in Excel a date is stored as a number and not as a formatted string or anything like this? So this might have problems with your WIndows regional setting and according all mentioned in 1) I think you might find a solution …

3

Hi Damir,

thanks for your answer.

I understand what you're saying. I know that database NLS date format is something different than TOAD grid date display format.

I know that Excel internally stores dates as numbers. I've done some additional research on the data I get in Excel:

When I get the date from TOAD in cell A2 in Excel as 31-march-2013 (or 31-ozujak-2013 in Croatian language) and in cell B2 I write formula =A2+0 I get value 41364. This is number value of that date.

ScreenShot084.jpeg

Now if I go to Format Cells for cell B2 and I set that it's a Date field (and not General) I get it in correct format:

ScreenShot085.jpeg

Ok, let's try to do that on cell A2 (which is originally exported from TOAD). I go to Format Cells for cell A2. It is "General". I change it to Date:

ScreenShot086.jpeg

I press OK, but nothing changes in the display of cell A2. It's still 31-ozujak-2013:

ScreenShot087.jpeg

So ... It is a date (when I do B2=A2+0), and at the same time it is not a date (it doesn't behave like a date when I try to format it in Excel). It looks to me as a problem in a way that TOAD serves this data to Excel.

In my previous post I wrote that I can't do any arithmetics on that data. As we can see from these examples that is not true, I don't know what I was looking at when I wrote it :slight_smile: Sorry for that.

Ok ... why does this problem bother me? Because I need to export data to Excel for my users, and they are not happy with date format 31-ozujak-2013, they would like it to be 31.03.2013.

Ok, I could do some changes in the Excel after I export it - make a new column where I would add zero to my date (e.g. A2+0) and then format it as date and do copy/paste values and then get rid of the original column. But ... there can be a lot of date columns in my exported data and this could be a lot of work sometimes, and it's prone to errors.

This used to work fine in TOAD 10, and now it doesn't work on TOAD 11.6.1.6.

In my database (release 11.1.0.7) NLS_DATE_FORMAT is DD.MM.RR

In TOAD data grid options I have date format dd.mm.yyyy.

In Windows (win7) I have regional settings set for Croatia, like this:

ScreenShot088.jpeg
ScreenShot088.jpeg477×553 39.1 KB

I've tried changing my short and long date format in windows, but that didn't help.

4

Hi Dalibor,

When Toad exports to Excel Instance, Toad only passes data and Excel takes care of the formatting. If you export to Excel File then you will see date format options appear in the Export Dataset window and Toad will pass over the formatting information. As far as this working in Toad 10, I can’t answer this for sure, but maybe a different version of Excel is being used or maybe your Windows date format has changed?

Thanks,

Brad

5

Hi Brad,

Thanks for your post.

I’m using Excel 2007, and it worked with Toad 10. When I switched to Toad 11 it started behaving as I described. Nothing else has changed, it’s the same machine, Excel version is the same, Windows settings are the same.

In fact, I still have Toad 10.1.0.7 installed on this machine, so I’ve tried export to Excel instance now from both Toads:

  • it works ok from Toad 10.1.0.7, date is formatted in Excel as 31.3.2013.

  • and fromToad 11.6.1.6, date is formatted in Excel as 31-ozujak-2013

I don’t know if it’s relevant, but my Windows are 64-bit and Toad 10 and Toad 11 are both installed as 32-bit applications, whereas Excel is 64-bit. But, it still works ok with 32-bit Toad 10.1.

I couldn’t install 64-bit Toad because I have 32-bit Oracle client installed, and also some other 32-bit applications that use that client, so it would be too much trouble to switch everything to 64-bit.

The question is stilll open …

6

I can confirm there was some kind of change made between 10 and 11 to fix a larger bug which changed the way we pass the data to excel, but the developer (John) will have to do a little digging to see exactly what had changed. He is on a plane right now so he will be able to reply either tonight or tomorrow. Thanks

7

We send data to excel as a variant, so the if the data is a number, it will be sent as a number, strings go as strings, dates as dates, etc. We had some
reports of Excel getting the days and months mixed up, so 12/10/2001 would come in as December 10th when it was meant to be October 12th . So then we started sending dates as strings, in the format of DD/MONTH/YYYY. Excel seems to
recognize this format as a date no matter the language. You can change the format in excel if you like.

From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Thursday, June 13, 2013 12:43 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] date format problem when exporting to Excel instance

I can confirm there was some kind of change made between 10 and 11 to fix a larger bug which changed the way we pass the data to excel, but the developer (John)
will have to do a little digging to see exactly what had changed. He is on a plane right now so he will be able to reply either tonight or tomorrow. Thanks

8

Hi John,

Thanks for your post. This explains the behaviour I described - I’m getting 31-ozujak-2013 (“ozujak” is a name for March in Croatian language).

You said that I can change the format in Excel. I’ve tried that, but it doesn’t work. Here’s a part of my earlier post (you can see it if you scroll up):

Ok, let’s try to do that on cell A2 (which is originally exported from TOAD). I go to Format Cells for cell A2. It is “General”. I change it to Date:

ScreenShot086.jpeg

I press OK, but nothing changes in the display of cell A2. It’s still 31-ozujak-2013.

I’ve also tried to set Custom format dd.mm.yyyy, but that doesn’t work either - exported field is still displayed as 31-ozujak-2013

My clients are accountants, and this date format is most unusual for their business, they want it to be 31.03.2013 because it’s easier to visually compare dates in that format.

You can also see in my earlier post that I have found a workaround, but it’s rather complicated to be used in daily work, especially with large amount of data. It involves copying data to a new column with formula, e.g. B2=A2+0, and then copy/paste values in column B and get rid of column A.

Can you recommend something else that I could try?

Regards,
Dalibor

9

Hi Dalibor,

March 31st is my birthday!

It seems that for some reason, Excel is not recognizing that value as a date and that’s why it is not formatting it as you wish. I don’t know why it is acting
that way, especially given your workaround. I am glad you found a workaround, but agree that it is a pain. Have you tried the exporting to the “Excel File” format?

-John

From: DaliborK [mailto:bounce-dkovac9732013@toadworld.com]

Sent: Wednesday, June 19, 2013 2:15 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] date format problem when exporting to Excel instance

Hi John,

Thanks for your post. This explains the behaviour I described - I’m getting 31-ozujak-2013 (“ozujak” is a name for March in Croatian language).

You said that I can change the format in Excel. I’ve tried that, but it doesn’t work. Here’s a part of my earlier post (you can see it if you scroll up):

Ok, let’s try to do that on cell A2 (which is originally exported from TOAD). I go to Format Cells for cell A2. It is “General”. I change it to Date:

I press OK, but nothing changes in the display of cell A2. It’s still 31-ozujak-2013.

I’ve also tried to set Custom format dd.mm.yyyy, but that doesn’t work either - exported field is still displayed as 31-ozujak-2013

My clients are accountants, and this date format is most unusual for their business, they want it to be 31.03.2013 because it’s easier to visually compare dates in that format.

You can also see in my earlier post that I have found a workaround, but it’s rather complicated to be used in daily work, especially with large amount of data. It involves copying data to a
new column with formula, e.g. B2=A2+0, and then copy/paste values in column B and get rid of column A.

Can you recommend something else that I could try?

Regards,

Dalibor

10

Hi John,

What a coincidence this fact with your birthday! It looks like we were destined to meet, if only on ToadWorld forum :slight_smile:

I was not using Export to Excel File because in the previous Toad version (10.1) I’ve had problems with number values (some of them came out already formatted in a strange way, with no decimal digits or so, and there were some confusions about decimal and thousand separators), so I gave that up.

But now I have tried Export to Excel File in new Toad (11.6) and it seems that now it deals with numbers differently and they come out OK for me. And date values are also ok.

So, I guess I’ll be using Export to Excel File more often now.

Thank you for your help, I consider my question answered.

Regards,

Dalibor

11

Hi

Cretae Excel file with correct format.

Save it.

In Export dialog choose “Set locality” (under Date format) and select correct Excel file (correct CP).

Export …should be as you like.

Rg

Damir Vadas

12

Good idea. Thanks Damir.

13

Hi Damir,

sorry for late reply, but I’ve been on vacation and after that rather busy.

I’ve tried this solution you proposed (set locality), but this works only for export to “Excel File”, and not for export to “Excel instance”.

So, it doesn’t solve the problem with export to “Excel Instance”, but as I said in my previous post - 11.x version of TOAD works ok for me when I export to “Excel File” (and I’ve had some problems with that in previous versions of TOAD), so I’ll use export to “Excel File” when I need correct date format.

I’m getting ready to try TOAD 12 one of these days, so I’ll post again when I try export to Excel Instance in TOAD 12.

Regards,

Dalibor

14

I created an idea in the Idea Pond to allow you to decide on the date format when exporting to an Excel Instance

http://toadfororacle.ideascale.com/a/dtd/Decide-on-Date-Format-Export-to-Excel-Instance/446665-8477

15

Alan, that’s great. You have my vote!

16

Fixed link to idea pond toadfororacle.ideascale.com/…/446665-8477

17

On my german system using ORA 10g, toad 12.1, Win 7 and Office 2007 export to “excel instance” shows the correct date, when it’s formatted as:

select to_char(sysdate,‘MM/DD/YYYY’) curr_date from dual;