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!
  • Students Click Here

*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.

Students Click Here

Microsoft: FoxPro FAQ

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!



&&&&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")
&&&&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)

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

  &&Remove Commas From Within Quotes and Drop Quotes
  DO WHILE OCCURS(CHR(34),lcString)>1

  &&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

    &&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

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

  IF lCenturyOn=.F.


  && If All Went Well, Write Result Out

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

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

My Archive

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