ASCII File Process

 

The ASCII File Process is a powerful command that extracts data from text files, one line at a time. The values of each record are saved to variables for later use, such as inserting the extracted values into other applications. This is an efficient method for transferring data from one file to another.

 

Cautionary Note: Do not manually edit or have another program edit the file during the processing, as this may cause unpredictable results on the computer. If you need to edit or update the file while the ASCII File Begin Process command is running, then have the macro make a copy of the file and process the file copy.

 

 

 

Script Editor > Expand Files or Folders Category > ASCII File Process Command

 

 

Filename

Enter the name of the file to be opened and processed in this field. Click on the Browse button to locate the file and path on the computer.

 

 

File Format

The ASCII File Process processes three commonly used  Delimited Text file formats. Most spreadsheet and database programs can export information in at least one of these file formats.

 

Comma Delimited Text

In a Comma Delimited Text file every field in each record is separated by a comma. Any field that contains a comma must be surrounded by a double quote (").

 

Comma Delimited Text file sample

 

84031,UT,HANNA,DUCHESNE,435,UTC -7:00,Y

84032,UT,HEBER CITY,WASATCH,435,UTC -7:00,Y

84033,UT,HENEFER,SUMMIT,435,UTC -7:00,Y

84034,UT,IBAPAH,TOOELE,435,UTC -7:00,Y

84035,UT,JENSEN,UINTAH,435,UTC -7:00,Y

84036,UT,KAMAS,SUMMIT,435,UTC -7:00,Y

84037,UT,KAYSVILLE,DAVIS,801/385,UTC -7:00,Y

84037,UT,FRUIT HEIGHTS,DAVIS,801/385,UTC -7:00,Y

84038,UT,LAKETOWN,RICH,435,UTC -7:00,Y

84039,UT,LAPOINT,UINTAH,435,UTC -7:00,Y

84040,UT,LAYTON,DAVIS,801/385,UTC -7:00,Y

 

           

Tab Delimited Text

In a Tab Delimited Text file every field in each record is separated by a Tab character. For visual effect, <tab> is displayed in the example to represent where a Tab character should be placed.

 

Tab Delimited Text file sample

 

84031<tab>UT<tab>HANNA<tab>DUCHESNE<tab>435<tab>UTC -7:00<tab>Y

84032<tab>UT<tab>HEBER CITY<tab>WASATCH<tab>435<tab>UTC -7:00<tab>Y

84033<tab>UT<tab>HENEFER<tab>SUMMIT<tab>435<tab>UTC -7:00<tab>Y

84034<tab>UT<tab>IBAPAH<tab>TOOELE<tab>435<tab>UTC -7:00<tab>Y

84035<tab>UT<tab>JENSEN<tab>UINTAH<tab>435<tab>UTC -7:00<tab>Y

84036<tab>UT<tab>KAMAS<tab>SUMMIT<tab>435<tab>UTC -7:00<tab>Y

84037<tab>UT<tab>KAYSVILLE<tab>DAVIS<tab>801/385<tab>UTC -7:00<tab>Y

84037<tab>UT<tab>FRUIT HEIGHTS<tab>DAVIS<tab>801/385<tab>UTC -7:00<tab>Y

84038<tab>UT<tab>LAKETOWN<tab>RICH<tab>435<tab>UTC -7:00<tab>Y

84039<tab>UT<tab>LAPOINT<tab>UINTAH<tab>435<tab>UTC -7:00<tab>Y

84040<tab>UT<tab>LAYTON<tab>DAVIS<tab>801/385<tab>UTC -7:00<tab>Y

 

 

ASCII Delimited Text

In an ASCII Delimited Text file every field in each record is surrounded by double quote marks (") and separated by a comma. If the text contains a double quote it must use two double quotes in a row or it will be interpreted as the end of the field.

 

ASCII Delimited Text file sample

 

"84031","UT","HANNA","DUCHESNE","435","UTC -7:00","Y"

"84032","UT","HEBER CITY","WASATCH","435","UTC -7:00","Y"

"84033","UT","HENEFER","SUMMIT","435","UTC -7:00","Y"

"84034","UT","IBAPAH","TOOELE","435","UTC -7:00","Y"

"84035","UT","JENSEN","UINTAH","435","UTC -7:00","Y"

"84036","UT","KAMAS","SUMMIT","435","UTC -7:00","Y"

"84037","UT","KAYSVILLE","DAVIS","801/385","UTC -7:00","Y"

"84037","UT","FRUIT HEIGHTS","DAVIS","801/385","UTC -7:00","Y"

"84038","UT","LAKETOWN","RICH","435","UTC -7:00","Y"

"84039","UT","LAPOINT","UINTAH","435","UTC -7:00","Y"

"84040","UT","LAYTON","DAVIS","801/385","UTC -7:00","Y"

 

Each field in a record (a record is a line of the text file) is loaded into a variable which can be used throughout the macro. Optionally specify from which line to start in the text file and to either process all the records from that point or just a specific number of records. Use of the variables and processing will take place between the command to open the file and the command to end the file processing.

 

Example

This example uses any one of the example data files shown above and the following macro:

 

ASCII File Begin Process: "test.txt" (Tab Delimited Text )

      Text Type (Simulate Keystrokes): %T[1]%

         %T[2]%

         %T[3]%

         %T[4]%

         %T[5]%

         %T[6]%

         %T[7]%

ASCII File End Process

 

For this example, T is entered in the field labeled "Variable Array to Receive Result" in the ASCII File Begin Process command. The value of 1 is entered as the Starting Index.

 

The first line of the file is processed with the following variable values:

  T[1] = 84031

  T[2] = UT

  T[3] = HANNA

  T[4] = DUCHESNE

  T[5] = 435

  T[6] = UTC - 7:00

  T[7] = Y

 

 

Next, the second line of the file is processed with the following variable values:

  T[1] = 84032

  T[2] = UT

  T[3] = HEBER CITY

  T[4] = WASATCH

  T[5] = 435

  T[6] = UTC - 7:00

  T[7] = Y

 

 

The third line of the file is processed with the following variable values:

  T[1] = 84033

  T[2] = UT

  T[3] = HENEFER

  T[4] = SUMMIT

  T[5] = 435

  T[6] = UTC - 7:00

  T[7] = Y

 

And so on, for each line of the file.

 

 

Variable array to receive result

This is the text variable array that will receive the first field in a record (or line of text). Sequential text variables will receive subsequent field numbers. For example, if T is selected as the starting text variable, the first field in the record is saved to T[1], the second field in the record is saved to T[2] and so on.

 

Or instead of using T as the variable, assign a name to the variable such as "sales" and create as an array.

 

These variables may then be used and processed by other macro commands. For example, to type out the first two records into another application, use the Text Type command. Insert the variables to play back. In this case insert %T[1]% %T[2]%. This plays back the values saved to the first two variables.

 

 

Starting Index

The Starting Index defines which element in the array to start with. In the example above the variables used in the Process an ASCII Text File would start with T[1]. If the starting index is set to 5 then the first variable used would be T[5].

 

If other variables in the macro were previously assigned to T[1], T[2], etc. and will be used later, then start the index with a higher value in order to not overwrite the values saved to these variables.

 

 

Note: The "T" variable is automatically set up as an array. The variables are assigned as T[1], T[2], T[3], etc. There is not a limit as to the number of elements in the array. If using a named variable, first create the variable, assign it as an array and select the number of elements needed.

 

 

Begin processing on record ___

Use this option to select which record to begin processing in the file. To process the entire file, enter 1 in the value field to indicate starting with the first record. Or use an Integer variable when specifying which record to start processing and the number of records to process.

 

 

What to process

Process All Records

Select this option to process every record in the file, if electing to start with record #1. Or it will start with the record selected and process all subsequent records.

 

Process Partial Records

Choose the "Process Partial Records if only needing to process a certain range of records (or lines of text).

 

Number of records to process

Enter in this field the number of records to process - how many lines of the file to process.

 

 

Parse blank lines

Use this option to process any blank lines in the file. The variable values will be empty when the blank line of the file is processed.

 

Leave this option unchecked so that the macro skips any blank lines in the file.

 

 

Clear the array before processing the file

Select this option to clear the values of all elements in the array selected to receive the results before the file processing begins. In the example above, the T variable array is selected. If a value were previously set to the the array element T[10] before the processing begins, this value would be cleared when the file processing starts.

 

 


 

 

ASCII File End Process

Each ASCII File Begin Process command requires a corresponding ASCII File End Process command. All commands in the script between the Begin and the End Process commands will be repeated the number of times instructed.

 

If all records have been read out of the text file and processed, then control goes to the macro command that immediately follows the ASCII File End Process command.

 

 

See Also 

Text File Begin Process

Break

Continue