Tableau Data Prep

In My Humble Opinion
21

Stop focusing on object-oriented programming (OOP). Yes, in Python everything is an object and it was built from the ground up to facilitate the object-oriented programming paradigm, but you don’t actually need to know a single thing about OOP to write cromulent programs in Python, and in fact the vast majority of people using Python don’t have a clue about what an object oriented program even looks like. Python supports every major programming paradigm—procedure, declarative, concurrent, functional, et cetera—and can even be used as an imperative tool from the IDLE or IPython shell, so you can literally just type in equations or logic statements and get an answer the way you would with Matlab or BASIC or test whether a particular operation will work.

For what you are doing, you don’t really need to know anything about the deep functionality of Python beyond being able to import modules (literally a single line command), use the builtin string functionality, list slicing and comparison operations, and basic file I/O using with open… and the csv library. If you wanted to get clever you could learn the Openpyxl to be able to directly import from and manipulate Excel spreadsheets or the Pandas library to create spreadsheet-like dataframes and perform complex manipulations far more easily and reliably than in Excel but if you are content with the manual labor of saving Excel files as a CSV there is no reason to even do that.

It is really not as scary as you are making it out to be in your head.

I process different kinds and formats of complex data all the time using Python (and regex), a lot of it extracted from random Fortran and C tools where people just made up whatever format they liked, or people who think it is clever to dump time history data with different lengths, formats, and sample rates all in to one giant Excel spreadsheet. While it can sometimes be tricky to come up with a regex statement or a string command that correctly parse out the structure of the data, that is just an ability to build sufficient flexibility into the expression and have the code throw an exception when it encounters something that won’t parse.

I don’t do a lot of having to parse natural language data but pyparsing is purpose designed for that use. For Western-style street and mail addresses that do have a linear structure it could make short work of addresses that are just broken up across fields to parse out street name and number from city and state, and frankly if you poke around a bit you can probably find a utility that someone has written to do just that. (It would be hopeless with Japanese addresses, and I’m always thankful that in Japan every landline has an associated GPS coordinate position and most businesses provide mapcodes you can just punch into your rental car nav system).

There is really no reason to buy an expensive data analytics package to perform basic data formatting and processing, and frankly it probably is just going to make your job harder. The Salesforce rep is doubtless pitching it because he can then follow up offering you consluting services for the low low price of your left kidney and right testicle. The last thing you want to do is get locked into some kind of ‘enterprise software’ system that does a bunch of shit you will never need and requires expensive support to keep it functioning for your purpose.

Stranger

22

Silly question, but is that because the new Lenovos don’t have a CD drive?

23

I don’t think this is a matter of programming language, but of the variance in address labeling schemes. Sure you can easily find the average 100 Example Aves, but what about the El Camino Reals, Rue St Clairs, off-highway industrial areas, the rural addresses with no standardization…? There’s no formal system, just databases of near matches.

That other thread links to an address parsing Python lib, but its test suites barely cover just the basic examples. Is there some ready made solution for this? If not, it seems very difficult…

24

No idea. I only heard about it today.

Yep.

Here is the sort program. (I separate programs in case I need to identify a problem) Sorry about the formatting. ‘Code’ doesn’t work as well in Discourse as it did on the old board.

*********************************************************************

** Easytrieve to sort files **
** 11/10/10 – Johnny L.A. **

FILE FILE1 V(1000) SEQUENTIAL +
SYSNAME ‘G:\Auto Data[Code number]_BIS.txt’
SORT1 214 12 A

FILE FILE2 V(1000) SEQUENTIAL +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Current.txt’
SORT2 6 12 A

FILE FILEA V SEQUENTIAL CREATE RESET +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Prev_Sort.txt’
AGING-REC 1 350 A

FILE FILEB V SEQUENTIAL CREATE RESET +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Current_Sort.txt’
CUST-REC 1 350 A

SORT FILE1 TO FILEA USING SORT1
SORT FILE2 TO FILEB USING SORT2

JOB INPUT(FILE1 KEY SORT1 FILE2 KEY SORT2) FINISH(Z-REC)

Z-REC. PROC
DISPLAY ‘Done!’
END-PROC

25

Here is the cleanup program that compares the current file to the previous file. When the account numbers match, the previously-cleaned name and address information is written with the current amount information. It also does a case conversion.

*********************************************************************

** Address Match Easytrieve **
** 02/18/11 – Johnny L.A. Easytrieve to match current file to **
** previous file. Replaces matched names and **
** addresses in current file with names and addresses **
** from previous file. Writes unmatched records to a **
** separate file. **

FILE FILEA V(1000) SEQUENTIAL +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Prev_Sort.txt’
PREV-AR 214 12 A
PREV-NAME 12 40 A
PREV-NAME2 52 40 A
PREV-ADDR1 92 40 A
PREV-ADDR2 122 30 A
PREV-CITY 158 20 A
PREV-STATE 178 20 A
PREV-ZIP 198 10 A
PREV-ZIP9 329 9 A
PREV-PHONE 230 10 A

FILE FILEB V(1000) SEQUENTIAL +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Current_Sort.txt’
CUR-REC 6 350 A
IN-REC 1 350 A
FIELD-BYTE-IN1 IN-REC 1 A INDEX SUB1
CUR-AR 6 12 A
CUR-NAME 18 40 A
CUR-NAME2 58 40 A
CUR-ADDR1 98 30 A
CUR-ADDR2 128 30 A
CUR-CITY 158 20 A
CUR-STATE 178 20 A
CUR-ZIP 198 10 A
CUR-BAL 208 142 A
CUR-PHONE 208 10 A
CUR-DOLS 307 10 A
CUR-DOLS-YY 313 2 N
A-DATE 171 6 A
A-MM 171 2 A
A-DD 173 2 A
A-YY 175 2 A
A-TEMP-DATE 232 8 A
A-TEMP-MM 232 2 N
A-TEMP-DD 235 2 N
A-TEMP-YY 238 2 N

FILE FILEC V SEQUENTIAL CREATE RESET +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Current_Output.txt’
OUT-REC 6 350 A
OUT-AR 6 12 A
OUT-NAME 18 40 A
OUT-NAME2 58 40 A
OUT-ADDR1 98 30 A
OUT-ADDR2 128 30 A
OUT-CITY 158 20 A
OUT-STATE 178 20 A
OUT-ZIP 198 10 A
OUT-BAL 208 142 A
OUT-PHONE 208 12 A

FILE NONMATCH V SEQUENTIAL CREATE RESET +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Nonmatch.txt’
NOT-REC 6 350 A

** WORKING STORAGE **

WS-IN-REC-A-IN W 350 A
FIELD-BYTE-IN1B WS-IN-REC-A-IN 1 A INDEX SUB1B

WS-MM W 2 N
WS-DD W 2 N
WS-YY W 2 N
WS-DATE W 6 N
WS-YEARS W 2 N

COUNTER1 W 6 N
COUNTER2 W 6 N
COUNTER3 W 6 N
COUNTER5 W 6 N

JOB INPUT(FILEA KEY PREV-AR FILEB KEY CUR-AR) START(A-DATE) +
FINISH(Z-REC)

IF CUR-DOLS-YY NUMERIC
WS-YEARS = WS-YY - CUR-DOLS-YY
IF WS-YEARS GT 2
CUR-DOLS = ‘’
COUNTER5 = COUNTER5 + 1
END-IF
END-IF

IF CUR-AR = ‘520217’
DISPLAY CUR-AR CUR-NAME ’ internal records dropped.’
GOTO JOB
END-IF
IF MATCHED
OUT-AR = PREV-AR
OUT-NAME = PREV-NAME
OUT-NAME2 = PREV-NAME2
OUT-ADDR1 = PREV-ADDR1
OUT-ADDR2 = PREV-ADDR2
OUT-CITY = PREV-CITY
OUT-STATE = PREV-STATE
OUT-ZIP = PREV-ZIP9
OUT-BAL = CUR-BAL
OUT-PHONE = PREV-PHONE
PUT FILEC
COUNTER1 = COUNTER1 + 1
ELSE
IF NOT MATCHED FILEA
PERFORM CASECONV
NOT-REC = CUR-REC
PUT NONMATCH
COUNTER2 = COUNTER2 + 1
END-IF
END-IF

** A-DATE captures current date **

A-DATE. PROC
A-TEMP-DATE = SYSDATE
A-MM = A-TEMP-MM
A-DD = A-TEMP-DD
A-YY = A-TEMP-YY
WS-MM = A-TEMP-MM
WS-DD = A-TEMP-DD
WS-YY = A-TEMP-YY
END-PROC

** Case conversion **

CASECONV. PROC
SUB1 = 0
SUB1B = 0
DO WHILE SUB1 LE 349
IF FIELD-BYTE-IN1 = ‘a’
FIELD-BYTE-IN1 = ‘A’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘b’
FIELD-BYTE-IN1 = ‘B’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘c’
FIELD-BYTE-IN1 = ‘C’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘d’
FIELD-BYTE-IN1 = ‘D’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘e’
FIELD-BYTE-IN1 = ‘E’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘f’
FIELD-BYTE-IN1 = ‘F’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘g’
FIELD-BYTE-IN1 = ‘G’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘h’
FIELD-BYTE-IN1 = ‘H’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘i’
FIELD-BYTE-IN1 = ‘I’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘j’
FIELD-BYTE-IN1 = ‘J’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘k’
FIELD-BYTE-IN1 = ‘K’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘l’
FIELD-BYTE-IN1 = ‘L’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘m’
FIELD-BYTE-IN1 = ‘M’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘n’
FIELD-BYTE-IN1 = ‘N’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘o’
FIELD-BYTE-IN1 = ‘O’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘p’
FIELD-BYTE-IN1 = ‘P’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘q’
FIELD-BYTE-IN1 = ‘Q’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘r’
FIELD-BYTE-IN1 = ‘R’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘s’
FIELD-BYTE-IN1 = ‘S’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘t’
FIELD-BYTE-IN1 = ‘T’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘u’
FIELD-BYTE-IN1 = ‘U’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘v’
FIELD-BYTE-IN1 = ‘V’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘w’
FIELD-BYTE-IN1 = ‘W’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘x’
FIELD-BYTE-IN1 = ‘X’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘y’
FIELD-BYTE-IN1 = ‘Y’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
IF FIELD-BYTE-IN1 = ‘z’
FIELD-BYTE-IN1 = ‘Z’
FIELD-BYTE-IN1B = FIELD-BYTE-IN1
SUB1 = SUB1 + 1
SUB1B = SUB1B + 1
ELSE
*DISPLAY FIELD-BYTE-IN1

  •  FIELD-BYTE-IN1B   = FIELD-BYTE-IN1
MOVE FIELD-BYTE-IN1 TO FIELD-BYTE-IN1B
SUB1  = SUB1 + 1
SUB1B = SUB1B + 1
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-IF
    END-DO
    MOVE WS-IN-REC-A-IN TO IN-REC
    END-PROC

