Transform a Partially Formatted Delimited Text File Into a Standard Delimited File

CLEAR
CLOSE ALL

&&&&Sample Strings
&&&&We need to remove quotes and commas within quotes,
&&&&'illegal characters' and transform datetime
lcString1="Sam O'Donald,202525233,11/05/99 00:00 AM,12/19/02 12:00 AM,"+CHR(34)+"7,357.29"+CHR(34)+","+CHR(34)+"8,189.55"+CHR(34)+",001,-19,B,12.49,60,2,LA018,LA,51,Y,KMH013,573,HYUNDAI,"+CHR(34)+"2,002"+CHR(34)
lcString2="John/Mary Doe,20255200297623,10/05/1999 12:00 AM,12/19/01 00:00 AM,"+CHR(34)+"13,366.33"+CHR(34)+","+CHR(34)+"12,189.55"+CHR(34)+",001,-19,A,133.49,60%,2,NY018,NY,50,Y,2U354013,573,FORD,"+CHR(34)+"2,001"+CHR(34)
&&&&

&&&&Create Test Input File
lnNewFile= fcreate("Input_Sample.txt")
  fput(lnNewFile,lcString1)
  fput(lnNewFile,lcString2)
  fclose(lnNewFile)
&&&&Test Input File Done

&&&&Open Input File and Create Output File
lnhandle= fopen("Input_Sample.txt")
lnNewFile= fcreate("Output_Sample.txt")

&&&&Get Data, Transform, and Write to Output File
DO WHILE not Feof(lnHandle)
  lcString=FGETS(lnhandle,3000)

  IF MOD(OCCURS(CHR(34),lcString),2)#0
    ?"Quote Match Error! Extra Quote Cannot Resolve Line"
    ?lcString
    RETURN
  ENDIF 

  &&Remove Commas From Within Quotes and Drop Quotes
  DO WHILE OCCURS(CHR(34),lcString)>1
    nFirstQuote=ATC(CHR(34),lcString,1)
    nSecondQuote=ATC(CHR(34),lcString,2)
    lcString=LEFT(lcString,nFirstQuote-1)+;
    CHRTRAN(SUBSTR(lcString,nFirstQuote+1,nSecondQuote-nFirstQuote-1),",","")+;
    RIGHT(lcString,LEN(lcString)-nSecondQuote)
  ENDDO

  &&Remove time part of datatime fields and any'illegal' characters (tab and % here) -DateTime format may need tweaked
  lcString=STRTRAN(STRTRAN(CHRTRAN(lcString,CHR(9)+"%","")," 12:00 AM","")," 00:00 AM","")

  &&Make Date Standard Text Date Format
  DO WHILE OCCURS("/",lcString)>1
    nFirstSlash=ATC("/",lcString,1)
    nSecondSlash=ATC("/",lcString,2)

    &&Test for "/"s that are not date related and make them CHR(1) for later conversion back
    DO WHILE nSecondSlash-nFirstSlash<1 or nSecondSlash-nFirstSlash>3 OR ;
      VAL(SUBSTR(lcString,nFirstSlash+1,nSecondSlash-nFirstSlash-1))<1 or VAL(SUBSTR(lcString,nFirstSlash+1,nSecondSlash-nFirstSlash-1))>31
      lcString=LEFT(lcString,nFirstSlash-1)+CHR(1)+RIGHT(lcString,LEN(lcString)-nFirstSlash)
      nFirstSlash=ATC("/",lcString,1)
	  nSecondSlash=ATC("/",lcString,2)
    ENDDO

  lCenturyOn=IIF("," $ SUBSTR(lcString,nSecondSlash+4,4),.T.,.F.)

  IF lCenturyOn=.F.
    cYearparse="nFirstSlash+4,2"
    nRightparse=5
  ELSE
    nYearparse="nFirstSlash+4,4"
    nRightparse=7
  ENDIF 

  lcString=LEFT(lcString,nFirstSlash-3)+;
  IIF(lCenturyOn=.F.,IIF(VAL(SUBSTR(lcString,nFirstSlash+4,2))<50,"20","19"),"")+SUBSTR(lcString,&cYearparse)+;
    SUBSTR(lcString,nFirstSlash-2,2)+;
    SUBSTR(lcString,nFirstSlash+1,2)+;
    RIGHT(lcString,LEN(lcString)-(nFirstSlash+nRightparse))
  ENDDO

  && If All Went Well, Write Result Out
  fput(lnNewFile,CHRTRAN(lcString,CHR(1),"/"))
ENDDO 
FCLOSE(lnHandle)
CLOSE ALL

&&&&View Input and Output Files
MODIFY COMM Input_Sample.txt nowait
MODIFY COMM Output_Sample.txt nowait