Validate an inventory export
Turn a fleet export into validated records and traceable rejection reasons.
An export is a starting point
An inventory tool may collect data from several systems. Empty values, duplicate identifiers and malformed numbers can arrive together. Validate the export before calculating totals or recommending changes. A row that cannot be interpreted should remain visible as a rejected record, not silently become a machine with zero memory.
This course uses a deliberately simple TSV format: tab-separated fields, one record per line, without embedded tabs or newlines. Awk retains empty tab-separated fields, unlike a shell read loop that can collapse adjacent whitespace separators. Check the field count first, then apply specific field rules in a documented order.
Identity comes before aggregation
An asset ID identifies one inventory record. Reserve it only after the row passes validation. Otherwise a malformed first row could prevent a later valid observation from being used. Once an ID is accepted, reject later occurrences rather than adding the same asset twice.
Keep the physical input line number and one reason for each rejection. This makes correction practical for whoever maintains the source export. The supplied files describe fictional systems; the browser guest is a practice workspace, not a live inventory of your Mac. Required scripts use Bash 3.2-compatible syntax and portable Unix commands.
awk -F '\t' 'NR>1 {printf "line %d: %d fields\n",NR,NF}' inventory.tsv
Put it into practice.
Validate inventory.tsv after its supplied header asset_id<TAB>hostname<TAB>role<TAB>memory_gib. Write valid.tsv with the same header and accepted rows in input order; write errors.tsv with header line<TAB>reason. Reject in this order: fields (not four); asset_id (not ^[A-Za-z0-9_-]+$); hostname (empty); role (not web, db or worker); memory_gib (not a canonical integer 1–1024, without leading zeros); duplicate (ID already accepted). A rejected row does not reserve its ID. Use physical line numbers and replace reports on every run.
See an explained solution
export LC_ALL=C
printf 'asset_id\thostname\trole\tmemory_gib\n' > valid.tsv
printf 'line\treason\n' > errors.tsv
awk -F '\t' 'NR>1 {
reason=""
if(NF!=4) reason="fields"
else if($1!~/^[A-Za-z0-9_-]+$/) reason="asset_id"
else if($2=="") reason="hostname"
else if($3!="web" && $3!="db" && $3!="worker") reason="role"
else if($4!~/^[1-9][0-9]*$/ || length($4)>4 || $4>1024) reason="memory_gib"
else if($1 in seen) reason="duplicate"
if(reason!="") printf "%d\t%s\n",NR,reason >> "errors.tsv"
else {seen[$1]=1;print $0 >> "valid.tsv"}
}' inventory.tsv
The script separates source validation from later operations. Its ordered rules retain a stable rejection reason, and reserving only valid IDs allows a corrected later row to be accepted without double-counting valid assets.