** Z-REC creates the Z-record and reports when job is finished **

Z-REC. PROC
COUNTER3 = COUNTER1 + COUNTER2
DISPLAY 'Matched: ’ COUNTER1
DISPLAY 'Not matched: ’ COUNTER2
DISPLAY 'DOLS modified: ’ COUNTER5
DISPLAY 'Total records: ’ COUNTER3
DISPLAY ‘Done!’
END-PROC

26

This is the reformat program.

*********************************************************************

** 802364 [Company name] **
** 02/16/11 – Johnny L.A. **

  • Writes to [350-byte Trade format, New 1500+ byte format, and 350-byte trade with other agency’s header

FILE FILEA V(1000) SEQUENTIAL +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Input.txt’
AR-CHK 6 1 N
AR-NUM 6 12 A
NAME 18 40 A
NAME2 58 40 A
ADDR1 98 30 A
ADDR2 128 30 A
CITY 158 20 A
STATE 178 2 A
ZIP 198 5 A
ZIP-4 203 4 A
PHONE-AREA 208 3 A
PHONE-PRE 211 3 A
PHONE-REST 214 4 A
RHC 223 12 A
RHC-NUM 223 12 N 0
RHC-BYTE-IN1 RHC 1 A INDEX SUB1
RHC-DOLLARS 223 10 N
RHC-CENTS 233 2 N
BALANCE 235 12 A
BALANCE-NUM 235 12 N 0
BALANCE-BYTE-IN1 BALANCE 1 A INDEX SUB1
BALANCE-DOLLARS 235 10 N
BALANCE-CENTS 245 2 N
CURRENT 247 12 A
CURRENT-NUM 247 12 N 0
CURRENT-BYTE-IN1 CURRENT 1 A INDEX SUB1
CURRENT-DOLLARS 247 10 N
CURRENT-CENTS 257 2 N
01-30 259 12 A
01-30-BYTE-IN1 01-30 1 A INDEX SUB1
01-30-DOLLARS 259 10 N
01-30-CENTS 269 2 N
31-60 271 12 A
31-60-BYTE-IN1 31-60 1 A INDEX SUB1
31-60-DOLLARS 271 10 N
31-60-CENTS 281 2 N
61-90 283 12 A
61-90-BYTE-IN1 61-90 1 A INDEX SUB1
61-90-DOLLARS 283 10 N
61-90-CENTS 293 2 N
OVER-90 295 12 A
OVER-90-BYTE-IN1 OVER-90 1 A INDEX SUB1
OVER-90-DOLLARS 295 10 N
OVER-90-CENTS 305 2 N
DOLS-MM 307 2 N
DOLS-DD 310 2 N
DOLS-YY 313 2 N
DOLS-YY-NUM 313 2 N
START-MM 317 2 N
START-DD 320 2 N
START-YY 323 2 N

FILE FILEB V SEQUENTIAL CREATE RESET +
SYSNAME ‘G:\Auto Data[Code number]_BIS.txt’
*BIS-format output file
CDR-OUT-REC 1 350 A
CDR-REC-CD 1 1 A
CDR-CONTRIB# 2 6 A
CDR-BUS-CAT 8 4 A
CDR-ACCT-NAME 12 40 A
CDR-ADDL-NAME 52 40 A
CDR-ADDR1 92 30 A
CDR-ADDL-ADDR 122 30 A
CDR-SIC-CODE 152 4 A
CDR-CITY-ST-CD 157 1 A
CDR-CTY 158 20 A
CDR-ST 178 20 A
CDR-ZIP 198 5 A
CDR-ST-ABBREVIATION 203 2 A
CDR-EXTDT 208 6 A
CDR-EXTDT-YY 212 2 N 0
CDR-ARNUM 214 12 A
CDR-CUS-SIC-CODE 226 4 A
CDR-PHONE 230 10 A
CDR-PHONE-AREA 230 3 A
CDR-PHONE-PREFIX 233 3 A
CDR-PHONE-REST 236 4 A
CDR-YRS-CUST-CD 248 1 A
CDR-YRS-CUST 249 2 N
CDR-DOLS-MM 251 2 A
CDR-DOLS-YY 253 2 A
CDR-DOLS-MM-NUM 251 2 N 0
CDR-DOLS-YY-NUM 253 2 N 0
CDR-TERMS 255 7 A
CDR-RHC-CODE 262 1 A
CDR-RHC 263 8 N
CDR-BAL-CODE 271 1 A
CDR-BALANCE 272 8 N 0
CDR-BAL-SIGN 280 1 A
CDR-CURRENT 281 7 N 0
CDR-CUR-SIGN 288 1 A
CDR-01-30 289 7 N 0
CDR-01-30-SIGN 296 1 A
CDR-31-60 297 7 N 0
CDR-31-60-SIGN 304 1 A
CDR-61-90 305 7 N 0
CDR-61-90-SIGN 312 1 A
CDR-OVER-90 313 7 N 0
CDR-OVER-90-SIGN 320 1 A
CDR-COMMENT-CD 321 2 A
CDR-ZERO-FILL 323 4 A
CDR-OPTIONAL-ZIP5 329 5 A
CDR-OPTIONAL-ZIP4 334 4 A
A-REC 1 350 A
A-REC-CODE 1 1 A
A-CONTUM 2 6 A
A-VERSION 8 6 A
A-DATE 171 6 A
A-MM 171 2 A
A-DD 173 2 A
A-YY 175 2 A
A-DESCRIPTION 177 25 A
A-TEMP-DATE 232 8 A
A-TEMP-MM 232 2 A
A-TEMP-DD 235 2 A
A-TEMP-YY 238 2 A
Z-REC 1 350 A
Z-REC-CODE 1 1 A
Z-REC-COUNT 2 7 N
Z-RHC 9 10 N 0
Z-BALANCE 19 10 N 0
Z-CURRENT 29 10 N 0
Z-01-30 39 10 N 0
Z-31-60 49 10 N 0
Z-61-90 59 10 N 0
Z-OVER-90 69 10 N 0

FILE FILEC V SEQUENTIAL CREATE RESET +
SYSNAME ‘G:\Auto Data[code number]_CPEG.txt’
*CPEG-format output file
*CPEG header record
CPEG-A-REC 1 183 A
CPEG-A-REC-CODE 1 2 A
CPEG-A-VERSION 3 8 A
CPEG-A-CONTUM 11 6 A
CPEG-A-EQUIFAX 17 10 A
CPEG-A-DANDB 27 9 A
CPEG-A-CONT-NAME 36 120 A
CPEG-A-CREATE-DATE 156 8 A
CPEG-A-CREATE-MM 156 2 A
CPEG-A-CREATE-DD 158 2 A
CPEG-A-CREATE-CC 160 2 A
CPEG-A-CREATE-YY 162 2 A
CPEG-A-EXT-DATE 164 8 A
CPEG-A-EXT-MM 164 2 A
CPEG-A-EXT-DD 166 2 A
CPEG-A-EXT-CC 168 2 A
CPEG-A-EXT-YY 170 2 A
CPEG-A-TEMP-DATE 276 8 A
CPEG-A-TEMP-MM 276 2 A
CPEG-A-TEMP-DD 279 2 A
CPEG-A-TEMP-YY 282 2 A

*CPEG data record
CPEG-OUT-REC 1 1430 A
CPEG-REC-CD 1 2 A
CPEG-CONTRIB# 3 6 A
CPEG-BUS-CAT 9 4 A
CPEG-EXTDT 13 8 A
CPEG-EXTDT-MM 13 2 A
CPEG-EXTDT-DD 15 2 A
CPEG-EXTDT-CC 17 2 A
CPEG-EXTDT-YY 19 2 A
CPEG-EXTDT-YY-N 19 2 N 0
CPEG-SUB-CODE 21 7 A
CPEG-EQUIFAX-CONTNUM 28 10 A
CPEG-DANDB-CONTNUM 38 9 A
CPEG-NAME-DISPLAY 47 1 A
CPEG-ACCT-NAME 48 120 A
CPEG-NAME-TYPE 168 3 A
CPEG-EXP-BIN 171 10 A
CPEG-DUNS 181 10 A
CPEG-EQUIFAX-ID 191 10 A
CPEG-CUST-SIC 201 8 A
CPEG-NAICS 209 6 A
CPEG-TAX-ID 215 11 A
CPEG-TAX-ID-TYPE 226 1 A
CPEG-OWNER-CHANGE 227 1 A
CPEG-NAME-CHANGE 228 1 A
CPEG-ADDR1 229 50 A
CPEG-ADDL-ADDR 279 50 A
CPEG-CTY 329 32 A
CPEG-ST-ABBREVIATION 361 2 A
CPEG-PROVINCE 363 25 A
CPEG-ZIP 388 9 A
CPEG-COUNTRY 397 3 A
CPEG-CUST-LOC-TYPE 400 2 A
CPEG-CUST-ADDR-TYPE 402 1 A
CPEG-PHONE-CONTRY 403 5 A
CPEG-PHONE-AREA 408 3 A
CPEG-PHONE-PREFIX 411 3 A
CPEG-PHONE-REST 414 4 A
CPEG-CUST-URL 422 75 A
CPEG-CUST-EMAIL 497 75 A
CPEG-ADDL-NAME 572 120 A
CPEG-ADDL-NAME-TYPE 692 3 A
CPEG-ADDR2 695 50 A
CPEG-ADDL-ADDR2 745 50 A
CPEG-ADDL-CITY 795 32 A
CPEG-ADDL-ST 827 2 A
CPEG-ADDL-PROVINCE 829 25 A
CPEG-ADDL-ZIP 854 9 A
CPEG-OPTIONAL-ZIP5 854 5 A
CPEG-OPTIONAL-ZIP4 859 4 A
CPEG-ADDL-COUNTRY 863 3 A
CPEG-ADDL-LOC-TYPE 866 2 A
CPEG-ADDL-ADDR-TYPE 868 1 A
CPEG-ADDL-PHONE-COUNTRY 869 5 A
CPEG-ADDL-PHONE 874 14 A
CPEG-ADDL-PHONE-AREA 874 3 A
CPEG-ADDL-PHONE-PREFIX 877 3 A
CPEG-ADDL-PHONE-REST 880 4 A
CPEG-LEGAL-ENTITY-TYPE 888 1 A
CPEG-FRANCHISE-IND 889 1 A
CPEG-ARNUM 890 40 A
CPEG-DATE-ACQUIRED 930 8 A
CPEG-ACCOUNT-TYPE 938 3 A
CPEG-START-DATE 941 8 A
CPEG-START-MM 941 2 N
CPEG-START-DD 943 2 N
CPEG-START-CC 945 2 N
CPEG-START-YY 947 2 N
CPEG-START-CCYY 945 4 N
CPEG-CLOSE-DATE 949 8 A
CPEG-CLOSE-MM 949 2 A
CPEG-CLOSE-DD 951 2 A
CPEG-CLOSE-CC 953 2 A
CPEG-CLOSE-YY 955 2 A
CPEG-CLOSE-CCYY 953 4 A
CPEG-REASON-CLOSED 957 2 A
CPEG-DOLS-MM 959 2 A
CPEG-DOLS-DD 961 2 A
CPEG-DOLS-CC 963 2 A
CPEG-DOLS-YY 965 2 A
CPEG-DOLS 959 10 A
*CPEG-DOLS-MM-NUM 959 2 N 0
*CPEG-DOLS-CC-NUM 961 2 N 0
*CPEG-DOLS-YY-NUM 963 2 N 0
CPEG-LAST-ACT-TYPE 967 1 A
CPEG-PAYMENT-TYPE 968 2 A
CPEG-PAY-FREQ 970 1 A
CPEG-TERMS-DURATION 971 4 A
CPEG-TERMS-DUR-1 974 1 A
CPEG-TERMS-DUR-2 973 2 A
CPEG-TERMS-DUR-3 972 3 A
CPEG-TERMS 975 15 A
CPEG-STATUS-CODE 990 2 A
CPEG-ACTIVITY-FLAG 992 1 A
CPEG-NUM-OWNERS 993 2 A
CPEG-NUM-GUARANTORS 995 2 A
CPEG-GOVT-GUAR 997 3 A
CPEG-PERCENT-GUAR 1000 3 A
CPEG-SECURED 1003 1 A
CPEG-LEASE-CAT 1004 2 A
CPEG-MATURE-DATE 1006 8 A
CPEG-MATURE-MM 1006 2 A
CPEG-MATURE-DD 1008 2 A
CPEG-MATURE-CC 1010 2 A
CPEG-MATURE-YY 1012 2 A
CPEG-MATURE-CCYY 1010 4 A
CPEG-COLLATERAL 1014 2 A
CPEG-CREDIT-LIMIT 1016 11 N
CPEG-RHC-CODE 1027 1 A
CPEG-RHC 1028 11 N
CPEG-ORIG-LOAN-AMT 1039 11 N
CPEG-CUR-LOAN-AMT 1050 11 N
CPEG-SCHED-PAY 1061 11 N
CPEG-ACTUAL-PAY 1072 11 N
CPEG-STATEMENT-DATE 1083 8 A
CPEG-STATEMENT-MM 1083 2 A
CPEG-STATEMENT-DD 1085 2 A
CPEG-STATEMENT-CC 1087 2 A
CPEG-STATEMENT-YY 1089 2 A
CPEG-STATEMENT-CCYY 1087 4 A
CPEG-DUE-DATE 1091 8 A
CPEG-DUE-MM 1091 2 A
CPEG-DUE-DD 1093 2 A
CPEG-DUE-CC 1095 2 A
CPEG-DUE-YY 1097 2 A
CPEG-DUE-CCYY 1095 4 A
CPEG-DOLP-DATE 1099 8 A
CPEG-DOLP-MM 1099 2 A
CPEG-DOLP-DD 1101 2 A
CPEG-DOLP-CC 1103 2 A
CPEG-DOLP-YY 1105 2 A
CPEG-DOLP-CCYY 1103 4 A
CPEG-BALLOON-AMT 1107 11 A
CPEG-BALLOON-DATE 1118 8 A
CPEG-BALLOON-MM 1118 2 A
CPEG-BALLOON-DD 1120 2 A
CPEG-BALLOON-CC 1122 2 A
CPEG-BALLOON-YY 1124 2 A
CPEG-BALLOON-CCYY 1122 4 A
CPEG-BAL-CODE 1126 1 A
CPEG-BALANCE 1127 11 N 0
CPEG-BAL-SIGN 1127 1 A
CPEG-CURRENT 1138 11 N 0
CPEG-CUR-SIGN 1138 1 A
CPEG-01-30 1149 11 N 0
CPEG-01-30-SIGN 1149 1 A
CPEG-31-60 1160 11 N 0
CPEG-31-60-SIGN 1160 1 A
CPEG-61-90 1171 11 N 0
CPEG-61-90-SIGN 1171 1 A
CPEG-91-120 1182 11 N 0
CPEG-91-120-SIGN 1182 1 A
CPEG-121-150 1193 11 N 0
CPEG-121-150-SIGN 1193 1 A
CPEG-151-180 1204 11 N 0
CPEG-151-180-SIGN 1204 1 A
CPEG-OVER-180 1215 11 N 0
CPEG-OVER-180-SIGN 1215 1 A
CPEG-TOTAL-PAST-DUE 1226 11 N 0
CPEG-TOTAL-PAST-DUE-SIGN 1226 1 A
CPEG-1ST-DEL-DATE 1237 8 A
CPEG-1ST-DEL-MM 1237 2 A
CPEG-1ST-DEL-DD 1239 2 A
CPEG-1ST-DEL-CC 1241 2 A
CPEG-1ST-DEL-YY 1243 2 A
CPEG-1ST-DEL-CCYY 1241 4 A
CPEG-CHARGE-OFF 1245 11 N 0
CPEG-CHARGE-OFF-SIGN 1245 1 A
CPEG-CHARGE-DATE 1256 8 A
CPEG-CHARGE-MM 1256 2 A
CPEG-CHARGE-DD 1258 2 A
CPEG-CHARGE-CC 1260 2 A
CPEG-CHARGE-YY 1262 2 A
CPEG-CHARGE-CCYY 1260 4 A
CPEG-COLLECTION-FLAG 1264 1 A
CPEG-COLLECT-AMT 1265 11 N 0
CPEG-COLECT-SIGN 1265 1 A
CPEG-PMT-AMT 1276 11 N 0
CPEG-PMT-SIGN 1276 1 A
CPEG-PMT-HIST 1287 24 A
CPEG-COMMENT 1311 3 A
CPEG-COMMENT2 1314 3 A
CPEG-COMMENT3 1317 3 A
CPEG-COMMENT-TEXT 1320 70 A
CPEG-USER-TRACKING 1390 40 A
CPEG-SCORE-FLAG 1430 1 A
*Trailer record

FILE FILED V SEQUENTIAL CREATE RESET +
SYSNAME ‘C:\Users\john\OneDrive - [my company]+
\Documents\EZ_Text_Files[Code number]_Crossfoot_Errors.txt’
ERROR-OUT-REC 1 350 A

FILE STSAUTO V SEQUENTIAL CREATE RESET +
SYSNAME ‘G:[BMCRA]\S000.BC.[Code number].STSAUTO.txt’
AUTO-RECORD 1 1430 A

FILE EQUIFAX V SEQUENTIAL CREATE RESET +
SYSNAME ‘G:[Other agency]\Comany LLC [Code number] A[another code number].txt’
EQUIFAX-RECORD 1 350 A
EQUIFAX-HEADER1 1 50 A
EQUIFAX-EXTDT-MM 25 2 A
EQUIFAX-EXTDT-DD 27 2 A
EQUIFAX-EXTDT-CC 29 2 A
EQUIFAX-EXTDT-YY 31 2 A

** WORKING STORAGE **

WS-RHC-A-IN W 12 A
RHC-BYTE-IN1B WS-RHC-A-IN 1 A INDEX SUB1B

WS-BALANCE-A-IN W 12 A
BALANCE-BYTE-IN1B WS-BALANCE-A-IN 1 A INDEX SUB1B

WS-CURRENT-A-IN W 12 A
CURRENT-BYTE-IN1B WS-CURRENT-A-IN 1 A INDEX SUB1B

WS-01-30-A-IN W 12 A
01-30-BYTE-IN1B WS-01-30-A-IN 1 A INDEX SUB1B

WS-31-60-A-IN W 12 A
31-60-BYTE-IN1B WS-31-60-A-IN 1 A INDEX SUB1B

WS-61-90-A-IN W 12 A
61-90-BYTE-IN1B WS-61-90-A-IN 1 A INDEX SUB1B

WS-OVER-90-A-IN W 12 A
OVER-90-BYTE-IN1B WS-OVER-90-A-IN 1 A INDEX SUB1B

WS-MM W 2 A
WS-DD W 2 A
WS-YY W 2 A
WS-YY-DOLS W 4 N 0
WS-DOLS-YY W 2 N 0
WS-DATE W 8 A
WS-DOLS-FLAG W 1 A
WS-YRS-CUST W 3 N 0

WS-Z-REC-COUNT W 7 N
WS-Z-RHC W 10 N 0
WS-Z-BALANCE W 10 N 0
WS-Z-CURRENT W 10 N 0
WS-Z-01-30 W 10 N 0
WS-Z-31-60 W 10 N 0
WS-Z-61-90 W 10 N 0
WS-Z-OVER-90 W 10 N 0

WS-PAST01 W 11 N 0
WS-PAST30 W 11 N 0
WS-PAST60 W 11 N 0
WS-PAST90 W 11 N 0
WS-PAST120 W 11 N 0
WS-PAST150 W 11 N 0
WS-PAST180 W 11 N 0

WS-BALANCE W 8 N 0
WS-CDR-BALANCE W 8 N 0
WS-CDR-CURRENT W 7 N 0
WS-CDR-01-30 W 7 N 0
WS-CDR-31-60 W 7 N 0
WS-CDR-61-90 W 7 N 0
WS-CDR-OVER-90 W 7 N 0
WS-CROSSFOOT W 7 N 0
WS-CROSS-COUNTER W 7 N
WS-TOTAL-RECORDS W 7 N
WS-PERCENT W 4 N 2

WS-DISPLAY-COUNTER W 4 N

JOB INPUT(FILEA) START(A-REC) FINISH(Z-REC)

** Wrie BIS-format output file **

IF NAME = ‘NAME’ ‘Name’ ‘COMPANY NAME’ ’ ’ ‘Customer Name’ +
‘CUSTOMER NAME’ ‘Customer Name’ ‘NAME1’ ‘Name1’ 'NAME2 ’ ‘Name2’ +
‘cust’ ‘Custname’ ‘Customer’ ‘Sort Name’ ‘SORT NAME’
GOTO JOB
END-IF

IF AR-NUM = ‘3A750’
CURRENT = BALANCE
01-30 = ‘0.00’
31-60 = ‘0.00’
61-90 = ‘0.00’
OVER-90 = ‘0.00’
END-IF

WS-TOTAL-RECORDS = WS-TOTAL-RECORDS + 1
CDR-ARNUM = AR-NUM
CDR-OUT-REC = ’ ’
CDR-REC-CD = ‘C’
CDR-CONTRIB# = ‘802364’
CDR-BUS-CAT = ‘1250’
CDR-ACCT-NAME = NAME
CDR-ADDL-NAME = NAME2
CDR-ADDR1 = ADDR1
CDR-ADDL-ADDR = ADDR2
CDR-SIC-CODE = ‘5031’
CDR-CITY-ST-CD = ‘2’
CDR-CTY = CITY
CDR-ST = STATE
CDR-ZIP = ZIP
CDR-ST-ABBREVIATION = STATE
CDR-EXTDT = WS-DATE

*CDR-EXTDT = ‘080124’

PERFORM NUMFIX
CDR-CUS-SIC-CODE = ‘0000’
CDR-ARNUM = AR-NUM
CDR-PHONE-AREA = PHONE-AREA
CDR-PHONE-PREFIX = PHONE-PRE
CDR-PHONE-REST = PHONE-REST
IF CDR-PHONE = ‘’ ‘0’
CDR-PHONE = ‘0000000000’
END-IF
CDR-TERMS = 'NET 30 ’
CDR-COMMENT-CD = ‘00’
CDR-RHC-CODE = ‘0’

  • CDR-RHC = 0
    CDR-ZERO-FILL = ‘0000’
    CDR-OPTIONAL-ZIP5 = ZIP
    CDR-OPTIONAL-ZIP4 = ZIP-4
    IF DOLS-MM NOT NUMERIC
    DOLS-MM = 0
    DOLS-YY = 0
    END-IF
    CDR-DOLS-MM = DOLS-MM
    CDR-DOLS-YY = DOLS-YY

** Check for crossfoot errors **

IF CDR-BAL-SIGN = ‘1’
WS-CDR-BALANCE = CDR-BALANCE * (-1)
ELSE
WS-CDR-BALANCE = CDR-BALANCE
END-IF
IF CDR-CUR-SIGN = ‘1’
WS-CDR-CURRENT = CDR-CURRENT * (-1)
ELSE
WS-CDR-CURRENT = CDR-CURRENT
END-IF
IF CDR-01-30-SIGN = ‘1’
WS-CDR-01-30 = CDR-01-30 * (-1)
ELSE
WS-CDR-01-30 = CDR-01-30
END-IF
IF CDR-31-60-SIGN = ‘1’
WS-CDR-31-60 = CDR-31-60 * (-1)
ELSE
WS-CDR-31-60 = CDR-31-60
END-IF
IF CDR-61-90-SIGN = ‘1’
WS-CDR-61-90 = CDR-61-90 * (-1)
ELSE
WS-CDR-61-90 = CDR-61-90
END-IF
IF CDR-OVER-90-SIGN = ‘1’
WS-CDR-OVER-90 = CDR-OVER-90 * (-1)
ELSE
WS-CDR-OVER-90 = CDR-OVER-90
END-IF
WS-BALANCE = WS-CDR-CURRENT + +
WS-CDR-01-30 + +
WS-CDR-31-60 + +
WS-CDR-61-90 + +
WS-CDR-OVER-90
IF WS-BALANCE LT 0
WS-BALANCE = WS-BALANCE * (-1)
END-IF
WS-CROSSFOOT = CDR-BALANCE - WS-BALANCE
IF WS-CROSSFOOT LT 0
WS-CROSSFOOT = WS-CROSSFOOT * (-1)
END-IF
IF WS-CROSSFOOT GT 5
ERROR-OUT-REC = CDR-OUT-REC
WS-CROSS-COUNTER = WS-CROSS-COUNTER + 1
PUT FILED
*PUT FILEB
ELSE
WS-Z-REC-COUNT = WS-Z-REC-COUNT + 1
PUT FILEB
EQUIFAX-RECORD = CDR-OUT-REC
PUT EQUIFAX
END-IF

Broken for Discourse

27

The rest of the program (broken for Discourse):

*********************************************************************
** Write CPEG-format output file **

CPEG-OUT-REC = ’ ’
CPEG-REC-CD = 'C ’
CPEG-CONTRIB# = ‘802364’
CPEG-BUS-CAT = ‘1250’
*CPEG-EXTDT = CPEG-A-EXT-DATE
CPEG-EXTDT = WS-DATE
CPEG-EXTDT-MM = WS-MM
CPEG-EXTDT-DD = WS-DD
CPEG-EXTDT-CC = ‘20’
CPEG-EXTDT-YY = WS-YY

*CPEG-EXTDT = ‘08012024’

CPEG-SUB-CODE = ‘0000000’
CPEG-EQUIFAX-CONTNUM = ’ ’
CPEG-DANDB-CONTNUM = ’ ’
CPEG-NAME-DISPLAY = ‘Y’
CPEG-ACCT-NAME = NAME
CPEG-NAME-TYPE = ‘PN’
CPEG-EXP-BIN = ’ ’
CPEG-DUNS = ’ ’
CPEG-EQUIFAX-ID = ’ ’
CPEG-CUST-SIC = ’ ’
CPEG-NAICS = ’ ’
CPEG-TAX-ID = ’ ’
CPEG-TAX-ID-TYPE = ’ ’
CPEG-OWNER-CHANGE = ‘N’
CPEG-NAME-CHANGE = ‘N’
CPEG-ADDR1 = ADDR1
CPEG-ADDL-ADDR = ADDR2
CPEG-CTY = CITY
CPEG-ST-ABBREVIATION = CDR-ST-ABBREVIATION
CPEG-PROVINCE = ’ ’
CPEG-ZIP = ZIP
CPEG-COUNTRY = ’ ’
CPEG-CUST-LOC-TYPE = ‘99’
CPEG-CUST-ADDR-TYPE = ‘B’
CPEG-PHONE-CONTRY = ’ ’
CPEG-PHONE-AREA = PHONE-AREA
CPEG-PHONE-PREFIX = PHONE-PRE
CPEG-PHONE-REST = PHONE-REST
CPEG-CUST-URL = ’ ’
CPEG-CUST-EMAIL = ’ ’
CPEG-ADDL-NAME = NAME2
CPEG-ADDL-NAME-TYPE = ‘DBA’
CPEG-ADDR2 = ADDR1
CPEG-ADDL-ADDR2 = ADDR2
CPEG-ADDL-CITY = CITY
CPEG-ADDL-ST = CDR-ST-ABBREVIATION
CPEG-ADDL-PROVINCE = ’ ’
CPEG-ADDL-ZIP = ZIP
CPEG-OPTIONAL-ZIP5 = ZIP
CPEG-OPTIONAL-ZIP4 = ZIP-4
CPEG-ADDL-COUNTRY = ’ ’
CPEG-ADDL-LOC-TYPE = ’ ’
CPEG-ADDL-ADDR-TYPE = ’ ’
CPEG-ADDL-PHONE-COUNTRY = ’ ’
CPEG-ADDL-PHONE = ’ ’
CPEG-ADDL-PHONE-AREA = ’ ’
CPEG-ADDL-PHONE-PREFIX = ’ ’
CPEG-ADDL-PHONE-REST = ’ ’
CPEG-LEGAL-ENTITY-TYPE = ‘9’
CPEG-FRANCHISE-IND = ’ ’
CPEG-ARNUM = AR-NUM
CPEG-DATE-ACQUIRED = ’ ’
CPEG-ACCOUNT-TYPE = ‘150’
CPEG-START-DATE = ’ ’
*CPEG-START-MM = 0
*CPEG-START-DD = 0
*CPEG-START-CC = 0
*CPEG-START-YY = 0
*CPEG-START-CCYY = 0

CPEG-START-MM = START-MM
CPEG-START-DD = START-DD
IF START-YY LE CDR-EXTDT-YY
CPEG-START-CC = 20
ELSE
CPEG-START-CC = 19
END-IF
CPEG-START-YY = START-YY
IF CPEG-START-MM = 0
CPEG-START-DATE = ‘’
END-IF

CPEG-CLOSE-DATE = ’ ’
CPEG-CLOSE-MM = ’ ’
CPEG-CLOSE-DD = ’ ’
CPEG-CLOSE-CC = ’ ’
CPEG-CLOSE-YY = ’ ’
CPEG-CLOSE-CCYY = ’ ’
CPEG-REASON-CLOSED = ’ ’

CPEG-DOLS-MM = DOLS-MM
CPEG-DOLS-DD = DOLS-DD
WS-DOLS-YY = CDR-EXTDT-YY - DOLS-YY-NUM
IF WS-DOLS-YY LT 0
CPEG-DOLS-CC = ‘19’
ELSE
CPEG-DOLS-CC = ‘20’
END-IF
CPEG-DOLS-YY = DOLS-YY
IF CPEG-DOLS-MM = ‘00’
CPEG-DOLS-MM = ’ ’
CPEG-DOLS-DD = ’ ’
CPEG-DOLS-CC = ’ ’
CPEG-DOLS-YY = ’ ’
END-IF
CPEG-LAST-ACT-TYPE = ‘U’
CPEG-PAYMENT-TYPE = ‘01’
CPEG-PAY-FREQ = ‘U’
CPEG-TERMS-DURATION = ‘AR’
*CPEG-TERMS-DUR-1 = ‘0’
*CPEG-TERMS-DUR-2 = ‘00’
*CPEG-TERMS-DUR-3 = ‘000’
CPEG-TERMS = CDR-TERMS
CPEG-STATUS-CODE = ’ ’
CPEG-ACTIVITY-FLAG = ’ ’
CPEG-NUM-OWNERS = ’ ’
CPEG-NUM-GUARANTORS = ‘00’
CPEG-GOVT-GUAR = ’ ’
CPEG-PERCENT-GUAR = ’ ’
CPEG-SECURED = ’ ’
CPEG-LEASE-CAT = ’ ’
CPEG-MATURE-DATE = ’ ’
CPEG-MATURE-MM = ’ ’
CPEG-MATURE-DD = ’ ’
CPEG-MATURE-CC = ’ ’
CPEG-MATURE-YY = ’ ’
CPEG-MATURE-CCYY = ’ ’
CPEG-COLLATERAL = ’ ’
CPEG-CREDIT-LIMIT = 0
CPEG-RHC-CODE = ‘1’
CPEG-RHC = CDR-RHC
CPEG-ORIG-LOAN-AMT = 0
CPEG-CUR-LOAN-AMT = 0
CPEG-SCHED-PAY = 0
CPEG-ACTUAL-PAY = 0
CPEG-STATEMENT-DATE = ’ ’
CPEG-STATEMENT-MM = ’ ’
CPEG-STATEMENT-DD = ’ ’
CPEG-STATEMENT-CC = ’ ’
CPEG-STATEMENT-YY = ’ ’
CPEG-STATEMENT-CCYY = ’ ’
CPEG-DUE-DATE = ’ ’
CPEG-DUE-MM = ’ ’
CPEG-DUE-DD = ’ ’
CPEG-DUE-CC = ’ ’
CPEG-DUE-YY = ’ ’
CPEG-DUE-CCYY = ’ ’
CPEG-DOLP-DATE = ’ ’
CPEG-DOLP-MM = ’ ’
CPEG-DOLP-DD = ’ ’
CPEG-DOLP-CC = ’ ’
CPEG-DOLP-YY = ’ ’
CPEG-DOLP-CCYY = ’ ’
CPEG-BALLOON-AMT = ’ ’
CPEG-BALLOON-DATE = ’ ’
CPEG-BALLOON-MM = ’ ’
CPEG-BALLOON-DD = ’ ’
CPEG-BALLOON-CC = ’ ’
CPEG-BALLOON-YY = ’ ’
CPEG-BALLOON-CCYY = ’ ’
CPEG-BAL-CODE = ‘0’
CPEG-BALANCE = CDR-BALANCE
IF CDR-BAL-SIGN = ‘1’
CPEG-BAL-SIGN = ‘-’
ELSE
CPEG-BAL-SIGN = ‘0’
END-IF
CPEG-CURRENT = CDR-CURRENT
IF CDR-CUR-SIGN = ‘1’
CPEG-CUR-SIGN = ‘-’
ELSE
CPEG-CUR-SIGN = ‘0’
END-IF
CPEG-01-30 = CDR-01-30
IF CDR-01-30-SIGN = ‘1’
CPEG-01-30-SIGN = ‘-’
ELSE
CPEG-01-30-SIGN = ‘0’
END-IF
CPEG-31-60 = CDR-31-60
IF CDR-31-60-SIGN = ‘1’
CPEG-31-60-SIGN = ‘-’
ELSE
CPEG-31-60-SIGN = ‘0’
END-IF
CPEG-61-90 = CDR-61-90
IF CDR-61-90-SIGN = ‘1’
CPEG-61-90-SIGN = ‘-’
ELSE
CPEG-61-90-SIGN = ‘0’
END-IF
CPEG-91-120 = CDR-OVER-90
IF CDR-OVER-90-SIGN = ‘1’
CPEG-91-120-SIGN = ‘-’
ELSE
CPEG-91-120-SIGN = ‘0’
END-IF
CPEG-121-150 = 0
CPEG-121-150-SIGN = ‘0’
CPEG-151-180 = 0
CPEG-151-180-SIGN = ‘0’
CPEG-OVER-180 = 0
CPEG-OVER-180-SIGN = ‘0’
WS-PAST01 = CDR-01-30
WS-PAST30 = CDR-31-60
WS-PAST60 = CDR-61-90
WS-PAST90 = CDR-OVER-90
WS-PAST120 = 0
WS-PAST150 = 0
WS-PAST180 = 0
IF CDR-01-30-SIGN = ‘1’
WS-PAST01 = WS-PAST01 * (-1)
END-IF
IF CDR-31-60-SIGN = ‘1’
WS-PAST30 = WS-PAST30 * (-1)
END-IF
IF CDR-61-90-SIGN = ‘1’
WS-PAST60 = WS-PAST60 * (-1)
END-IF
IF CDR-OVER-90-SIGN = ‘1’
WS-PAST90 = WS-PAST90 * (-1)
END-IF
CPEG-TOTAL-PAST-DUE = WS-PAST01 + WS-PAST30 + WS-PAST60 + +
WS-PAST90 + WS-PAST120 + WS-PAST150 + WS-PAST180
IF CPEG-TOTAL-PAST-DUE LT 0
CPEG-TOTAL-PAST-DUE = CPEG-TOTAL-PAST-DUE * (-1)
CPEG-TOTAL-PAST-DUE-SIGN = ‘-’
END-IF
CPEG-1ST-DEL-DATE = ’ ’
CPEG-1ST-DEL-MM = ’ ’
CPEG-1ST-DEL-DD = ’ ’
CPEG-1ST-DEL-CC = ’ ’
CPEG-1ST-DEL-YY = ’ ’
CPEG-1ST-DEL-CCYY = ’ ’
CPEG-CHARGE-OFF = 0
CPEG-CHARGE-OFF-SIGN = ‘0’
CPEG-CHARGE-DATE = ’ ’
CPEG-CHARGE-MM = ’ ’
CPEG-CHARGE-DD = ’ ’
CPEG-CHARGE-CC = ’ ’
CPEG-CHARGE-YY = ’ ’
CPEG-CHARGE-CCYY = ’ ’
CPEG-COLLECTION-FLAG = ‘N’
CPEG-COLLECT-AMT = 0
CPEG-COLECT-SIGN = ‘0’
CPEG-PMT-AMT = 0
CPEG-PMT-SIGN = ‘0’
CPEG-PMT-HIST = ‘’
PERFORM COMMENT
CPEG-COMMENT2 = ’ ’
CPEG-COMMENT3 = ’ ’
CPEG-USER-TRACKING = ’ ’
CPEG-SCORE-FLAG = ’ ’
PUT FILEC
AUTO-RECORD = CPEG-OUT-REC
PUT STSAUTO

** NUMFIX right-justifies and zero-fills numeric fields

NUMFIX. PROC

WS-RHC-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
DO WHILE SUB1 GE 0
IF RHC-BYTE-IN1 NUMERIC
RHC-BYTE-IN1B = RHC-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-RHC-A-IN TO RHC
IF RHC-CENTS GE 50
RHC-DOLLARS = RHC-DOLLARS + 1
END-IF
CDR-RHC = RHC-DOLLARS
WS-Z-RHC = WS-Z-RHC + CDR-RHC

WS-BALANCE-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-BAL-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF BALANCE-BYTE-IN1 NUMERIC
BALANCE-BYTE-IN1B = BALANCE-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF BALANCE-BYTE-IN1 = ‘-’
CDR-BAL-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-BALANCE-A-IN TO BALANCE
IF BALANCE-CENTS GE 50
BALANCE-DOLLARS = BALANCE-DOLLARS + 1
END-IF
CDR-BALANCE = BALANCE-DOLLARS
IF CDR-BAL-SIGN = ‘1’
WS-Z-BALANCE = WS-Z-BALANCE - CDR-BALANCE
ELSE
WS-Z-BALANCE = WS-Z-BALANCE + CDR-BALANCE
END-IF

WS-CURRENT-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-CUR-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF CURRENT-BYTE-IN1 NUMERIC
CURRENT-BYTE-IN1B = CURRENT-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF CURRENT-BYTE-IN1 = ‘-’
CDR-CUR-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-CURRENT-A-IN TO CURRENT
IF CURRENT-CENTS GE 50
CURRENT-DOLLARS = CURRENT-DOLLARS + 1
END-IF
CDR-CURRENT = CURRENT-DOLLARS
IF CDR-CUR-SIGN = ‘1’
WS-Z-CURRENT = WS-Z-CURRENT - CDR-CURRENT
ELSE
WS-Z-CURRENT = WS-Z-CURRENT + CDR-CURRENT
END-IF

WS-01-30-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-01-30-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF 01-30-BYTE-IN1 NUMERIC
01-30-BYTE-IN1B = 01-30-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF 01-30-BYTE-IN1 = ‘-’
CDR-01-30-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-01-30-A-IN TO 01-30
IF 01-30-CENTS GE 50
01-30-DOLLARS = 01-30-DOLLARS + 1
END-IF
CDR-01-30 = 01-30-DOLLARS
IF CDR-01-30-SIGN = ‘1’
WS-Z-01-30 = WS-Z-01-30 - CDR-01-30
ELSE
WS-Z-01-30 = WS-Z-01-30 + CDR-01-30
END-IF

WS-31-60-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-31-60-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF 31-60-BYTE-IN1 NUMERIC
31-60-BYTE-IN1B = 31-60-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF 31-60-BYTE-IN1 = ‘-’
CDR-31-60-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-31-60-A-IN TO 31-60
IF 31-60-CENTS GE 50
31-60-DOLLARS = 31-60-DOLLARS + 1
END-IF
CDR-31-60 = 31-60-DOLLARS
IF CDR-31-60-SIGN = ‘1’
WS-Z-31-60 = WS-Z-31-60 - CDR-31-60
ELSE
WS-Z-31-60 = WS-Z-31-60 + CDR-31-60
END-IF

WS-61-90-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-61-90-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF 61-90-BYTE-IN1 NUMERIC
61-90-BYTE-IN1B = 61-90-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF 61-90-BYTE-IN1 = ‘-’
CDR-61-90-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-61-90-A-IN TO 61-90
IF 61-90-CENTS GE 50
61-90-DOLLARS = 61-90-DOLLARS + 1
END-IF
CDR-61-90 = 61-90-DOLLARS
IF CDR-61-90-SIGN = ‘1’
WS-Z-61-90 = WS-Z-61-90 - CDR-61-90
ELSE
WS-Z-61-90 = WS-Z-61-90 + CDR-61-90
END-IF

WS-OVER-90-A-IN = ‘000000000000’
SUB1 = 11
SUB1B = 11
CDR-OVER-90-SIGN = ’ ’
DO WHILE SUB1 GE 0
IF OVER-90-BYTE-IN1 NUMERIC
OVER-90-BYTE-IN1B = OVER-90-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
IF OVER-90-BYTE-IN1 = ‘-’
CDR-OVER-90-SIGN = ‘1’
END-IF
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-OVER-90-A-IN TO OVER-90
IF OVER-90-CENTS GE 50
OVER-90-DOLLARS = OVER-90-DOLLARS + 1
END-IF
CDR-OVER-90 = OVER-90-DOLLARS
IF CDR-OVER-90-SIGN = ‘1’
WS-Z-OVER-90 = WS-Z-OVER-90 - CDR-OVER-90
ELSE
WS-Z-OVER-90 = WS-Z-OVER-90 + CDR-OVER-90
END-IF

END-PROC

** COMMENT converts Trade-format comment codes to CPEG format

COMMENT. PROC
IF CDR-COMMENT-CD = ‘97’
CPEG-COMMENT = ‘354’
CPEG-COMMENT-TEXT = ‘BANKRUPTCY’
ELSE
IF CDR-COMMENT-CD = ‘88’
CPEG-COMMENT = ‘353’
CPEG-COMMENT-TEXT = ‘LEGAL INVOLVEMENT’
ELSE
IF CDR-COMMENT-CD = ‘82’
CPEG-COMMENT = ‘355’
CPEG-COMMENT-TEXT = ‘FORECLOSURE’
ELSE
IF CDR-COMMENT-CD = ‘65’
CPEG-COMMENT = ‘150’
CPEG-COMMENT-TEXT = ‘PAID COLLECTION’
ELSE
IF CDR-COMMENT-CD = ‘76’
CPEG-COMMENT = ‘170’
CPEG-COMMENT-TEXT = ‘COLLECTION’
ELSE
IF CDR-COMMENT-CD = ‘83’
CPEG-COMMENT = ‘151’
CPEG-COMMENT-TEXT = ‘UNPAID COLLECTION’
ELSE
IF CDR-COMMENT-CD = ‘80’
CPEG-COMMENT = ‘163’
CPEG-COMMENT-TEXT = ‘WRITE-OFF’
ELSE
IF CDR-COMMENT-CD = ‘84’
CPEG-COMMENT = ‘500’
CPEG-COMMENT-TEXT = ‘SKIP ACCOUNT - CANNOT LOCATE’
ELSE
IF CDR-COMMENT-CD = ‘87’
CPEG-COMMENT = ‘311’
CPEG-COMMENT-TEXT = ‘SERVICES DISCONNECTED’
ELSE
IF CDR-COMMENT-CD = ‘89’
CPEG-COMMENT = ‘312’
CPEG-COMMENT-TEXT = ‘SERVICES SUSPENDED’
ELSE
IF CDR-COMMENT-CD = ‘62’
CPEG-COMMENT = ‘473’
CPEG-COMMENT-TEXT = ‘NSF’
ELSE
IF CDR-COMMENT-CD = ‘43’
CPEG-COMMENT = ‘474’
CPEG-COMMENT-TEXT = ‘RETURNED CHECKS’
ELSE
IF CDR-COMMENT-CD = ‘74’
CPEG-COMMENT = ‘352’
CPEG-COMMENT-TEXT = ‘LIENS’
ELSE
IF CDR-COMMENT-CD = ‘75’
CPEG-COMMENT = ‘534’
CPEG-COMMENT-TEXT = ‘REFUSED FURTHER CREDIT’
ELSE
IF CDR-COMMENT-CD = ‘73’
CPEG-COMMENT = ‘169’
CPEG-COMMENT-TEXT = ‘ADJUSTMENT BUREAU’
ELSE
IF CDR-COMMENT-CD = ‘59’ ‘61’
CPEG-COMMENT = ‘531’
CPEG-COMMENT-TEXT = ‘CREDIT PRIVLEDGES WITHDRAWN - SELL ONLY COD’
ELSE
IF CDR-COMMENT-CD = ‘79’
CPEG-COMMENT = ‘533’
CPEG-COMMENT-TEXT = ‘CREDIT WITHDRAWN’
ELSE
IF CDR-COMMENT-CD = ‘53’
CPEG-COMMENT = ‘468’
CPEG-COMMENT-TEXT = ‘90 DAYS SLOW’
ELSE
IF CDR-COMMENT-CD = ‘54’
CPEG-COMMENT = ‘467’
CPEG-COMMENT-TEXT = ‘60 DAYS SLOW’
ELSE
IF CDR-COMMENT-CD = ‘55’
CPEG-COMMENT = ‘466’
CPEG-COMMENT-TEXT = ‘30 DAYS SLOW’
ELSE
IF CDR-COMMENT-CD = ‘57’
CPEG-COMMENT = ‘465’
CPEG-COMMENT-TEXT = ‘15 DAYS SLOW’
ELSE
IF CDR-COMMENT-CD = ‘21’
CPEG-COMMENT = ‘469’
CPEG-COMMENT-TEXT = ‘7 DAYS LATE’
ELSE
IF CDR-COMMENT-CD = ‘22’
CPEG-COMMENT = ‘470’
CPEG-COMMENT-TEXT = ‘14 DAYS LATE’
ELSE
IF CDR-COMMENT-CD = ‘23’
CPEG-COMMENT = ‘471’
CPEG-COMMENT-TEXT = ‘21 DAYS LATE’
ELSE
IF CDR-COMMENT-CD = ‘56’
CPEG-COMMENT = ‘458’
CPEG-COMMENT-TEXT = ‘PAYS SLOWLY’
ELSE
IF CDR-COMMENT-CD = ‘35’
CPEG-COMMENT = ‘461’
CPEG-COMMENT-TEXT = ‘PAYING DELINQUENT’
ELSE
IF CDR-COMMENT-CD = ‘70’
CPEG-COMMENT = ‘536’
CPEG-COMMENT-TEXT = ‘CASH IN ADVANCE - REQUESTED’
ELSE
IF CDR-COMMENT-CD = ‘68’
CPEG-COMMENT = ‘535’
CPEG-COMMENT-TEXT = ‘CASH IN ADVANCE’
ELSE
IF CDR-COMMENT-CD = ‘51’
CPEG-COMMENT = ‘532’
CPEG-COMMENT-TEXT = ‘DEPOSIT REQUIRED’
ELSE
IF CDR-COMMENT-CD = ‘69’
CPEG-COMMENT = ‘152’
CPEG-COMMENT-TEXT = ‘COLLECTION RELEASE’
ELSE
IF CDR-COMMENT-CD = ‘64’
CPEG-COMMENT = ‘528’
CPEG-COMMENT-TEXT = ‘CASH ON DELIVERY’
ELSE
IF CDR-COMMENT-CD = ‘41’
CPEG-COMMENT = ‘159’
CPEG-COMMENT-TEXT = ‘LEASE DEFAULTS’
ELSE
IF CDR-COMMENT-CD = ‘91’
CPEG-COMMENT = ‘156’
CPEG-COMMENT-TEXT = ‘BOND DEFAULT’
ELSE
IF CDR-COMMENT-CD = ‘81’
CPEG-COMMENT = ‘165’
CPEG-COMMENT-TEXT = ‘REDEEMED POSSESSION’
ELSE
IF CDR-COMMENT-CD = ‘78’
CPEG-COMMENT = ‘164’
CPEG-COMMENT-TEXT = ‘REPOSSESSION’
ELSE
IF CDR-COMMENT-CD = ‘60’
CPEG-COMMENT = ‘472’
CPEG-COMMENT-TEXT = ‘ADVERSE TREND’
ELSE
IF CDR-COMMENT-CD = ‘40’
CPEG-COMMENT = ‘401’
CPEG-COMMENT-TEXT = ‘HOLDING ORDERS’
ELSE
IF CDR-COMMENT-CD = ‘72’
CPEG-COMMENT = ‘314’
CPEG-COMMENT-TEXT = ‘SECURED ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘71’
CPEG-COMMENT = ‘460’
CPEG-COMMENT-TEXT = ‘NOT PAYING AS AGREED’
ELSE
IF CDR-COMMENT-CD = ‘77’
CPEG-COMMENT = ‘454’
CPEG-COMMENT-TEXT = ‘IMPROVING’
ELSE
IF CDR-COMMENT-CD = ‘67’
CPEG-COMMENT = ‘464’
CPEG-COMMENT-TEXT = ‘WAS A PROBLEM’
ELSE
IF CDR-COMMENT-CD = ‘30’
CPEG-COMMENT = ‘455’
CPEG-COMMENT-TEXT = ‘REFUSE FINANCE CHARGE’
ELSE
IF CDR-COMMENT-CD = ‘28’
CPEG-COMMENT = ‘200’
CPEG-COMMENT-TEXT = ‘UNEARNED DISCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘52’
CPEG-COMMENT = ‘475’
CPEG-COMMENT-TEXT = ‘UNAUTHORIZED DEDUCTION’
ELSE
IF CDR-COMMENT-CD = ‘63’
CPEG-COMMENT = ‘313’
CPEG-COMMENT-TEXT = ‘REFINANCED LOAN’
ELSE
IF CDR-COMMENT-CD = ‘29’
CPEG-COMMENT = ‘317’
CPEG-COMMENT-TEXT = ‘ACCOUNT CLOSED’
ELSE
IF CDR-COMMENT-CD = ‘66’
CPEG-COMMENT = ‘463’
CPEG-COMMENT-TEXT = ‘WAS PAST DUE’
ELSE
IF CDR-COMMENT-CD = ‘20’
CPEG-COMMENT = ‘202’
CPEG-COMMENT-TEXT = ‘EXCESSIVE DISCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘32’
CPEG-COMMENT = ‘225’
CPEG-COMMENT-TEXT = ‘DISPUTED OR CONTESTED ACCOUNT OR INVOICE’
ELSE
IF CDR-COMMENT-CD = ‘46’
CPEG-COMMENT = ’ ’
CPEG-COMMENT-TEXT = ‘CONSOLIDATION NOTE’
ELSE
IF CDR-COMMENT-CD = ‘44’
CPEG-COMMENT = ‘452’
CPEG-COMMENT-TEXT = ‘PAYS BY NOTE’
ELSE
IF CDR-COMMENT-CD = ‘42’
CPEG-COMMENT = ‘402’
CPEG-COMMENT-TEXT = ‘RETURNED MERCHANDISE’
ELSE
IF CDR-COMMENT-CD = ‘58’
CPEG-COMMENT = ‘426’
CPEG-COMMENT-TEXT = ‘RECENT OWNER CHANGE’
ELSE
IF CDR-COMMENT-CD = ‘24’
CPEG-COMMENT = ‘100’
CPEG-COMMENT-TEXT = ‘FIRST SALE’
ELSE
IF CDR-COMMENT-CD = ‘07’
CPEG-COMMENT = ‘310’
CPEG-COMMENT-TEXT = ‘LIMITED EXPERIENCE’
ELSE
IF CDR-COMMENT-CD = ‘47’
CPEG-COMMENT = ‘530’
CPEG-COMMENT-TEXT = ‘SELLING COD - ADDITIONAL GOES TO AMOUNT OWING’
ELSE
IF CDR-COMMENT-CD = ‘45’
CPEG-COMMENT = ‘529’
CPEG-COMMENT-TEXT = ‘COD CUSTOMER REQUEST’
ELSE
IF CDR-COMMENT-CD = ‘18’
CPEG-COMMENT = ‘537’
CPEG-COMMENT-TEXT = ‘FLOOR PLAN ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘19’
CPEG-COMMENT = ‘405’
CPEG-COMMENT-TEXT = ‘JOB COMPLETE’
ELSE
IF CDR-COMMENT-CD = ‘50’
CPEG-COMMENT = ‘403’
CPEG-COMMENT-TEXT = ‘PRODUCT COMPLAINT’
ELSE
IF CDR-COMMENT-CD = ‘17’
CPEG-COMMENT = ‘527’
CPEG-COMMENT-TEXT = ‘SPECIAL TERMS’
ELSE
IF CDR-COMMENT-CD = ‘08’
CPEG-COMMENT = ‘201’
CPEG-COMMENT-TEXT = ‘EARNED DISCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘13’
CPEG-COMMENT = ‘203’
CPEG-COMMENT-TEXT = ‘GIVES DISCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘36’
CPEG-COMMENT = ‘400’
CPEG-COMMENT-TEXT = ‘UNFILLED ORDERS’
ELSE
IF CDR-COMMENT-CD = ‘31’
CPEG-COMMENT = ‘538’
CPEG-COMMENT-TEXT = ‘CREDIT MEMO PENDING’
ELSE
IF CDR-COMMENT-CD = ‘14’
CPEG-COMMENT = ‘526’
CPEG-COMMENT-TEXT = ‘INSTALLMENT ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘15’
CPEG-COMMENT = ‘301’
CPEG-COMMENT-TEXT = ‘MULTIPLE LOCATIONS’
ELSE
IF CDR-COMMENT-CD = ‘10’
CPEG-COMMENT = ‘525’
CPEG-COMMENT-TEXT = ‘SELL ON CONSIGNMENT’
ELSE
IF CDR-COMMENT-CD = ‘48’
CPEG-COMMENT = ‘404’
CPEG-COMMENT-TEXT = ‘TRADE ACCEPTED’
ELSE
IF CDR-COMMENT-CD = ‘11’
CPEG-COMMENT = ‘300’
CPEG-COMMENT-TEXT = ‘SEASONAL ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘06’
CPEG-COMMENT = ‘309’
CPEG-COMMENT-TEXT = ‘RETENTION’
ELSE
IF CDR-COMMENT-CD = ‘09’
CPEG-COMMENT = ‘451’
CPEG-COMMENT-TEXT = ‘BONDING SATISFACTORY’
ELSE
IF CDR-COMMENT-CD = ‘16’
CPEG-COMMENT = ‘453’
CPEG-COMMENT-TEXT = ‘SATISFACTORY’
ELSE
IF CDR-COMMENT-CD = ‘12’
CPEG-COMMENT = ‘457’
CPEG-COMMENT-TEXT = ‘PAYS PROMPT’
ELSE
IF CDR-COMMENT-CD = ‘25’
CPEG-COMMENT = ‘450’
CPEG-COMMENT-TEXT = ‘EXCELLENT ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘01’
CPEG-COMMENT = ‘102’
CPEG-COMMENT-TEXT = ‘CUSTOMER LESS THAN ONE YEAR’
ELSE
IF CDR-COMMENT-CD = ‘05’
CPEG-COMMENT = ‘101’
CPEG-COMMENT-TEXT = ‘NEW ACCOUNT’
ELSE
IF CDR-COMMENT-CD = ‘02’
CPEG-COMMENT = ‘103’
CPEG-COMMENT-TEXT = ‘CUSTOMER 1-5 YEARS’
ELSE
IF CDR-COMMENT-CD = ‘03’
CPEG-COMMENT = ‘104’
CPEG-COMMENT-TEXT = ‘CUSTOMER 5-10 YEARS’
ELSE
IF CDR-COMMENT-CD = ‘04’
CPEG-COMMENT = ‘105’
CPEG-COMMENT-TEXT = ‘CUSTOMER GREATER THAN 10 YEARS’
ELSE
IF CDR-COMMENT-CD = ‘00’ ’ ’
CPEG-COMMENT = ’ ’
CPEG-COMMENT-TEXT = ‘NO COMMENT’
ELSE
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF

END-PROC

** A_REC creates the A-record

A-REC. PROC
A-REC = ’ ’
A-REC-CODE = ‘A’
A-CONTUM = ‘802364’
A-VERSION = ‘C75001’
A-TEMP-DATE = SYSDATE
A-MM = A-TEMP-MM
A-DD = A-TEMP-DD
A-YY = A-TEMP-YY
WS-MM = A-TEMP-MM
WS-DD = A-TEMP-DD
WS-YY = A-TEMP-YY
A-TEMP-DATE = ’ ’
WS-DATE = A-DATE
WS-MM = A-MM
WS-DD = A-DD
WS-YY = A-YY
A-DESCRIPTION = ‘[BMCRA] CONTRIBUTOR FILE’
PUT FILEB

EQUIFAX-HEADER1 = 'A [Company LLC] vDC010 08022013 0 [Other code number]
EQUIFAX-EXTDT-MM = WS-MM
EQUIFAX-EXTDT-DD = WS-DD
EQUIFAX-EXTDT-CC = ‘20’
EQUIFAX-EXTDT-YY = WS-YY
PUT EQUIFAX

CPEG-A-REC = ’ ’
CPEG-A-REC-CODE = ‘A ’
CPEG-A-VERSION = ‘CPEG1.00’
CPEG-A-CONTUM = [Companycode]’
CPEG-A-EQUIFAX = ’ ’
CPEG-A-DANDB = ’ ’
CPEG-A-CONT-NAME = ‘[Company LLC]’
*CPEG-A-CREATE-DATE =
CPEG-A-CREATE-MM = A-MM
CPEG-A-CREATE-DD = A-DD
CPEG-A-CREATE-CC = ‘20’
CPEG-A-CREATE-YY = A-YY
*CPEG-A-EXT-DATE =
CPEG-A-EXT-MM = WS-MM
CPEG-A-EXT-DD = WS-DD
CPEG-A-EXT-CC = ‘20’
CPEG-A-EXT-YY = WS-YY
*CPEG-A-TEMP-DATE =
CPEG-A-TEMP-MM = WS-MM
CPEG-A-TEMP-DD = WS-DD
CPEG-A-TEMP-YY = WS-YY
PUT FILEC
AUTO-RECORD = CPEG-OUT-REC
PUT STSAUTO

END-PROC

** Z_REC creates the Z-record

Z-REC. PROC
Z-REC = ’ ’
Z-REC-CODE = ‘Z’
Z-REC-COUNT = WS-Z-REC-COUNT
Z-RHC = WS-Z-RHC
Z-BALANCE = WS-Z-BALANCE
Z-CURRENT = WS-Z-CURRENT
Z-01-30 = WS-Z-01-30
Z-31-60 = WS-Z-31-60
Z-61-90 = WS-Z-61-90
Z-OVER-90 = WS-Z-OVER-90
PUT FILEB
[Other agency]-RECORD = CDR-OUT-REC
PUT [Other agency]
WS-PERCENT = (WS-CROSS-COUNTER / WS-TOTAL-RECORDS) * 100
DISPLAY 'Total Records: ’ WS-TOTAL-RECORDS
DISPLAY 'Records written: ’ WS-Z-REC-COUNT
DISPLAY 'Crossfoot Errors: ’ WS-CROSS-COUNTER ’ = ’ +
WS-PERCENT ‘% of file’
DISPLAY ‘Done!’
END-PROC

28

The thing is, I used some sort of converter to get Python script, and it makes absolutely no sense to me. I can only do sequential programs.

It’s because the version of Easytrieve we have is not supported on the Lenovo’s OS.

29

I have never been so happy to see the end of a program listing, and I often have to deal with legacy multi-hundred line F77 spaghetti code written by people who were brilliant engineers in their day, I’m sure, but couldn’t structure or document a Fortran program to save their dear lives. That is…insane. I’m pretty sure inclusion of the term “EASY” in the same was some bit of bitter irony by the programer tasked with building an interpreter for it. I’m also morally certain it could be replaced with at most a few dozen lines of easily maintained Python, or any other modern language like Lua, Scala, or even (ugh!) JavaScript.

You need to stop putting up roadblocks for yourself by saying what you “can only do”. I don’t know what you mean by “some sort of converter to get Python script” but I guarantee that you can learn to write a simple Python program to import your data within 20 minutes and some samples to crib from, and from there is is a matter of figuring out how to use functions like string and lists to manipulate the data and then export it out to whatever final format you need. What you want to do is really not that difficult aside from actually definite the rules to tell the program how to evaluate, slice, and recombine the data.

Or else, resign yourself to running this god-awful, clunky ‘sequential’ programming language on obsolete operating systems and spending massive amounts of time doing manual cleanup and import/export.

Stranger

30

Here’s what a Python converter came up with for the sort:

import os
from pathlib import Path

def sort_file(input_path, output_path, sort_key_start, sort_key_length, ascending=True):
    """Sort a file based on a specified key range."""
    with open(input_path, 'r') as f:
        lines = f.readlines()
    
    # Sort lines based on the specified key range
    lines.sort(key=lambda x: x[sort_key_start-1:sort_key_start-1+sort_key_length], 
              reverse=not ascending)
    
    with open(output_path, 'w') as f:
        f.writelines(lines)

def main():
    # Define file paths (using format strings for the code number)
    code_number = input("Enter code number: ")  # Assuming code number is dynamic
    
    # File paths
    file1_path = Path('G:/Auto Data') / f"{code_number}_BIS.txt"
    file2_path = Path('C:/Users/john/OneDrive - [my company]/Documents/EZ_Text_Files') / f"{code_number}_Current.txt"
    filea_path = Path('C:/Users/john/OneDrive - [my company]/Documents/EZ_Text_Files') / f"{code_number}_Prev_Sort.txt"
    fileb_path = Path('C:/Users/john/OneDrive - [my company]/Documents/EZ_Text_Files') / f"{code_number}_Current_Sort.txt"
    
    # Ensure output directories exist
    os.makedirs(filea_path.parent, exist_ok=True)
    os.makedirs(fileb_path.parent, exist_ok=True)
    
    # Sort operations
    # FILE1 sorted by position 214-225 (12 chars) ascending
    sort_file(file1_path, filea_path, 214, 12)
    
    # FILE2 sorted by position 6-17 (12 chars) ascending
    sort_file(file2_path, fileb_path, 6, 12)
    
    # Process the files (original JOB INPUT logic would go here)
    # Since the original doesn't specify what to do with the files after sorting,
    # we'll just display "Done!" as in the original
    
    print("Done!")

if __name__ == "__main__":
    main()

The structure is completely foreign to me. The first bit of the third program is too large for the converter.
31

Whew, that was a lot to read!

Most of it looks like basic string manipulation, and I agree with @Stranger_On_A_Train there that it can be drastically reduced down to a few lines of any modern programming language, Python or otherwise (especially the whole section dedicated to “make this letter uppercase”, which would be a single function in any modern language).

But what I don’t understand is what you said in the other thread (which is from 2017? have the app requirements changed since then?):

How does your Easytrieve program (or some other part of your workflow) “know” that JOHNNY'S BUILDERS AND belongs together with CONTRACTORS INC as two parts of a name, while 8910 HARVARD is part of the street? And not every incoming row of data will be structured exactly that way, right, with the second “field” consisting of part of a name and part of a street?

Like you’re not just moving certain characters around from one column to another, you actually have to have some sort of heuristic there to try to detect “these words & characters together are probably a name, and that other part is probably part of the street address”. I don’t see that in the existing Easytrieve code, unless I’m missing something?

Is that feature (string token detection & name/address normalization) part of your requirements for the new app or not?

You can probably port the current Easytrieve code you have to Python or another modern language with ChatGPT, Gemini, Claude, or another coding-centric LLM. If you can provide sample input & output files, it can also write the tests for you to help ensure that the resulting conversion is correct. Here’s the beginning of a ChatGPT convo, for example, but I can’t properly test it without input & output samples: ChatGPT - Easytrieve Program Analysis

However, I don’t know if this would fully solve your problem… it’s one thing to just reach feature parity with your existing Easytrieve code, but it’s much harder if you also need to try to clean up the resulting names & addresses.

I also don’t believe Tableau would be able to do this kind of thing in its GUI interface alone; you would probably also need to write some sort of script or a very convoluted query to match your current transformation.

This sort of “ETL” (extract transform load) is very common in today’s workflows, and it would probably be easier to maintain over time (especially for anyone else who comes after you) if you did port it to Python, learning the basics with LLM or YouTube help… it is the de-facto standard language for data processing now, especially with all the AI stuff. There are also many, many online tutorials and videos for Python, both free and paid.

But I’m not sure what your underlying goals here are. Your existing programs read like they’re translating between several archaic systems; in this new version, will the output still have to go into other archaic systems (so it’s necessary to maintain the current formatting) or are they being used for some other purpose ultimately? Cuz you could drastically simplify the logic if you are able to also use modern data formats that aren’t character-delimited like this.

Can you provide more details?

And FYI, for readability, you can make code blocks with tags like [code]code blocks[/code], or just surround them with triple backticks like:

```
your code goes here

```

becomes:

`**  Write CPEG-format output file                                  **` 
`CPEG-OUT-REC              = ’ ’
CPEG-REC-CD               = 'C ’
CPEG-CONTRIB#             = ‘802364’
CPEG-BUS-CAT              = ‘1250’
*CPEG-EXTDT                = CPEG-A-EXT-DATE
CPEG-EXTDT                = WS-DATE
CPEG-EXTDT-MM               = WS-MM
CPEG-EXTDT-DD               = WS-DD
CPEG-EXTDT-CC               = ‘20’
CPEG-EXTDT-YY               = WS-YY`
etc.

You can also hide the whole thing inside a details block that won’t expand until someone clicks on it:

[details="Summary"]
This text will be hidden
[/details]
32

PS About Tableau, they were very popular about a decade ago, but not so much these days. They were eventually bought by another company (Salesforce), which a lot of smaller companies and tech companies don’t like.

The ETL space has also exploded since then, with a lot of major competitors in the cloud, and Python got drastically better and more powerful since then, becoming the default choice for a lot of teams now, especially the AI ones.

I don’t think it makes sense to pay for a Tableau license in your case:

A Tableau license would mean you’re jumping from one ancient, proprietary system to another old (not quite ancient), proprietary and expensive system. It means you’re locked in to their particular way of doing things, which is unique to them, and not a part of the greater ETL ecosystem, and it’s not an entirely transferable skillset to other systems. Salesforce is notorious for this, creating a proprietary system with extensive certifications that deliberately causes vendor lock-in.

An open, Python (or any other modern language) based system would have much better longevity, both in terms of your hiring pool (it’s super popular with the kids and the AI generation) and also in terms of the training data for future LLMs to easily read and port to whatever else comes in vogue a decade from now.

Even if the learning curve is higher for you initially, I think it would be a much more sound investment for your organization, long-term.

But I don’t know the full details of your needs and business requirements here. It may be that it’s such a specialized niche, with specialized hiring requirements, that maybe sticking to proprietary vendor-supported systems makes more sense (like if it’s generally easier for you to budget $1000 a year and can count on high-quality support from the vendor than to hire developers). It depends on your company culture and resources.

You said you’re a non-profit? Who do you serve? Is this something that you could potentially open-source and let the community help maintain over time, or is this an incredibly niche thing that nobody would want to bother with…?

33

While I’m completely on board with using Python as the replacement, the above is just not true. Tableau still has a HUGE market presence and is incredibly popular to this day. Your next paragraph makes me think that you consider it an ETL tool, which is not what Tableau is about. Data Prep could be considered a No-Code ETL tool perhaps, but it’s just a tiny fraction of Tableau’s ecosystem.

34

It’s not that Python has replaced Tableau, but that they have a lot more competition these days, and yes, not just in ETL but in analytics and visualizations, etc. (PowerBI, Looker, maybe things like Jupyter to some extent, in some fields).

To be clear, though: I’ve only ever been a light user of Tableau, so this isn’t a critique of its products but rather its business model and the risk of vendor lock-in, especially now that Salesforce owns them. They like to carve out little enterprise niches for themselves.

For the OP’s specific case of “transforming one kind of text file to another”, choosing Tableau is just going to lock them into that ecosytem.

35

PowerBI and Tableau are neck and neck in market share and together control their market. While Looker is often liked by the “cool kids”, it has a paltry presence compared to the two leaders.

Again, neither PowerBI nor Tableau are ETL tools in my opinion. They are data visualization tools. Tableau only came up because some “IT consultant” mentioned Tableau Prep to the OP. Not particularly germane to this thread, but I felt I needed to address a falsehood about Tableau’s popularity.

36

I mean… hasn’t it (declined in popularity)? Maybe it’s the specific bubble that I’m in (web dev), but I definitely remember Tableau being the cool kid on the block in the 2010s, but I haven’t heard it even mentioned in many, many years. And these days it has less market share than PowerBI (random sources 1, 2, 3), like less than 20% of the market now… whereas it used to be the market. It created that whole market and jumpstarted the industry, but it doesn’t really seem to have kept up and been able to maintain its dominance, while its competitors steadily gained shares…? Am I wrong?

Edit: Sorry, I don’t mean to say that Tableau is primarily an ETL product, only that using it solely for ETL would carry with it the downside of vendor lock-in. PowerBI would too, for that matter. Its popularity matters only insofar as “today this is still used by some businesses, but tomorrow it might not be” whereas a free and open system/language can still be used by anyone later on. IMO, Tableau isn’t a “safe” long-term guarantee the way that, say, Excel might be. I think it would be a poor choice as a domain-specific language for the OP’s use case of text file transformation.

37

Well, you could just ask instead of throwing your hands up in the air and declaring it to be in decipherable gibberish.

First of all, everything with a hash mark in front of it is non-executing; they’re all just comments.

The first two lines are importing libraries (os is a library that calls operating system functions and Path from pathlib allows Python to easily handle file path definitions without a bunch of OS dependent text formatting.

def sort_file is a function declaration; all of the listings in parenthesis are the parameters that are passed to the function that give it data or tell it how to function. It is getting file information from whatever file is specified (input_path), sorting them by the the sort_key criteria, and writes them out to the file specified in output_file.

def main() is the main function (the one that is called at the bottom by if name == “__main++”) executing the script and which calls all other functions as it progresses). code_number is (I’m guessing) some kind of job number which the input files and output file names are defined by (below in the “#File paths# section). os.makedirs creates new paths for filea_path and fileb_path, and then the program calls the sort function defined above.

This isn’t quite they way I would write this script but it is pretty straightforward. You basically just need to understand that main() executes first, calling other functions above it as necessary. (They don’t actually have to be above but that is long-standing programming flow), It doesn’t go top to bottom like an EASYTRIEVE or BASIC program but instead has one driving function executing and calling other functions as it comes to them.

Honestly, I would just clump all those separate fields together with a space delimiter and then gin up some algorithm that looks over obvious break points (the address number, a street/avenue/blvd/et cetera, city name and state code) to break them apart. Reformating things like # for APT is a basic substitution function. There is, in fact, a library on PyPi specifically for sussing out addresses and putting them in a standardized format although I can’t vouch for how robust it is. Regardless, this is a very solvable problem, and even if you can’t come up with a way that. will address every single record it should be pretty easy concoct an algorithm for sorting and formatting that will do the vast majority and throw an exception that you can address when it can’t.

I don’t have any opinions about Tableau except that it isn’t some purpose-created bit of wizardry to automagically solve your problem. It’s primary function is data analytics and visualization, which you obviously don’t need for this application. This would be a nice problem for some nerdy kid to get some spending cash to solve, or that you could pay a freelance Python developer an afternoon to gin up a working application with enough documentation to get you started.

Stranger

38

PowerBI has definitely surpassed Tableau in market share, but that is mostly price/bundle driven. Everyone with Office365 gets PowerBI automatically, so companies often make do. Once they start wanting to consolidate and share reporting, they find that the ecosystem just got a lot more expensive, but at that point they are locked in.

We should probably drop this hijack and get back to telling the OP that Tableau Prep is probably not the ideal solution for this.

39

Yeah, this might be a nice middle-ground. You don’t have to write the code yourself, just find a small dev agency or freelancer to do it for you. Ask them to focus on readability, write it in a simple, straightforward, procedural style, and thoroughly document/comment every major section — both for you to learn from and for any future maintainers to understand.

You don’t have to know anything about object-oriented programming. From what I can see of your existing code, it is pretty straightforward “move characters, substitute this for that”, etc., just a whole bunch of them. It’s the kind of stuff that your average programming student would learn in their first semester, not anything super fancy.

For what it’s worth, I’m also fairly confident this is a problem that LLMs can help solve for you (and pretty effectively teach you, line by line, along the way… just ask it to convert one section of your code at a time and explain what it’s doing and what the Python code means). That might be cheaper than trying to hire a developer. And by cheap, I mean anywhere from free to like $20. But be very sure to run test cases against it; they don’t always get the code right at the first (or even many) tries… but the same thing applies to any agency you hire, and especially freelancers. Test the heck out of any new version, whether it’s human-written Python, AI code, or a Tableau thing that a consultant writes for you.

Anyyyyyyhooow… I think the main benefit of using a more modern language (beyond long-term maintainability) would mostly be a readability improvement. It wouldn’t necessarily cause any increase in complexity. Your bazillions of if-statements could become more terse and readable, like these examples.

This kind of code doesn’t cause complexity (in any language), because primarily it is a matter of syntax (how the program “looks”) rather than logic (what it’s doing). The logic is simple, but the syntax is very verbose and pretty old-fashioned and kind of spreadsheet-like (reminds me of nested Excel formulas).

As a web developer, I’m probably a bit biased here, but I think you could also make the case that this would be better as a Javascript web app (because it’s easier to distribute that way; you can send any of the other companies or your own users to www.johnnys-transformer.com and they would just be able to upload the files and download them without needing to install anything). Python programs are a little trickier to distribute to your users, but not terribly so… we recently had another thread about this, and you can pretty easily distribute a Python app as a website too: Turning a Python app into a web app for easier distribution?

Yes, fair enough, sorry about that!

40

Hmm, address normalization is one of the hardest things I’ve ever had to try to do in my programming career, personally, but I’m also not very skilled as far as programmers go. I’m just a self-taught hack with a side interest in GIS and geocoding. Maybe there are good solutions I’m simply not aware of.

In our case, we ultimately just ended up sending the addresses to third-party APIs like https://www.smarty.com/ for validation and normalization. But we had clearly defined “address line 1 and 2” fields, not names & addresses mixed together in different parts of the same fields, like in the OP’s example.

I have no idea how you’d even begin to differentiate situations like that, when for example some restaurants might be named after the very street they’re on. Even as a human I can’t reliably tell apart what is a name and what is an address. It’s one thing to match an arbitrary string against a geodatabase (especially if you can limit it around a zip code or city/state); to try to parse data as messy as the OP’s example without an external reference source of real addresses would be quite the challenge, I think… it might be one of the rare cases where a local LLM would do a better job than a classical heuristics algorithm.

In any case, this isn’t a point we have to belabor to death… as you said, it’s absolutely something that can be solved later (if at all). It might not even be part of the OP’s requirements. If they’re not actually mailing anything or positioning things on a map, having accurate addresses and names might not be even be a necessary part of the automation. Maybe a human looks at it later and just figures it out as they go? Shrug. (Or even if they do mail it out, the USPS has its own processes for figuring out tricky addresses better than any simple script can!)