Advanced - ASCII Text File Process

This sample tutorial reads the contents of a text file in .csv format and inserts the data into another application. Formatting the text to one of the ASCII formats (comma delimited, tab delimited or ASCII delimited) is a quicker and more reliable way to transfer data than the copy and paste routine described in a previous tutorial. The macro reads directly from the file and inputs the results directly into the receiving application. No need to switch back and forth between applications - no copying from one program and pasting to another.

Creating a .csv file 

The first step is to create a .csv file with data for the macro to read.

1.Copy the zip code data shown below to the clipboard. (Highlight the text in the Zip Code Data box and press Ctrl + c.)

2.Open the notepad program. (Depending on your version of Windows, click on the Windows Start button, enter notepad in the Search field and press enter. Or right click on the Windows Start button, select Run, enter notepad and click OK.)

3.Place the cursor in the notepad program and press Ctrl + v to paste the text into notepad.

4.Click on File > Save As and make note or remember the location of where the file is about to be saved.

5.In the Name field remove .txt and type UtahZips.csv.

6.Click on the Save button to save the file.

Zip Code Data

Setting up the Macro

1.From the Macro Express - Explorer menu click on Macro > New Macro to open the Create New Macro window.

2.Hot Key should already be selected as the Activation.

3.Click on the HotKey edit box to place the cursor in this field.

4.Hold down the CTRL key, the SHIFT key and the Z key all at the same time and release them.

5.CTRL+SHIFT+Z should appear in the edit box. This is the hot key we will define for the macro.

6.Enter "Process ZIP Code Data" (without the quotes) in the Nickname field.

7.Click OK to open the Script Editor.

8.In the Script Editor click on the Scope tab and make sure that the macro scope is set to global.

9.Click on the Script tab to start building the macro.

Building the Macro

Process the ASCII Text File
 

1.Scroll through the list of commands and expand the Files/Folders category. Double click on the ASCII File Begin Process command to open the window of the same name.

2.Change the File Format option to Comma Delimited Text (.csv) from the drop down list.

3.Click on the browse button next to the File Name field and browse to the location of the saved .csv file.

4.Highlight the file and click on the Open button to place the name of the file and file path into the Filename edit box.

5.Place the cursor in the box next to Variable array to receive result and click on the Variables button to open the Insert Variable window.

6.Click on the + sign next to Type: Text Variables to expand the category.

7.Click on the T to place a letter T in the Variable Name field.

8.Click OK to save and return to the ASCII Begin File Process window.

9.Leave the Starting Index set to 1.

10.Leave the Begin processing on record field set to 1 in order to start processing the first line of the text file.

11.Make sure the Process All Records option is selected.

12.Click OK to save and return to the Script Editor.

The window should look like the above when finished, other than the location of the UtahZips.csv file.

üNote: The T variable is an array that by default allows up to 99 variables - T[1], T[2] and so on up to T[99]. We could also have named our own variable and created the array by clicking on the Add button in the Insert Variable window. This process is described in the Advanced - Using Integer Variables tutorial.

Insert the text into another application
 

1.Scroll through the list of commands and expand the Keyboard category. Double click on the Text Type command to open a window of the same name.

2.Click on the Variables button at the bottom of the window to open the Insert Variable command.

3.Click on the + sign next to Type: Text Variables to expand this category.

4.Click on the T to place T[ ] in the Variable Name field.

5.Enter the number 1 between the brackets so that it looks like T[1].

6.Click OK to save and return to the Text Type window.

7.Highlight the %T[1]% found in the Text Type window and press Ctrl c to copy to the clipboard.

8.Press Enter to move the cursor to the line below the %T[1] found on the first line and press Ctrl v to paste.

9.%T[1]% should now be displayed on two lines.

10.Move to line 3 and paste again.

11.Repeat the process so that %T[1]% is displayed seven times, each underneath the other.

12.On the last line, press the Enter key twice to move the cursor two lines below the last line of text.

13.Go to line 2 and change %T[1]% to %T[2]%.

14.Go to line 2 and change %T[1]% to %T[3]% and so on.

15.Click OK to save and return to the Script Editor.

End Process ASCII Text File
 

1.Scroll through the list of commands and expand the Files/Folders category. Double click on the ASCII File End Process command to insert the command at the end of the script.

2.Click on Script > Save Macro to save the macro just created.

Macro Script

The macro should look like the following in the Script Editor. If the macro doesn't look like the script shown below, arrange the commands so that they are in the following order. The file path in the first command most likely will be different, based on where you saved the UtahZips.csv file.

Playing Back the Macro

Start the WordPad program. Make sure the text cursor is in the WordPad program and then press CTRL+SHIFT+Z to run the macro.

The data is inserted in a vertical format as shown below.

üNote: This is a simple example of what can be done using the Process an ASCII Text File command. When entering the data into a database, choose which variables to insert and in what order, or add Tabs to move from field to field, etc. Create macros to update the record in the database, wait for a fresh screen, insert the second line of the text file into the database, and so on.