How to read 31-Mar-2006 4

[mikeopolo]

I have a csv file which includes dates in the above format. How should I read/convert it into a date field?

Regards
Mike

 

[ramani]

My star to you MikeYearwood. Interesting discussion.

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[baltman]

Ok Mike this isn't fully tested but is what I was refering to as being possible.

Brian

CLEAR 
CLOSE DATABASES ALL
SET DEFAULT TO JUSTPATH(SYS(16))

IF FILE("test.dbf")
  DROP TABLE test
ENDIF 

&& Issue replace all when destination table doesn't exist
&& returns single value
?covert_DD_MMM_YYYY_to_date("31-MAR-2006", "DATE2", "ALL")

create_test_data()

&& Issue simple call from field 
GO 10
?covert_DD_MMM_YYYY_to_date("DATE1") &&returns result

&& Issue replace ALL from string when destination table does exist
?covert_DD_MMM_YYYY_to_date("31-MAR-2006", "DATE2", "ALL")

&& Issue replace ALL from field when destination table does exist
?covert_DD_MMM_YYYY_to_date("DATE1", "DATE3", "ALL")

&& Issue replace ONE from field when destination table does exist 
&& but isn't current workspace
GO 10
SELECT decoy
?covert_DD_MMM_YYYY_to_date("TEST.DATE1", "TEST.DATE4", "ONE")

&& Issue replace ONE from other workspace when destination table
&& does exist but isn't current workspace
GO 11 IN test
?covert_DD_MMM_YYYY_to_date("DECOY.FLD1", "TEST.DATE4", "ONE")

&&our test
SELECT test
nSec=SECONDS()
FOR nCnt = 1 TO 5 
  covert_DD_MMM_YYYY_to_date("DATE1", "DATE3", "ALL")
ENDFOR
?"5 Iteration of 30,000 records in " + TRANSFORM(SECONDS()-nSec)

GO 10
BROWSE NOWAIT 
DROP TABLE decoy
RETURN 

PROCEDURE covert_DD_MMM_YYYY_to_date
LPARAMETERS tcString, tcDestinationField, tcCallType
  LOCAL cCmd, cResult, nDtCnt, cSourceAlias, cDestinationAlias 
  LOCAL tcDestinationStem, tcSourceStem
  LOCAL ltcSourceFieldAvailable, lDestinationFieldAvailable 
  LOCAL ARRAY aa1[1]
  LOCAL ARRAY aa2[1]
  LOCAL ARRAY aCMonth[12,2]

  FOR m.nDtCnt= 1 TO 12
    m.aCMonth[m.nDtCnt,1]=UPPER(LEFT(CMONTH(CTOD(TRANSFORM(m.nDtCnt)+'/1/1900')),3))
    m.aCMonth[m.nDtCnt,2]=TRANSFORM(m.nDtCnt)
  ENDFOR

  &&test for source field/string in tcString
  IF AT(".",m.tcString)>0
    m.cSourceAlias = GETWORDNUM(m.tcString, 1, ".")
    m.tcSourceStem = GETWORDNUM(m.tcString, 2, ".")
    AFIELDS(m.aa1, m.cSourceAlias)
  ELSE
    m.tcSourceStem = m.tcString
    IF !EMPTY(DBF()) &&if no alias, test current DBF if there is one
      AFIELDS(m.aa1)
    ENDIF
  ENDIF 

  IF ASCAN(m.aa1, UPPER(m.tcSourceStem), 1, ALEN(m.aa1,1), 1, 2 + 4) > 0
    m.ltcSourceFieldAvailable = .t.
  ELSE
    m.ltcSourceFieldAvailable = .f.
  ENDIF  

  &&string passed, add quotes if not a field reference
  IF m.ltcSourceFieldAvailable= .f. AND OCCURS("-", m.tcString)=2 
    m.tcString="'"+m.tcString+"'"
  ENDIF

  &&test for destination field
  IF VARTYPE(m.tcDestinationField)<>"L" AND AT(".",m.tcDestinationField)>0
    m.cDestinationAlias = GETWORDNUM(m.tcDestinationField, 1, ".")
    m.tcDestinationStem = GETWORDNUM(m.tcDestinationField, 2, ".")
    AFIELDS(m.aa2,m.cDestinationAlias)
  ELSE
    m.tcDestinationStem = m.tcDestinationField
    IF !EMPTY(DBF()) &&if no alias, test current DBF if there is one
      AFIELDS(m.aa2)
    ENDIF 
  ENDIF 

  IF VARTYPE(m.tcDestinationField)<>"L" AND ;
  ASCAN(m.aa2, UPPER(m.tcDestinationStem), 1, ALEN(m.aa2,1), 1, 2 + 4) > 0
    m.lDestinationFieldAvailable = .t.
  ELSE
    m.lDestinationFieldAvailable = .f.
  ENDIF  

  m.cCmd = "m.aCMonth[1+ASCAN(m.aCMonth,GETWORDNUM("+m.tcString+", 2, '-'),1,12,1,2+4)]+"
  m.cCmd = m.cCmd + "'/'+GETWORDNUM("+m.tcString+", 1, '-')+'/'+RIGHT(ALLTRIM("+m.tcString+"),4)"	
  m.cCmd = m.cCmd + IIF(VARTYPE(m.cDestinationAlias)="C"," in "+m.cDestinationAlias,"")
  
  DO CASE 
    CASE VARTYPE(m.tcDestinationField)="C" AND m.tcCallType="ALL" AND m.lDestinationFieldAvailable 
      REPLACE ALL (m.tcDestinationField) WITH &cCmd 
      RETURN .t.
	  
    CASE VARTYPE(m.tcDestinationField)="C" AND m.tcCallType="ONE" AND m.lDestinationFieldAvailable 
      REPLACE (m.tcDestinationField) WITH &cCmd 
      RETURN .t.
	  
    OTHERWISE 
      RETURN &cCmd
  ENDCASE 
ENDPROC


PROCEDURE create_test_data
  CREATE TABLE decoy (fld1 c(12))
  INSERT INTO decoy VALUES ("31-JUN-1944")
  CREATE TABLE test (txt c(20), date1 c(12),date2 c(12), date3 c(12), date4 c(12))
    FOR xx = 1 TO 30000
      INSERT INTO test VALUES ;
        (REPLICATE(CHR(65+MOD(m.xx,65)),18),;
        TRANSFORM(MAX(1,ROUND(28*RAND(),0)))+"-"+;
        UPPER(LEFT(CMONTH(CTOD(TRANSFORM(MAX(1,MOD(xx,12)))+"/1/1900")),3))+;
        "-"+TRANSFORM(YEAR(DATE())-(ROUND(RAND()*5,0))),"","","")
	ENDFOR
ENDPROC