Anyone know of a good datetime importing func or program?

I'm dealing with many different date and time, or datetime combined fields
coming from many different file structures, each time I have a new format I
have to jump through hoops to get the files imported into a foxpro datetime
field, for example:

20030610011154
Mon Jun 02 20:48:42 2003
2003-06-09 00:00:01.000
23:04:59.575 UTC Sat Jan 8 2003

some are easy, some are a nightmare, sometimes the length is fixed:
eg. 01/01/2003 01:01:23pm

sometimes it changes:
eg. 1/1/2003 1:1:3pm vs 11/11/2003 11:11:23pm in the same field(this was a
complete nightmare of embedded if statements)

I was just wondering if anyone knows of a function or program available to
simplify this part of importing into VFP, with many choices for common
datetime formats to choose from?

Anthony
Tue Jul 01 07:26:48 CDT 2003

"Steve" <steveinstl@aol.com> wrote:

>I was just wondering if anyone knows of a function or program available to
>simplify this part of importing into VFP, with many choices for common
>datetime formats to choose from?

Well, I or somebody else, will happily write it for you if you provide a list of
all the formats you are receiving. What are you willing to pay?


anthony shipley
-
Military Intelligence..... still an oxymoron!

Leemi
Tue Jul 01 09:46:33 CDT 2003

Hi Steve:

Have you looked in the download section of www.universalthread.com? They
often have download example files that handle many problems like this.

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retires June 30th, 2003
- VFP6 Mainstream Support retires Sept. 30th, 2003


>I was just wondering if anyone knows of a function or program available to
>simplify this part of importing into VFP, with many choices for common
>datetime formats to choose from?

Jeroen
Tue Jul 01 17:47:23 CDT 2003

For most of the formats you could something like the following program
(under your tekst):

>I'm dealing with many different date and time, or datetime combined fields
>coming from many different file structures, each time I have a new format I
>have to jump through hoops to get the files imported into a foxpro datetime
>field, for example:
>
>20030610011154
>Mon Jun 02 20:48:42 2003
>2003-06-09 00:00:01.000
>23:04:59.575 UTC Sat Jan 8 2003
>
>some are easy, some are a nightmare, sometimes the length is fixed:
>eg. 01/01/2003 01:01:23pm
>
> sometimes it changes:
>eg. 1/1/2003 1:1:3pm vs 11/11/2003 11:11:23pm in the same field(this was a
>complete nightmare of embedded if statements)
>
>I was just wondering if anyone knows of a function or program available to
>simplify this part of importing into VFP, with many choices for common
>datetime formats to choose from?
>

PROCEDURE getdatetime
* convert date from text to datetime
* (work in progress...)

PARAMETERS pcLayout,pcString

* pcLayout contains the basic layout in letters
* c century (only necesary if seperated from year)
* y year
* m month
* d day
* h hour
* n miNute
* s second
* u millisecond
* p check PM/AM
* x ignore (or any other undefined char)

* examples
* xmdhnsy Mon Jun 02 20:48:42 2003
* ymdhnsu 2003-06-09 00:00:01.000
* hnsuxxmdy 23:04:59.575 UTC Sat Jan 8 2003
* dmyhnsp 1/1/2003 1:1:3pm
* dmyhnsp 11/11/2003 11:11:23pm
#DEFINE seperators " /-:."

local lnYear,lnMonth,lnDay,lnHour,lnMinute,lnSecond,lnMilli
local llCheckPM
local lcWord

*cleanup pcLayout
pcLayout=CHRTRAN(LOWER(pcLayout),' '+seperators,'') && remove
unnecesary characters
IF 'p'$pcLayout
llCheckPM=.t.
pclayout=CHRTRAN(pcLayout,'p','') && remove from layout
ENDIF &&llCheckPM defaults to .f. in the declaration

lnYear=VAL(GETWORDNUM(pcString,AT('y',pcLayout),seperators))+;
VAL(GETWORDNUM(pcString,AT('y',pcLayout),seperators))*100

*todo: change to allow month in characters....
lnMonth=VAL(GETWORDNUM(pcString,AT('m',pcLayout),seperators))

