automating test file creation using excel, ultraedit, and batch files to build test data
TRANSCRIPT
![Page 1: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/1.jpg)
Automating Test File Creation
Using Excel, UltraEdit, and Batch files to build test data
![Page 2: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/2.jpg)
Barry DeBruin - November 9th, 2004 2
Introduction
• This presentation provides fives steps that users can follow to automate test file creation.
• The example is for creating files used in a code set release where the test files could be identical with the exception of four fields which needed to have a unique code per file.
![Page 3: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/3.jpg)
Barry DeBruin - November 9th, 2004 3
Four Steps
1. Build a “Template” file
2. Preparing your file for Excel
3. Using Excel to update the data
4. Using batch files
![Page 4: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/4.jpg)
Barry DeBruin - November 9th, 2004 4
Build a “Template” file
• Start with a clean file (no errors, IG edits, business edits, etc.)
• In our example we’re building X12 837I’s (Institutional health care claims) beginning with the following file.
![Page 5: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/5.jpg)
Barry DeBruin - November 9th, 2004 5
![Page 6: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/6.jpg)
Barry DeBruin - November 9th, 2004 6
Preparing your file for Excel
First we determine differences needed for each file:1. We need to change the HCPCS code in three places
and modify the CLM01 for tracking.2. To isolate the fields to update, insert a TAB before
and after the field.3. Once this is completed, we’ll also need to unwrap
the file by removing the carriage return/line feeds to give us a continuous string of data (~^p with ~). NOTE: The same commands can be used in Word.
Here’s how using UltraEdit…
![Page 7: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/7.jpg)
Barry DeBruin - November 9th, 2004 7
![Page 8: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/8.jpg)
Barry DeBruin - November 9th, 2004 8
![Page 9: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/9.jpg)
Barry DeBruin - November 9th, 2004 9
![Page 10: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/10.jpg)
Barry DeBruin - November 9th, 2004 10
Using Excel to update the data
1. Copy the string of data from UltraEdit to a new workbook in Excel.
2. The tabs will separate your data into their own columns.
3. Autofill as many rows as you need test files.
![Page 11: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/11.jpg)
Barry DeBruin - November 9th, 2004 11
Note that each code has it’s own column
![Page 12: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/12.jpg)
Barry DeBruin - November 9th, 2004 12
1. Highlight the entire row.
2. Autofill by using the black cross (hold cursor over black dot)
3. Drag it down as many rows as needed.
![Page 13: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/13.jpg)
Barry DeBruin - November 9th, 2004 13
![Page 14: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/14.jpg)
Barry DeBruin - November 9th, 2004 14
1. You must have the data for each row separated with carriage return/line feeds.
2. Highlight your data and copy it for pasting onto the Excel spreadsheet.
![Page 15: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/15.jpg)
Barry DeBruin - November 9th, 2004 15
Paste into each column requiring an update.
![Page 16: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/16.jpg)
Barry DeBruin - November 9th, 2004 16
You may modify any column as show on these slides.
![Page 17: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/17.jpg)
Barry DeBruin - November 9th, 2004 17
You may modify any column as show on these slides.
![Page 18: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/18.jpg)
Barry DeBruin - November 9th, 2004 18
You may modify any column as show on these slides.
![Page 19: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/19.jpg)
Barry DeBruin - November 9th, 2004 19
You may modify any column as show on these slides.
![Page 20: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/20.jpg)
Barry DeBruin - November 9th, 2004 20
Using batch files
Now we’ll create a batch file to pipe data to multiple files.
1. Copy the data from the spreadsheet into a text file and save.
2. Remove all TAB characters.
![Page 21: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/21.jpg)
Barry DeBruin - November 9th, 2004 21
![Page 22: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/22.jpg)
Barry DeBruin - November 9th, 2004 22
![Page 23: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/23.jpg)
Barry DeBruin - November 9th, 2004 23
![Page 24: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/24.jpg)
Barry DeBruin - November 9th, 2004 24
Using batch files, cont’d.
Using UltraEdit we can use column mode to build a working batch file.
1. Save this file as a batch file (*.bat).
2. Identify a unique text pattern on each line.
3. Using the FINDSTR function from a DOS batch file we can build our individual files using each rows unique “Text Pattern” (aka Regular Expressions).
4. The syntax is as follows:FINDSTR /R "ISA.*I_AE_ADDED_PASS" Example.txt > I_AE_ADDED_PASS.txt
![Page 25: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/25.jpg)
Barry DeBruin - November 9th, 2004 25
Cursor is here
Using Column mode we can insert character into a specific column thereby building our batch file.
Here we are inserting a period in the fourth column to begin building our regular expression.
![Page 26: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/26.jpg)
Barry DeBruin - November 9th, 2004 26
![Page 27: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/27.jpg)
Barry DeBruin - November 9th, 2004 27
Your final batch file should look something like the following.
![Page 28: Automating Test File Creation Using Excel, UltraEdit, and Batch files to build test data](https://reader033.vdocuments.us/reader033/viewer/2022061613/5518c58055034638098b4b2c/html5/thumbnails/28.jpg)
Barry DeBruin - November 9th, 2004 28
We can see the files build as the batch file runs.