Copy to Excel Date Field Q.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonathan Morningstar - 24 Jul 2008 16:31 GMT

How, when you copy a dbf out to an XLS file, how can you make the date range
sortable? What I am getting is the field is only sorting my month, not
month/day/year. Any knowledge that you can share would be great.

Respectfully,

Jonathan Morningstar
Greenville, South Carolina USA

Reply to this Message

Olaf Doschke - 24 Jul 2008 17:06 GMT

I don't know how you export to xls,
but if I simply do this:

CREATE CURSOR curDates (dDate D)
INSERT INTO curDates VALUES (DATE())
EXPORT TO test.xls TYPE xls

And then open up the xls file with Excel (2003)
If I click on A2 Excel let's me choose amongs others to
accept a date value with only two digits for the year
convert it to 19XX or 20XX.

So the date is exported to the xls file without
century, despite SET CENTURY ON.

Maybe that's the problem.

It can be solved by

EXPORT TO test.xls TYPE XL5

With XL5 instead of XLS

Bye, Olaf.

Reply to this Message

Jonathan Morningstar - 24 Jul 2008 17:15 GMT

The problem is the date fields from Foxpro will not sort by YYYY/MM/DD. It
will put all the May entries together, regardles of the year. I want my
data to go from Foxpro to Excel and have the ability to sort, in
chronological order, the dates. This is becoming frustrating.

Respectfully,
Jonathan Morningstar

>I don't know how you export to xls,
> but if I simply do this:
[quoted text clipped - 20 lines]
>
> Bye, Olaf.

Reply to this Message

Fred Taylor - 24 Jul 2008 17:27 GMT

The problem is your date field sounds like they're not going into Excel as
dates, but simply character strings. If that's the case, you may be better
off to convert the date to DTOS() format before you send to Excel. At least
then it would sort correctly.

Signature

Fred
Microsoft Visual FoxPro MVP

> The problem is the date fields from Foxpro will not sort by YYYY/MM/DD.
> It will put all the May entries together, regardles of the year. I want
[quoted text clipped - 28 lines]
>>
>> Bye, Olaf.

Reply to this Message

Olaf Doschke - 24 Jul 2008 18:06 GMT

Hi Jonathan,

you still don't say how you export to xls.
The impact of using XLS instead of XL5
as type are not only, that Excel asks
about how to convert that values to
19XX or 20XX, as long as you don't
do so, Excel treats these values as texts,
eg "24-Jul-08".

And of course that means the dates will
not sort correctly, excel does not interpret
these as date values although it guesses
by the options it gives me, these should be
dates.

If I don't act Excel even sorts the caption
ddate in my example excel sheet after the
"24-Jul-08" value, as "2"<"d".

If you tell me how you export your DBF
to an XLS file I may be able to help.

My suggestion is to use
EXPORT TO <filename.xls> TYPE XL5.

Bye, Olaf.

Reply to this Message

Jonathan Morningstar - 27 Jul 2008 11:27 GMT

Olaf,

I copy the data with a COPY TO XL5 command.
Using DTOS(date()) puts the date into a sortable format, it just isn't as
pretty at a mm/dd/yy format.

Respectfully,

Jonathan Morningstar

> Hi Jonathan,
>
[quoted text clipped - 23 lines]
>
> Bye, Olaf.

Reply to this Message

Olaf Doschke - 28 Jul 2008 10:00 GMT

> I copy the data with a COPY TO XL5 command.
> Using DTOS(date()) puts the date into a sortable format, it just isn't as
> pretty at a mm/dd/yy format.

Hi Jonathan,

When using EXPORT TO XL5
you don't need to convert your date
with DTOS, the resulkting excel
file will recognize dates as dates
and sort them correspondingly.

Bye, Olaf.

Reply to this Message