lnDay=VAL(GETWORDNUM(pcString,AT('d',pcLayout),seperators))
lnHour=VAL(GETWORDNUM(pcString,AT('h',pcLayout),seperators))
IF llcheckPM AND 'pm'$LOWER(pcString)
lnHour=lnHour+12
endif
lnMinute=VAL(GETWORDNUM(pcString,AT('n',pcLayout),seperators))
lnSecond=VAL(GETWORDNUM(pcString,AT('s',pcLayout),seperators))
lnMilli=VAL(GETWORDNUM(pcString,AT('u',pcLayout),seperators))

*todo: do some sanety checking because datetime() will error out on
invalid values
RETURN
DATETIME(lnYear,lnMonth,lnDay,lnHour,lnMinute,lnSecond)+lnMilli/1000

Anders
Tue Jul 01 18:18:25 CDT 2003

VFP will import dates like
1/1/2003 1:1:3pm
11/11/2003 11:11:23pm
from a textfile just fine
CREATE CURSOR XXX (dt T)
SET HOURS TO 12
SET DATE MDY && DMY perhaps
APPEND FROM Dates.txt DELIMITED

For dates like
2003-06-09 00:00:01.000
SET DATE YMD
SET HOURS TO 24
APPEND FROM Dates.txt DELIMITED

For dates like 20030610011154 you can do a straight APPEND FROM texrfile
DELIMITED.
VFP (at least VFP8) will handle it automatically regardless of your SET
DATE, YMD, MDY or DMY. and SET HOUR.

