INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Utility Program

Transform a Partially Formatted Delimited Text File Into a Standard Delimited File by baltman
Posted: 7 Oct 03 (Edited 24 Jan 07)

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&& This program takes a partial quote and fully comma delimited file
&&&& with some embedded formatting and ouputs a 'clean' comma delimited
&&&& text file that FoxPro can easily inport as TYPE DELIM
&&&&
&&&& Brian Altman
&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
FoxPro programming rocks!

CODE

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

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close