QLOF.TXT Sept 27,1992 PROGRAM: QLOF Report data extractor VER:2.7 DESCR:Extract and manipulate data from printed reports. AUTHOR: FAST ENTRY PROGRAMMING Walter Killeen 210 S. Lopez St. New Orleans, LA 70119 (504)827-5989 Compusrv:71511,35 Requires: DOS3.1 Monitor: CGA CPU: 8088 Registration: $65 Customization quoted upon request. QUICK L-O-F HELP This program will translate Line Oriented Files to: a DIF file for Spreadsheets (.DIF) a MERGE file for Single Char Delimited (.MRG) a LUCID file for Three Space Delimited (.LTX) an ASCII file for Comma Delimited (.ASC) Proper call is: QLOF InputFiles[no ext] [HelpfileName] If HelpfileName is not specified, QLOF.INI is used Wild card (*) is allowed in both file specifications Quick help can be gotten by typing QLOF by itself. If Wild cards are used, it is best to define the extensions: QLOF *.TXT *.INI will prompt for each file. When QLOF is called, it will translate the instructions found in the specified Initialization file and act on the Input file(s) to produce an Output file of the type specified in HelpFileName. The Output file will be located in the same directory as the Input file with the extension as noted above. QLOF is capable of dealing with line oriented; space delimited files up to 255 characters long. It may receive its input from up to three such files simulta neously. A companion program is available to registered user to split up non-line oriented files into the format that QLOF needs. The output file may contai n up to 60 fields which may be grabbed from overlapping or immediately adjacen t columns in any order. (The output order is determined by the order that the information is listed in the INI file.) In keeping with the FAST ENTRY tradition of high speed, QLOF does its job at 7000 characters per second on an 8088! OK, if you enable all the bells and whi stles, it is slower; it has been clocked as slow as 3000 characters per second on a bad day. QLOF is also capable of performing many operations on the information as it processes it. For example: o Converting all Upper case lettering to Mixed case. o Converting Mixed case lettering to Lower case. Page 1 QLOF.TXT o Converting Mixed case lettering to Upper case. o Making Phone Numbers look like Phone numbers (504)123-1234 o Making Social Security Numbers look like it. 123-23-1234 o Calculating a User ID code which has over 98% chance of being unique for each user. (Lists can be cross referenced for duplicates) o Cross referencing the input on up to four fields to guarantee that the same information never goes through the system twice. (Depending on options chosen, Some of the fields can be similar and still have the information pass through.) If more than one file is used, all files must have corresponding information on the same line of each file and must have a KEY field which may be located in differing places in each file. (The KEY will be used to verify that the information belongs together.) In addition , QLOF is capable of taking a single file that is actually made up of several reports (separated by the phrase: START REPORT) and splitting the file before it begins. If a file is to be split (and the split is successful), the origina l file will be removed from the disk before translation begins to help avoid t he possibility that you will run out of disk space. OUTPUT FORMATS DIF The DIF format is a standardized data information exchange format. QLOF supports up to 9999 records per translation in this format. At this time, QLOF doesn't support field titles; but it does support number and text t ypes. Dates are treated text (a property of DIF). MERGE Perhaps the most powerful output format of QLOF is the .MRG output. If no parameters are given to this file format MERGE will create variable length records. (All leading and trailing spaces are stripped from individual fields.) o Each record is separated from the next by a CRLF. o Each field will be separated from the next by a "\". o Embedded "\" will be replaced by "/". This is probably the most compact way to store large quantities of information especially if many of the fields will end up blank. As an added bonus, the file is easily readable by most word processors (provided the overall record length doesn't get too long.) MERGE OPTIONS The .MRG option takes the following options: Page 2 QLOF.TXT o Delimit: Up to a four characters The character immediately following MRG is used as a marker b etween the rest of the options Ex:MRG;","; insuates that "," will delineate the fields. o AltDelimit: Up to four characters (Must be second) These are the characters that will replace Delimit if it is found inside a field. Can be set to nothing. o LeadDelimit: True or False If set true and Delimit is one character, that character will begin each line. Also, in the special case where Delimit is 3 characters and t he first character is the same as the last, only the last character will be used to lead Ex: MRG;"|";*|*;TRUE; would produce output that looks like: "First Name"|"Last Name"|"Address... As an aside, MERGE format was designed to be read by most text editors. Therefore, to keep the line oriented format from growing too long, if the last characters of a line are "\&" (no quotation marks) that field will continue to the next line. (In this instance, even if LeadDelimit is true, the leading character of the next line will NOT be a "\" .) Further, the MERGE format supports the concept of hard carriage returns for multi-line fields. With this in mind, the letter ^Q in the middle of a line designates a hard carriage return. The above special cases apply ONLY if MRG has no options enabled. INI FILES EXPLAINED To keep program complexity down (and therefore speed up) QLOF has been designed to be essentially non-interactive. It only asks for help when an ambiguous situation arises. To that end, it asks all of its questions by reading an INI (initialization) file on startup. As long as it understands everything that you put there it will display what it thinks that you asked it to do and ask if everything is OK. On the other hand, if it finds a mistake in the INI file, QLOF will inform you as best it can, why it thinks the file is in error and what line the error is on. As you look through the example INI files, you will probably agree that you wouldn't want to answer that many questions each time the program started. Since many agencies repeatedly get the same reports from the same several sources, most find that it is easiest to create one INI file that asks for what you want to know and then make separate copies for each source of information. After changing read-in locations for each source using your favorite text editor, you now have a startup file for each source. It may not be horribly elegant, but it works... perhaps in the next version, I will get around to one of those fancy "highlight the info you want and tell me where you want it" interfaces. Since I generally use Qedit whose multi-window multi-file abilities allow me to open the INI file and sample report info at the same time, that 'feature' may be a while. Still, if enough of you ask... QLOF.INI LINE BY LINE Most of the lines below should be fairly self explanatory (the ##: at the beginning of each line is not actually in the file). Page 3 QLOF.TXT Although not required, the comments in the actual sample files make the file considerably easier to follow . One of the most common mistakes will be to accidentally add or delete a line so comments help you to figure what is missing. The file can actually be divided into four parts: o a header (always 13 lines) o Cross Check area (lines = six times the #specified in line 3 of header) o Actual field area (lines = six times the Number of fields specified in line 2 of header) o TO-DO area (allows certain manipulation of info before storage) -----------------------------TOP of INI file------------------------------- 01: ADP to Q&A Owners W Check 0:17:54 11/26/1990 02: 46 ; Num Fields VERY IMPORTANT (determines where TODO area starts) 03: 3 ; Num Files (file name should be seven chars or less) 04: FALSE ; Fill Empty fields? (Case is important) 05: TRUE ; Break File First? 06: TRUE ; Use Dups file? 07: FALSE ; Put Dups in Log file? 08: TRUE ; Use Index file? 09: 3 ; Number Of indexes (next line is path to indexes) 10: D:\Q&A\Qmaster 11: .TXT ; Main file Ext (.TXT means treat contents as std) 12: .DIF ; Out File Ext (NONE means no output) 13: ------ End of Header----- this line can actually contain anything. 01: CheckVal[1] ;name of variable 02: 1 ; from which file do we get our cross referance 03: 1 ; Beginning read position 04: 6 ; Length of info to read 05: C ; Typ: C=CheckVal T=Text(no translation) U=Force Upper 06: ;Blank line for readability 01: CheckVal[2] 02: 2 ; This will be read from the second file 03: 1 ; Begin 04: 6 ; Length 05: C ; Typ M=Force mixed case P=Phone S=SS number N=Number D=Date 06: CheckVal[3] 3 ; from which 1 ; Begin 6 ; Length C ; Typ ------- Beginning of Fields---------- (this line usually blank) 01: @SlsPer 1 ;@ as leading char means Field name can be used 02: 0 ; from which (0 means that it will be filled in by calculation) 03: 0 ; Begin 04: 0 ; Length (actually can be up to 80 chars) Page 4 QLOF.TXT 05: T ; Typ 06: 01: @Team 2 02: 0 ; from which 03: 0 ; Begin 04: 0 ; Length 05: T ; Typ . . . 01: @CusId 44|2 02: 0 ; from NOTE: the | above designates this as an index field(2) 03: 0 ; Begin 04: 10 ; Length 05: U ; Typ 06: 01: Hphone 45 02: 2 ; from which 03: 87 ; Begin 04: 13 ; Length 05: P(504) ; Typ NOTE: (504)designates Local Area code 06: 01: Mthly Pay 46 02: 3 ; from which 03: 79 ; Begin 04: 8 ; Length 05: N ; Typ (numbers will have all non-number characters removed) ---------END of Fields--------- Next line is TODO ------ FL/FP/CU/OD/NS/MO/FD/ ; the above line, determines which of the following will occur ; the lines below are for information only, they are never read FLame {Parses "LastName, FirstName} FPhone {Removes Left over Areacode} CUsid {Generates ID Code} TItle {Title from Code} SOurce {From file and line number} ODate {If Blank fills in date dd mmm yy} FDear {First name Dear "John & Mary"} LDear {Last Name Dear "Mr & Mrs Smith"} NSlsper {Puts a "N" In SlsPer} MOdnum {Model Description from code} X1look {Special External lookup 1 (not used yet)} ===================== END OF FILE ======================== In the above example, FL stands for FLast name; FP means FPhone etc... WHAT CAN BE NONE Most of the real power of QLOF and the features that distinguish it from other packages is what you can do with the information between the time you read it from the input files and write it to the output file: FLast Required fields: Company, @Fname, @Lname, For this operation the raw information is read into the field @Fname the name is then broken into its components. For the purposes of this function, the information is assumed to be in "LastName, FirstName Minital" format. A company name has no comma Page 5 QLOF.TXT and will therefore be moved in its entirety to the @Lname field. The phrase "Company Name" will be placed in the @Fname field. If the comma is found, everything before it will be moved to the @Lname field. At this point @Fname will be scanned for " and " or " &". If it contains either of the above, it will be left alone. If a single letter and period are found, the period will be stripped and the single letter will be moved to the Minit field. If the Minit field was not defined, the middle initial stays where it is. The routine is smart enough to get "Jr.", "Sr.", and "III" where it belongs (at the end of the last name). FPhone Required fields: Any field of type "P" This is more like a flag than anything else. If set then local phone numbers will have the leading area code stripped (but replaced with spaces in some output formats). So how do I tell the program what the local area code is? Look at field 45 above, notice that an area code (in paretheses) immediately follows the type code. As long as THE LAST field of type "P" contains it, that will be assumed to be the local area code. This is an all or none situation, you can not have two phone fields one of which strips the area code and the other that doesn't... So make up your mind and stick with it. CUsid Required fields: @Lname, @Address, @Zpcode, @CusId Perhaps the most compelling reason to use QLOF is to correlate information from disparate sources. If you set /CU/ in your TO-DO line, QLOF will automatically scan the @Lname and @Address to generate a Customer ID that it stores in the @CusId field. To start, QLOF grabs: o the first three letters from @Lname and adds o the first five letters of the Street name (actually the first five letters following the first space in the address) o It then grabs the last two digits of the street number so: Jon Smith at 1225 Glendale St. would turn out like "SmiGle25". The routine is even smart enough to treat Post office boxes differently. Since it is highly likely that a proper zip code exists in this instance, we grab o First three letters of Last name o First three numbers of PO box number and o Last two digits of zip code. In addition, if Cross checking is on, QLOF can actually store the contents of the required fields to use in double checking on the uniquene ss of the number generated. (See | comment above.) Page 6 QLOF.TXT TItle Required fields: @Titl, Titles.def Many mailing list companies actually store their title (Mr. Mrs. Ms. etc) as a number. This routine reads @Titl and replaces the contents wi th a matching line number in Titles.def (located in the same directory as the INI file). SOurce Required field: @Src This very helpful text field allows you to record the name of the file and line number that the information came from. HINT: Set @Src as one of your indexed fields for cross checking ODate Required field: @ODate ODate stands for Origin date and will generate the current date and place the contents into the above field. If you use @Src and @ODate, you can easily trace the source of your data. FDear Required fields: @FName, @Dear This routine creates the field that would commonly be placed after Dear in a letter. It tries to strip any middle initials from @FName. For instance, if @Fname contains: "George L. & Susie R." then @Dear would contain: "George & Susie" LDear Required fields: @LName, @Titl, @Dear Like the function above, LDear creates the "Dear" used in more formal letters. I will let you figure out how it works. NSlsper Required field: @SlsPer This function simply puts an "N" in @SlsPer. The usual reason for this is for later assignment to someone. While this is outside the current scope of this program, if enough of you ask, I might create an automatic assignment routine. TALK to me, I'll listen, The rest of the routines involve looking up information from external files for code matching. If you need this sort of thing let me know and we can work something out. Custom programming is what I do for a living, Making this program work expressly for you would make both of us very happy. Study the INI file examples for comments. Don't forget, registration of shareware is NOT optional, if you use it and like it, I should be compensated for my trouble. If you want this product to get better I will need encouragement. So what does registration get you? o Printed full documentation, this mini document contains just enough info to see if the program meets your needs. o The latest version of the software or the next release if this is the latest version. o TO-LOF.EXE a companion program that translates space delimited information into a format that QLOF understands.. Among other things TO-LOF reads Dbase files as well as any other file that stores its records in fixed length format. Page 7