A S C t o D B F by Dick Koster Ver 2.6 1992 ASCtoDBF is a utility to convert a text report file into an ASCII delimited text file suitable for importation into other programs such as Lotus 123, dBase III+ / IV, etc. THE DESCRIPTOR TABLES: ---------------------- Before processing the input text file, three tables are created by the user within the program. 1. Exclusion Table - Not Required 2. Group Id Table - Not Required 3. Fielding Table - Required (else blank output!) The EXCLUSION TABLE contains up to 12 text sequences that you wish omitted from the output. These are usually report headers, summary lines, page numbers, etc. (Blank lines are automatically omitted.) The table from the example at the end of this text is as follows: 1. part# 2. ----- 3. sparkie 4. . . . 12. The GROUP ID TABLE contains up to 10 entries, each one including a text sequence, starting location, length, and type (character or numeric). Sometimes a report may have a header line for each major classification of ensuing data. These could be a customer name, customer number, date, salesman, etc. (see sample report below). This data is NOT repeated with each individual line item that follows. However, this "header" information is pertinent to grouping its associated data within a database program. These key header items can be searched for. When found, other information on the same line defined by the start/length/type criteria can be extracted and prefixed to the output data as a unique field. (see sample output below). No. Id Sequence Text Start Length Type 1. CUSTOMER: 12 22 C 2. DATE: 41 8 C 3. NUMBER: 9 5 N 4. 0 0 . . . 10. 0 0 The FIELDING TABLE contains up to 20 entries that tell the program which columns of the remaining input file to include as unique fields in the output. The starting column position, its length, and field type (character or numeric) is assigned...as well as a field descriptor for user documentation purposes only. The order that these items appear in the table dictate the order in the output delimited ascii file. Line items may be changed, inserted, or removed from the table during initial entry (or later during table maintenance to an existing parameter file.) A sample Fielding Table: Fld Description Start Length Type 1. INVOICE NO. 1 5 N 2. PART NO. 15 5 C 3. DESCRIPTION 25 16 C 4. QUANTITY 45 8 N 5. PRICE 55 13 N 6. 0 0 . . . 20. 0 0 THE PARAMETER FILE: ------------------- When the program is run, it first asks for a parameter filename. This is the file in which the abovementioned tables are stored so they may be used again in both manual and automatic processing mode (see below). If the file does not exist, it will create a new empty one. If it exists, it will load the tables for processing or editing. Any changes will be updated into the parameter file upon program exit. Two other items are also stored in the parameter file; the input filename, and the output filename. These two items are only used if a command line parameter (see below) requests a specific parameter file. This places the program into "automatic" mode and no user prompts are issued. Hence, once a parameter file is created to edit a specific report, it may be used in a batch file to automatically process reoccurring reports. COMMAND LINE OPTIONS: --------------------- At startup, a short display sequence illustrates a "sample" input and output file format. This may be bypassed by including a quickload command line parameter. (This parameter will be forwarded to you when the program is registered for the paultry sum of $5.00) However, it is automatically deactivated in automatic mode processing. ASCtoDBF ?? (where ?? is the quickload code) If you are running a monochrome monitor, and the cursor or highlighting features seem to be inoperable, the video sensing software may be misinterpreting your system. To force the program into "monochrome" mode, include a /M parameter on the command line. Likewise, to force "color" mode, include a /C on the command line. ASCtoDBF /M or ASCtoDBF /c Once the parameter file is established, it can be called to implement "automatic" mode processing. This is accomplished by placing a /P=ParamName switch on the command line, where "ParamName" is the parameter filename: ASCtoDBF /p=b:\subdir\DAILY.FIL /m When the above line is executed, the program will read the parameter file called "DAILY.FIL" on floppy drive B: in subdirectory "\subdir" which the user has previously created. The internally specified input and output text filenames are used as defaults. The short format display at the beginning of the program is turned off automatically, and the video mode is forced to monochrome. (1) The order of command line switches is irrelevant. (2) Device/path names supported for all files. PRINT OPTION: ------------- Before processing the input file (in manual mode), the user is prompted for an output device/filename on which to print the contents of the parameter file. To skip this option, merely depress the ENTER key at this prompt. Hard drive filenames, floppy drives, LPT1:, LPT2:, PRN, etc. are supported for this listing. IMPORTING INTO DBASE III+ ------------------------- Once the ascii delimited file has been made, create the structure accordingly in dBase. You may also position the items in Table 2 to correspond to an existing dBase structure. With the database open, enter the following line in dBase at the dot prompt: APPEND FROM filename DELIMITED WITH " This will load the delimited ascii text file into the dBase database file. -------------------------------------------------------------------------- SAMPLE INPUT TEXT -------------------------------------------------------------------------- J.J. Sparkie Corp Customer: Sears, Inc. Date: 11/23/92 Number: 45587 INVOICE# PART# DESCRIPTION QUANTITY PRICE -------- ----- ---------------- -------- -------- 18837 2A341 Table Saw 10 $5,867.27 18838 1-400 Paint Sprayer 200 $3,214.16 Customer: Grossman's Date: 11/23/92 Number: 21200 INVOICE# PART# DESCRIPTION QUANTITY PRICE -------- ----- ---------------- -------- -------- 34122 A4427 Nails 12,000 $98.99 45000 45555 Screws 100,000 $877.45 Customer: Ajax Lumber Date: 12/25/92 Number: 80174 INVOICE# PART# DESCRIPTION QUANTITY PRICE -------- ----- ---------------- -------- -------- 00872 5600G Plywood 50 $750.00 00873 1L277 Paint 300 $3,000.00 Customer: Date: Number: INVOICE# PART# DESCRIPTION QUANTITY PRICE -------- ----- ---------------- -------- -------- 11211 Road Service 1 $70.00 Phone Call $4.50 45400 NoChg Loaner 1 n/a Customer: Sommerville Supply Date: 10/14/91 Number: 12345 INVOICE# PART# DESCRIPTION QUANTITY PRICE -------- ----- ---------------- -------- -------- 99001 C-001 Windows 10 $-623.18 99002 C-002 Doors 3 $733.20- -------------------------------------------------------------------------- SAMPLE OUTPUT ASCII TEXT -------------------------------------------------------------------------- "SEARS, INC.","11/23/92",45587,18837,"2A341","Table Saw",10,5867.27 "SEARS, INC.","11/23/92",45587,18838,"1-400","Paint Sprayer",200,3214.16 "GROSSMAN'S","11/23/92",21200,34122,"A4427","Nails",12000,98.99 "GROSSMAN'S","11/23/92",21200,45000,"45555","Screws",100000,877.45 "AJAX LUMBER","12/25/92",80174,00872,"5600G","Plywood",50,750.00 "AJAX LUMBER","12/25/92",80174,00873,"1L277","Paint",300,3000.00 "","",0,11211,"","Road Service",1,70.00 "","",0,0,"","Phone Call",0,4.50 "","",0,45400,"NoChg","Loaner",1,0 "SOMMERVILLE SUPPLY","10/14/91",12345,99001,"C-001","Windows",10,-623.18 "SOMMERVILLE SUPPLY","10/14/91",12345,99002,"C-002","Doors",3,733.20- ----------------------------------------------------------------------- For questions, suggestions, or registration I can be notified at: Dick Koster 35 Guinevere Road N. Easton, MA 02356 CompuServe (72627,1670)