User Guide > Using Content Extraction Language > Script Examples > Example Script 5: Multirecord Type Hierarchical Reports
Was this helpful?
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
 
AcctNo
Date
LastName
FirstName
Address
City
Sate
Zip
1101-01
April 1, 1999
Robertson
Linda
143 Patterson Place
Austin
TX
78759
Output Record 2
 
AccNo
PetName
PetType
PetAge
PetSex
PetColor
1101-01
Shiva
DSH-C
4
F
Gray Tabby
Output Record 3
 
AccNo
PetName
VisitDate
Diagnosis
Service
1101-01
 
Shiva
02/18/99
N/A
Annual Shots
1101-01
Shiva
03/02/99
 
Plaque Build Up
Sedation, Teeth Cleaning
Output Record 4
 
AccNo
PetName
PetType
PetAge
PetSex
PetColor
1101-01
Blitzer
LAB
2
M
Golden
Output Record 5
 
AccNo
PetName
VisitDate
Diagnosis
Service
1101-01
Blitzer
01/06/99
Lacerated Rt Front Paw
Clean, Exam, Antibiotic RX
1101-01
Blitzer
02/14/99
Hip Evaluation
X-Rays
1101-01
Blitzer
02/26/99
Follow-up on Hip Eval
Review Evaluatio
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
 
AccNo
Date
LastName
FirstName
Address
City
State
Zip
1101-01
April 1, 1999
Robertson
Linda
143 Patterson Place
Austin
TX
78759
Output Table 2
 
AccNo
PetName
PetType
PetAge
PetSex
PetColor
1101-01
Shiva
DSH-C
4
F
Gray Tabby
1101-01
Blitzer
LAB
2
M
Golden
Output Table 3
 
AccNo
PetName
VisitDate
Diagnosis
Service
1101-01
Shiva
02/18/99
N/A
Annual Shots
1101-01
Shiva
03/02/99
Plaque Build Up
Sedation, Teeth Cleaning
1101-01
Blitzer
01/06/99
Lacerated Rt Front Paw
Clean, Exam, Antibiotic RX
1101-01
Blitzer
02/14/99
Hip Evaluation
X-Rays
1101-01
Blitzer
02/26/99
Follow-up on Hip Eval
Review Evaluation
Last modified date: 08/02/2023