Example Script 5: Multirecord Type Hierarchical Reports
Sometimes data in a report is structured in a hierarchy that can best be exported as more than one type of record. This data can then be exported into more than one table in a relational database, or into a hierarchical multirecord type export format such as XML.
The source file in this example contains simple fixed location veterinarian data in a three-level hierarchical form. There is a section with information pertaining to the pet owner, one or more sections under each pet owner with information pertaining to the pet, and zero or more sections under each pet with information pertaining to that pet’s vet visits. The example script structures each of these three different types of data into three different record types with any key fields necessary to maintain the proper relationship of owner to pet to visit. If these records were exported as three separate tables in a relational database, the account number and pet name fields would be the ones used to link the tables.
Input Source Files
Parmer Lane Animal Hospital
Quarterly Client Report
April 1, 1999
1101-01
Robertson
Linda
143 Patterson Place
Austin TX 78759
===============================================================
PET TYPE NAME AGE SEX COLOR
DATE DIAGNOSIS SERVICE
===============================================================
DSH-C Shiva 4 F Gray Tabby
02/18/99 N/A Annual Shots
03/02/99 Plaque Build Up Sedation, Teeth Cleaning
LAB Blitzer 2 M Golden
01/06/99 Lacerated Rt Front Paw Clean, Exam, Antibiotic RX
02/14/99 Hip Evaluation X-Rays
02/26/99 Follow-up on Hip Eval. Review Evaluation
CXL SCRIPT
Show/Hide Code Comments
#!djrr
# Hierarchical Multiple Record Type
BEGIN{firstaccept=1;}
# The account number line always contains 4 digits followed by a dash followed by two more digits.
/^[0-9][0-9][0-9][0-9]-[0-9][0-9]/ {account = $0(1 7);
acctline = NR;
}
# Address lines will always be a fixed distance from the line with the account number.
# The first output record is skipped as it finds data on lines 2, 3, 4, and 5 of the report rather than the lines that are 2, 3, 4, and 5 lines down from the account line.
# CSZ line is the end of the first record type.
NR == acctline + 2 {lastname = trim($0(1 30));}
NR == acctline + 3 {firstname = trim($0(1 30));}
NR == acctline + 4 {address = trim($0(1 60));}
NR == acctline + 5 {city = trim($0(1 21));
state = $0(22 23);
zip = $0(40 50);
if (firstaccept != 1)
accept ("PersonInfo")
<"AcctNo"><7>account,
<"LName"><30>lastname,
<"FName"><30>firstname,
<"Address"><30>address,
<"City"><20>city,
<"State"><2>state,
<"Zip"><10>zip;
firstaccept = 0;
}
# Pet information is all on a single line. The account number can be used as a key to associate the record types.
$0(26 26) ~ /[0-9]/{pettype = $0(1 8);
petname = $0(13 25);
petage = $0(26 32);
petsex = $0(34 34);
petcolor = $0(42 68);
if (firstaccept != 1)
accept ("AnimalInfo")
<"AcctNo"><7>account,
<"PetName"><12>petname,
<"PetType"><8>pettype,
<"PetAge"><6>petage,
<"PetSex"><1>petsex,
<"PetColor"><26>petcolor;
}
# Information about each visit a pet had is on another line. Both the person's account number and the pet's name are needed as keys to maintain the data's relationships.
$0(7 7) ~ /\// && $0(10 10) ~ /\//{visitdate = $0(5 12);
diagnosis = $0(17 40);
service = $0(42 75);
if (firstaccept != 1)
accept ("VisitInfo")
<"AcctNo"><7>account,
<"PetName"><12>petname,
<"VisitDate"><8>visitdate,
<"Diagnosis"><23>diagnosis,
<"Service"><33>service;
}
Output Records
Output Record 1
Output Record 2
Output Record 3
Output Record 4
Output Record 5
Note: This output could go directly into a multi-record type hierarchical data file such as XML. It is far more likely to be exported in the form below into a relational database. The relationships would be re-established using the key fields, AcctNo and PetName.
Output Table 1
Output Table 2
Output Table 3
Last modified date: 12/03/2024