How To Append Records from Any Delimited File with Memo Data

This article was previously published under Q140294
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
This article shows by example how to append records from any delimitedASCII text file into a predefined FoxPro table or .dbf file.

The intent of the program listed in this article is twofold. The first isto address the inability of the APPEND command to read data into a memofield. The second is to provide a wider range of support for source fileformats that the APPEND command does not support.
MORE INFORMATION
Here's an excerpt from the Visual FoxPro APPEND FROM Help Topic:
"A delimited file is an ASCII text file in which each record ends with acarriage return and linefeed. Field contents are by default assumed tobe separated from each other by commas, and characterfield values to be additionally delimited by double quotation marks."
The program listed in this article does not require linefeeds after thecarriage return, nor does it require that character data be delimited bydouble quotation marks. Also, files do not have to be terminated with anend-of-file marker (^Z), but the program will work fine if the end-of-filemarker is present. This flexibility is necessary because many applicationsdo not export their data in a format suitable for FoxPro's APPEND FROMcommand.

Numeric, Float, Double, Integer, Currency, Date, and Memo fields fromdelimited files can be imported if the data is in proper format. The dateformat defaults to mm/dd/yy. Including the century portion of a date isoptional. FoxPro will import a date, such as 12/25/95, that doesn't includethe century assuming the date is in the twentieth century.

Date delimiters can be any non-numeric character except the delimiter thatseparates the fields in the delimited file. Dates in other formats can beimported if their format matches a date format available in SET DATE. Toimport dates that are not in the default format, issue SET DATE with theproper date format before using the program listed in this article(Appendm.prg). To test if a date format can be successfully imported, useit with CTOD(). If the date is acceptable to CTOD(), the date will importproperly.

Character strings with a length in excess of 10,000 characters have beentested and work fine when imported into a memo field.

NOTE: Do not use a delimiting character that appears in the actual data.