For dates like
x ="23:04:59.575 UTC Sat Jan 8 2003"
you can fill a memo with the textfile (APPEND MEMO x FROM textfile) or a
string (ztring=FILETOSTR('txtgile) and the use MEMLINES and MLINE to scan
the text in the string or the memo line by line:
and parse the string
x ="23:04:59.575 UTC Sat Jan 8 2003"
jan = 1
feb = 2
mar=3
...

nyear = VAL(GETWORDNUM (x,6))
nmonth = EVALUATE(GETWORDNUM(x,4))
nday = VAL(GETWORDNUM(x,5))
y=GETWORDNUM(x,1)
nhour=VAL(GETWORDNUM (y,1,':'))
nminutes=VAL(GETWORDNUM (y,2,':'))
nseconds=VAL(GETWORDNUM (y,3,':'))
ldDatetime= DATETIME(nyear, nmonth, nday, nhour,nminutes,nseconds)

-Anders



"Steve" <steveinstl@aol.com> wrote in message
news:uAJv7a5PDHA.3192@TK2MSFTNGP10.phx.gbl...
> I'm dealing with many different date and time, or datetime combined fields
> coming from many different file structures, each time I have a new format
I
> have to jump through hoops to get the files imported into a foxpro
datetime
> field, for example:
>
> 20030610011154
> Mon Jun 02 20:48:42 2003
> 2003-06-09 00:00:01.000
> 23:04:59.575 UTC Sat Jan 8 2003
>
> some are easy, some are a nightmare, sometimes the length is fixed:
> eg. 01/01/2003 01:01:23pm
>
> sometimes it changes:
> eg. 1/1/2003 1:1:3pm vs 11/11/2003 11:11:23pm in the same field(this was
a
> complete nightmare of embedded if statements)
>
> I was just wondering if anyone knows of a function or program available to
> simplify this part of importing into VFP, with many choices for common
> datetime formats to choose from?
>
>

Jeroen
Tue Jul 01 18:32:14 CDT 2003

Small error in the year calculation..
added monthnames
added 2digit year conversion
added errorhandling
todo? timezones?

function getDateTime
* convert date from text to datetime

PARAMETERS pcLayout,pcString
* pcLayout contains the basic layout in letters
* c century (only if seperated from year)
* y year
* m month
* d day
* h hour
* n miNute
* s second
* u millisecond
* p check PM/AM
* x ignore (or any other undefined char)

* examples
* xmdhnsy Mon Jun 02 20:48:42 2003
* ymdhnsu 2003-06-09 00:00:01.000
* hnsuxxmdy 23:04:59.575 UTC Sat Jan 8 2003
* dmyhnsp 1/1/2003 1:1:3pm
* dmyhnsp 11/11/2003 11:11:23pm
#DEFINE seperators " /-:."

local lnYear,lnMonth,lnDay,lnHour,lnMinute,lnSecond,lnMilli
local llCheckPM,lcWord,ltResult,lcOnError

*cleanup pcLayout
pcLayout=CHRTRAN(LOWER(pcLayout),' '+seperators,'') && remove
unnecesary characters
IF 'p'$pcLayout
llCheckPM=.t.
pclayout=CHRTRAN(pcLayout,'p','') && remove from layout
ENDIF &&llCheckPM defaults to .f. in the declaration

lnYear=VAL(GETWORDNUM(pcString,AT('y',pcLayout),seperators))+;
VAL(GETWORDNUM(pcString,AT('c',pcLayout),seperators))*100
DO CASE
CASE lnYear<20 && rollover year
lnYear=lnYear+2000
CASE lnYear<100
lnYear=lnYear+1900
ENDCASE

lcWord=GETWORDNUM(pcString,AT('m',pcLayout),seperators)
IF VAL(lcWord)>0
lnMonth=VAL(lcWord)
ELSE
* get month number from monthnames
lcWord=LOWER(LEFT(lcWord,3))
DO case
CASE INLIST(lcWord,'jan')
lnMonth=1
CASE INLIST(lcWord,'feb')
lnMonth=2
CASE INLIST(lcWord,'mar','maa','mrt')
lnMonth=3
CASE INLIST(lcWord,'apr')
lnMonth=4
CASE INLIST(lcWord,'may','mei')
lnMonth=5
CASE INLIST(lcWord,'jun')
lnMonth=6
CASE INLIST(lcWord,'jul')
lnMonth=7
CASE INLIST(lcWord,'aug')
lnMonth=8
CASE INLIST(lcWord,'sep')
lnMonth=9
CASE INLIST(lcWord,'oct','okt')
lnMonth=10
CASE INLIST(lcWord,'nov')
lnMonth=11
CASE INLIST(lcWord,'dec')
lnMonth=12
OTHERWISE
lnMonth=0
ENDCASE
ENDIF
lnDay=VAL(GETWORDNUM(pcString,AT('d',pcLayout),seperators))
lnHour=VAL(GETWORDNUM(pcString,AT('h',pcLayout),seperators))
IF llcheckPM AND 'pm'$LOWER(pcString)
lnHour=lnHour+12
endif
lnMinute=VAL(GETWORDNUM(pcString,AT('n',pcLayout),seperators))
lnSecond=VAL(GETWORDNUM(pcString,AT('s',pcLayout),seperators))
lnMilli=VAL(GETWORDNUM(pcString,AT('u',pcLayout),seperators))

lcOnError=ON('error') && remember errorhandler
ON ERROR ltResult={/:} && if error, then return empty string
ltResult=DATETIME(lnYear,lnMonth,lnDay,lnHour,lnMinute,lnSecond)+lnMilli/1000
ON ERROR &lcOnError

RETURN ltResult

Anthony
Tue Jul 01 21:48:16 CDT 2003

Jeroen van Kalken <I@dont.like.spam> wrote:

>function getDateTime
>* convert date from text to datetime
>
>PARAMETERS pcLayout,pcString
>* pcLayout contains the basic layout in letters
>* c century (only if seperated from year)
>* y year
>* m month
>* d day
>* h hour
>* n miNute
>* s second
>* u millisecond
>* p check PM/AM
>* x ignore (or any other undefined char)

Very nice analysis and solution, Jeroen.

At first it seemed you matched a format character to each date component and
ignored all non-numeric characters except for special ones such as UTC and
month/day names.

I think you missed 20030610011154, however.
* hnsuxxmdy 23:04:59.575 UTC Sat Jan 8 2003

anthony shipley
-
Military Intelligence..... still an oxymoron!

Steve
Thu Jul 03 17:54:29 CDT 2003

Wow, hadn't looked at this post for a couple days, thanks a lot for all the
help especially Jeroen and Anders, can't believe you put all that work into
this. It is very very much appreciated.

Steve

Willianto
Fri Jul 04 03:08:50 CDT 2003

Hi Steve,

> Wow, hadn't looked at this post for a couple days, thanks a lot for all
the
> help especially Jeroen and Anders, can't believe you put all that work
into
> this.

Welcome to VFP community :-)

If you often post messages and forgot to follow up them, following is
Cindy's idea to that problem (assuming you use Outlook):

> To find answers to your posts make a mail rule like the following:
>
> Apply this rule after the message arrives
> Where the From line contains 'Cindy Winegarden'
> Highlight it with Purple
> and Mark it as read
> and Mark the message as watched
>
> You can easily see them by viewing watched messages (View | Current view |
> Show watched messages).

hth,
Willianto