For a full list of BASHing data blog posts, see the index page.     RSS

48 sea levels and a trope for your terminal

The messiest dataset I've ever audited included a field that illustrates a key rule of Fussy Database Management: Never let users enter free-text data in a field unless absolutely necessary.

The field in question was for elevations and it contained 48 different strings that all meant "sea level":


Another 39 strings meant "near sea level":


How to bulk-replace all 48 sea levels with "0 m a.s.l." (for example)? One way would be to save those unique entries in a file (I called the file "sl1"; see first screenshot, above), then store them in an AWK array. When AWK finds one of those entries in the elevation field (field 27 in "table"), it replaces the entry:

awk 'BEGIN {FS=OFS="\t"} FNR==NR {a[$0]; next} $27 in a {$27="0 m a.s.l."} 1' sl1 table > newtable


In movies and on TV over the past 25 years or so, "ACCESS DENIED" usually pops up when the keyboard user fails in a login attempt. I used to think this trope was pure Americana, but you can find it in Australian productions as well (below).


A still from the 2016 Jack Irish TV series, set in Melbourne. Maybe the TV producers were showing here a possible replacement for the ancient LEAP database used by Victoria Police.

Obviously we need this important message in our terminals, so here it is, obfuscated. Use it double-quoted with echo -e or with printf (add "\n" at the end) for little-visited if/else conditions in your scripts.



Last update: 2020-01-13
The blog posts on this website are licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License