Sample Code

     Appendm.prg         Purpose:           * Append records from any delimited ASCII text file into a       * predefined FoxPro table or .dbf file. The intent of this program       * is twofold. The first is to address the inability of the       * APPEND command to read in data into a memo field. The second       * is to provide a wider range of support for source file formats.         Syntax:           DO AppendM [WITH <expC1>, <expC2&>[, <expC3>[, <expC4>]]]         Parameters:          <expC1>         *   Specify the full name of the ASCII text file to append from     *   with extension. If this parameter is used, then <expC2> and     *   optionally <expC3> must be specified also. If this     *   parameter is not specified, the user will be prompted for the     *   file.          <expC2>         *  Specify the field-delimiting character. This parameter must     *  be used if <expC1> is specified. If <expC1> is not      *  specified, this parameter is ignored.                    Samples:    ','    = COMMA                     CHR(9) = TAB       <expC3>        *  Specify the character field delimiting character. Use the    *  null string ("") for none. This parameter is optional if    *  <expC1> is specified. If <expC1> is not specified, this    *  parameter is ignored.          <expC4>        *  Specify the name of the table or .dbf file to append to. If the    *  extension is not specified, .dbf is assumed. If a table or .dbf    *  file is open in the current work area, this parameter will be    *  ignored and the open table will be used.         Examples:        *  For full prompting:              DO AppendM        *  For a source file with comma-delimited fields and double-quotation-    *  mark delimited character fields:              DO AppendM WITH 'SRCFILE.TXT', ',', '"'        *  For a source file with tab-delimited fields and no character    *  field delimiter:              DO AppendM WITH 'SRCFILE.TXT', CHR(9)        *  For a source file with comma-delimited fields and double-quotation-    *  mark delimited character fields with the current work area unused:              DO AppendM WITH 'SRCFILE.TXT', ',', '"', 'DESTFILE.DBF'         Notes:        *  Before using this program, ensure that the target table or    * .dbf file is structured to accept the data from the source    *  file, and ensure that the source file is in ASCII file format.        *  Each record in the source file must be terminated with a    *  carriage return (CR). Linefeeds (LF) following a CR are    *  also supported. The program will address text files with    *  any field delimiters. Character data fields may optionally be    *  delimited. An end-of-file marker (^Z) is supported but not    *  required.                                                                             PARAMETERS cTextFile, cFieldDelim, cChrFldDelim, cTableFile                                               IF TYPE( "cTextFile" ) = 'L'                                                        cTextFile = ''                                                      ENDIF   IF TYPE( "cFieldDelim" ) = 'L'                                                        cFieldDelim = ''                                                       ENDIF   IF TYPE( "cChrFldDelim" ) = 'L'                                                        cChrFldDelim = ''                                                       ENDIF   IF TYPE( "cTableFile" ) = 'L'                                                        cTableFile = ''                                                       ENDIF                                                    *  Attempt to get and/or open the table or .dbf file    *  If successful, continue; otherwise, Return       IF USED()                                                       cDBF_File = ALIAS()                                                       ELSE                                                        IF EMPTY( cTableFile )         cDBF_File = GETFILE( 'DBF', 'Append to:')      ELSE         IF !FILE( cTableFile ) AND !FILE( cTableFile + '.DBF' )            cDBF_File = GETFILE( 'DBF', 'Append to:')         ELSE            cDBF_File = cTableFile         ENDIF      ENDIF      IF !EMPTY( cDBF_File )         USE (cDBF_File)      ELSE         ?? CHR(7)         WAIT WINDOW "No Table/DBF Selected to Append To!"         RETURN      ENDIF                                                       ENDIF                                                    *  Attempt to get ASCII text file    *  If successful, continue; otherwise, Return       IF EMPTY( cTextFile )                                                        cTXT_File = GETFILE( 'TXT', 'Source:')                                                       ELSE                                                        IF !FILE( cTextFile )         cTXT_File = GETFILE( 'TXT', 'Source:')      ELSE         cTXT_File = cTextFile      ENDIF                                                       ENDIF   IF EMPTY( cTXT_File )                                                       ?? CHR(7)      WAIT WINDOW "No Text File Selected to Append From!"      RETURN                                                       ENDIF                              *  Verify Delimiting Characters. If successful,    *  continue; otherwise, Return       IF EMPTY( cTextFile )                                                        DEFINE WINDOW wGetDelims ;         FROM 1,1 TO 15,60 ;         TITLE "Delimiters" ;         NOCLOSE NOFLOAT NOGROW NOMDI NOMINIMIZE NOSHADOW NOZOOM      MOVE WINDOW wGetDelims CENTER      ACTIVATE WINDOW wGetDelims NOSHOW      CLEAR      @3,10 SAY "Press the Field Delimiting Character"      @5,10 SAY "ie.. COMMA = <,> or TAB = <TAB KEY>"      @7,29 say "" COLOR I      ACTIVATE WINDOW wGetDelims      nFieldDelim = INKEY(0,'S')      IF nFieldDelim = 9 or nFieldDelim > 32 and nFieldDelim < 127         cFieldDelim = CHR( nFieldDelim )      ENDIF      @3,5 SAY "Press the Character Field Delimiting Character"      @5,6 SAY "Typically DOUBLE-QUOTE, Press ENTER for None"      @7,29 say "" COLOR I      nChrFldDelim = INKEY(0,'S')      CLEAR      IF nChrFldDelim > 32 and nChrFldDelim < 127         cChrFldDelim =CHR( nChrFldDelim )         ELSE           cChrFldDelim = ""    && No delimiter      ENDIF      DEACTIVATE WINDOW wGetDelims NOSHOW      RELEASE WINDOW wGetDelims                                                       ENDIF                                             IF LEN( cFieldDelim ) = 0                                                    ?? CHR(7)      WAIT WINDOW "No Field Delimiting character Specified!"      RETURN                                                       ENDIF                                                *  Attempt to open text file with low-level handle.    *  If the test is successful, continue; otherwise,    *  report reason for failure.       nFileHandle = FOPEN( cTXT_File )   IF nFileHandle < 0                                                        DO ErrHand WITH FERROR(), cTXT_File      RETURN .F.                                                       ENDIF        *  Create an array of database structure.    *  Determine number of records in array for    *  loop counting.       =AFIELDS( aStructure )   nRows = ALEN( aStructure, 1 )                                                   *  Begin DO WHILE loop and continue until end of    *  file marker is reached in text file.                                                nFileSize = FSEEK( nFileHandle, 0, 2 )   = FSEEK( nFileHandle, 0, 0 )   nBytesRead = 0   RecsApnd = 0   lChrFldDelimFlag = LEN(cChrFldDelim ) # 0   WAIT WINDOW ' Records Appended: ' + LTRIM( STR( RecsApnd )) + ;                                                       CHR(13) + ' Percent Completed: ' + ;     LTRIM( STR( nBytesRead/nFileSize 100)) NOWAIT                                                       DO WHILE !FEOF( nFileHandle )                                                        APPEND BLANK      FOR i = 1 TO nRows         cFieldData = ReadField( cFieldDelim, cChrFldDelim )         DO CASE            CASE aStructure[i,2] $ "NY"              REPLACE &aStructure[i,1] WITH VAL( cFieldData )            CASE aStructure[i,2] = "D"               REPLACE &aStructure[i,1] WITH CTOD( cFieldData )             OTHERWISE               REPLACE &aStructure[i,1] WITH cFieldData         ENDCASE           WAIT WINDOW ' Records Appended: ' + LTRIM( STR( RecsApnd )) + ;           CHR(13) + ' Percent Completed: ' +;           LTRIM( STR( nBytesRead/nFileSize 100 )) NOWAIT      ENDFOR      RecsApnd = RecsApnd + 1                                                       ENDDO   =FCLOSE( nFileHandle )   WAIT WINDOW ' Records Appended: ' + LTRIM( STR( RecsApnd )) + ;                                                       CHR(13) + ' Percent Completed: ' + ;     LTRIM( STR( nBytesRead/nFileSize 100)) NOWAIT                                              RETURN                         FUNCTION ReadField   PARAMETER cFieldDelim, cChrFldDelim   cAccumText = ""   lFldDelimFlag = .F.   DO WHILE !lFldDelimFlag AND !FEOF( nFileHandle )      cSingle = FREAD( nFileHandle, 1 )      nBytesRead = nBytesRead + 1      IF cSingle = cFieldDelim OR cSingle = CHR(13) OR ;         IIF( lChrFldDelimFlag, cSingle = cChrFldDelim, .F. )                                     IF cSingle = CHR(13)            cSingle = FREAD( nFileHandle, 1 )            nBytesRead = nBytesRead + 1            IF cSingle = CHR(10)               cSingle = FREAD( nFileHandle, 1 )               nBytesRead = nBytesRead + 1               IF !(cSingle = CHR(26) OR ASC(cSingle) = 0)                  =FSEEK( nFileHandle, -1, 1 )                  nBytesRead = nBytesRead - 1               ENDIF            ELSE               IF !(cSingle = CHR(26) OR ASC(cSingle) = 0)                 =FSEEK( nFileHandle, -1, 1 )                 nBytesRead = nBytesRead - 1               ENDIF            ENDIF            lFldDelimFlag = .T.         ENDIF         IF cSingle = cFieldDelim            lFldDelimFlag = .T.         ENDIF         cSingle = ""      ELSE         cAccumText = cAccumText + cSingle      ENDIF                                               ENDDO   RETURN cAccumText                           PROCEDURE ErrHand   PARAMETERS nError, cFileName   DO CASE   CASE nError = 2                                                       cReason = "File not found: " + cFileName                                                       CASE nError = 4                                                        cReason = "Unable to open ' + cFileName + '. Too many files open"                         CASE nError = 5                                                        cReason = "File access denied: " + cFileName                                                       CASE nError = 6                                                        cReason = "Error: Invalid file handle given"                                                       CASE nError = 8                                                        cReason = "Error: Out of memory"                                                       CASE nError = 25                            cReason = "Seek error (can't seek before start of file)"                                                      CASE nError = 29                            cReason = "Error: Disk is full"                                                       CASE nError = 31                                                        cReason = "Error opening file: " + cFileName                                                       ENDCASE   ?? CHR(7)   WAIT WINDOW cReason   RETURN                                                EOF Appendm.prg				
Properties

Article ID: 140294 - Last Review: 12/04/2015 12:28:30 - Revision: 2.3

Microsoft Visual FoxPro 3.0 Standard Edition, Microsoft Visual FoxPro 6.0 Professional Edition, Microsoft FoxPro 2.5b, Microsoft FoxPro 2.5a, Microsoft FoxPro 2.5b, Microsoft FoxPro 2.6 Standard Edition, Microsoft FoxPro 2.6a Standard Edition, Microsoft FoxPro 2.5b for MS-DOS, Microsoft FoxPro 2.5a, Microsoft FoxPro 2.5b for MS-DOS, Microsoft FoxPro 2.6 for MS-DOS, Microsoft FoxPro 2.6a Standard Edition, Microsoft FoxPro 2.5b for Macintosh, Microsoft Visual FoxPro 2.5c for Macintosh, Microsoft FoxPro 2.6a Professional Edition for Macintosh

  • kbnosurvey kbarchive kbhowto kbcode KB140294
Feedback