dQuery Export of Dates to Excel

Hello.

I am having trouble getting dQuery to export date values properly to 
Excel.

In dBASE v2.61 I have the following settings for dates:
	set("date"):  USA
	set("mark"):  -   [hyphen]

So a typical date is shown in tables as "12-31-2007".  This matches 
the 'Regional Settings' in XP Control Panel which I use for short 
dates.

When I try to export a table from dQuery to Excel that date shows up 
as a text value in a cell in the new Excel worksheet.  Namely, there 
is an apostrophe preceding a text string representing the actual 
date.  For example, it will literally contain:  " '12-31-2007 ".
If you test that cell with the Excel function ISTEXT() it will return 
'true'.

Yes, I am aware that Excel will do its best to automatically convert 
on the fly when that cell is used for date math.  However, there is 
no way to reformat that cell into another date format because the 
cell value is still a text string.  And it's confusing for the end 
user of that Excel table.

I am also aware of the alternative to first use dQuery to export to 
CSV format, and then use Excel to manually import and format the 
result.  But this process seems way more work to automate just to get 
the dates to be represented properly.

How do I configure dBASE and dQuery to export dates as real date 
values rather than as text strings?  Must I take the long route, 
namely, convert the Level 7 table to Level 4 and then open the latter 
in Excel?  Yuk!?

Thank you in advance for your assistance.

-- 

In article <MPG.2206c3ac78095e0c9896cf@news.dbase.com>, 
cecil@outoutdamnspam.ca says...
> Hello.
> 
> I am having trouble getting dQuery to export date values properly to 
> Excel.
> 
> In dBASE v2.61 I have the following settings for dates:
> 	set("date"):  USA
> 	set("mark"):  -   [hyphen]
> 
> So a typical date is shown in tables as "12-31-2007".  This
matches 
> the 'Regional Settings' in XP Control Panel which I use for short 
> dates.
> 
> When I try to export a table from dQuery to Excel that date shows up 
> as a text value in a cell in the new Excel worksheet.  Namely, there 
> is an apostrophe preceding a text string representing the actual 
> date.  For example, it will literally contain:  " '12-31-2007 ".
> If you test that cell with the Excel function ISTEXT() it will return 
> 'true'.
> 
> Yes, I am aware that Excel will do its best to automatically convert 
> on the fly when that cell is used for date math.  However, there is 
> no way to reformat that cell into another date format because the 
> cell value is still a text string.  And it's confusing for the end 
> user of that Excel table.
> 
> I am also aware of the alternative to first use dQuery to export to 
> CSV format, and then use Excel to manually import and format the 
> result.  But this process seems way more work to automate just to get 
> the dates to be represented properly.
> 
> How do I configure dBASE and dQuery to export dates as real date 
> values rather than as text strings?  Must I take the long route, 
> namely, convert the Level 7 table to Level 4 and then open the latter 
> in Excel?  Yuk!?
> 
> Thank you in advance for your assistance.
> 


Cecil,

I don't have Excel. If, in Excel, you change the formatting of those 
cells from TEXT to DATE, does it work?

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

In article <MPG.220700c5213ac1c9989e81@news.dbase.com>, 
gswassREMOVE_ME@attglobal.net says...
> 
> I don't have Excel. If, in Excel, you change the formatting of those 
> cells from TEXT to DATE, does it work?
> 
------------------
Hello Geoff.

No it doesn't.  The Excel function ISTEXT() is specified designed to 
determine if a cell value is actually a text string regardless of how 
it is represented or formatted.  For the dQuery-exported dates that 
ISTEXT() function returns 'true' for all the sort-of-date-looking 
cells.

-- 

Cecil Green wrote
>
> I am having trouble getting dQuery to export date values properly to
> Excel.
>
> In dBASE v2.61 I have the following settings for dates:
> set("date"):  USA
> set("mark"):  -   [hyphen]
>
> So a typical date is shown in tables as "12-31-2007".  This
matches
> the 'Regional Settings' in XP Control Panel which I use for short
> dates.
>
> When I try to export a table from dQuery to Excel that date shows up
> as a text value in a cell in the new Excel worksheet.  Namely, there
> is an apostrophe preceding a text string representing the actual
> date.  For example, it will literally contain:  " '12-31-2007 ".
> If you test that cell with the Excel function ISTEXT() it will return
> 'true'.
---------
If dQuery does not do what you want, there are alternatives you can use 
programmatically:
- Ken Mayer's exportData.wfm in the dUFLP
- Set up an Excel ODBC DSN, then you can copy the data to Excel.

Roland 

In article <MPG.22070e6ff9e2f44a9896d0@news.dbase.com>, 
cecil@outoutdamnspam.ca says...
> In article <MPG.220700c5213ac1c9989e81@news.dbase.com>, 
> gswassREMOVE_ME@attglobal.net says...
> > 
> > I don't have Excel. If, in Excel, you change the formatting of those 
> > cells from TEXT to DATE, does it work?
> > 
> ------------------
> Hello Geoff.
> 
> No it doesn't.  The Excel function ISTEXT() is specified designed to 
> determine if a cell value is actually a text string regardless of how 
> it is represented or formatted.  For the dQuery-exported dates that 
> ISTEXT() function returns 'true' for all the sort-of-date-looking 
> cells.
> 

Cecil,

I am working from memory... but you mean to say nothing happens when you 
right-click on the cell, go to Cell Formatting... and change the cell 
from "text" to "Date"? I thought that was all it took.

Alternatively, perhaps you could make a macro which strips the leading 
' from the string so that it becomes a date (or your dBASE program could 
do that if you are already opening the document via OLEAUTOCLIENT).

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.