Generate Text From Templates, Scripts and CSV Data

The job has not changed since 2008: a template, a CSV with one row per device, one output file per row. The 2008 script (kept under As published) got the idea right and the shell details wrong in ways that bite the moment real data shows up: for i in $(cat file) splits on spaces as well as newlines, so a TACACS key with a space in it becomes two rows; unquoted sed -e s/X/$VALUE/g breaks on any value containing /; and a CSV saved from Excel carries \r at the end of every line, which ends up inside the last field. The rewrite below fixes all three and swaps sed for envsubst, which does the substitution without any quoting problems.

The template

Placeholders are shell-style variables. envsubst only touches the names you list, so a literal $ elsewhere in a Cisco banner or a password is left alone.

!
hostname $VAR_HOSTNAME
!
interface Vlan100
 ip address $VAR_IPADDR $VAR_NETMASK
 ip ospf message-digest-key 1 md5 $VAR_OSPF_KEY
!
tacacs-server host 1.1.1.1
tacacs-server key $VAR_TACACS_KEY
!

The data

Plain CSV, one header line, one row per device. Fields with spaces are fine; fields containing commas are not (this is a five-column read, not a CSV parser; for quoted fields use python3 -m csv or csvtool).

hostname,ip,mask,ospf,tacacs
switch-01,10.1.1.1,255.255.255.0,ospf-key-1,tacacs-key-1
switch-02,10.1.1.2,255.255.255.0,ospf-key-2,tacacs-key-2
switch-03,10.1.1.3,255.255.255.0,ospf-key-3,tacacs-key-3

The script

#!/bin/bash
# import-script.sh - one output file per CSV row; placeholders are $VAR_* in the template.
set -euo pipefail
IMPORT=./data/import-data.csv
TEMPLATE=./data/import-template.txt
mkdir -p output
tail -n +2 "$IMPORT" | sed 's/\r$//' | while IFS=, read -r VAR_HOSTNAME VAR_IPADDR VAR_NETMASK VAR_OSPF_KEY VAR_TACACS_KEY; do
  [ -z "$VAR_HOSTNAME" ] && continue
  export VAR_HOSTNAME VAR_IPADDR VAR_NETMASK VAR_OSPF_KEY VAR_TACACS_KEY
  envsubst '$VAR_HOSTNAME $VAR_IPADDR $VAR_NETMASK $VAR_OSPF_KEY $VAR_TACACS_KEY' < "$TEMPLATE" > "output/$VAR_HOSTNAME.txt"
  echo "wrote output/$VAR_HOSTNAME.txt"
done

Line by line: tail -n +2 skips the header; sed 's/\r$//' removes Windows line endings; IFS=, read -r splits each row on commas and nothing else, into the five variables; export makes them visible to envsubst; the quoted list after envsubst is the set of names it may replace; everything else in the template passes through untouched.

envsubst comes from the gettext-base package, which is installed on every Ubuntu. If it is missing on another distribution, the same loop works with sed as long as you quote the expression:

sed -e "s|\$VAR_HOSTNAME|$VAR_HOSTNAME|g" -e "s|\$VAR_IPADDR|$VAR_IPADDR|g" "$TEMPLATE"

(| as the delimiter keeps IP addresses and paths from colliding with the s/// syntax.)

Run it

mkdir -p data && cd data
curl -sO https://savvyadmin.com/downloads/import-script/import-template-v2.txt
curl -sO https://savvyadmin.com/downloads/import-script/import-data-v2.csv
mv import-template-v2.txt import-template.txt && mv import-data-v2.csv import-data.csv && cd ..
curl -sO https://savvyadmin.com/downloads/import-script/import-script-v2.sh && chmod +x import-script-v2.sh
./import-script-v2.sh

wrote output/switch-01.txt
wrote output/switch-02.txt
wrote output/switch-03.txt

cat output/switch-01.txt

!
hostname switch-01
!
interface Vlan100
 ip address 10.1.1.1 255.255.255.0
 ip ospf message-digest-key 1 md5 ospf-key-1
!
tacacs-server host 1.1.1.1
tacacs-server key tacacs-key-1
!

The 2008 files are still at their original addresses (import-script.sh, import-template.txt, import-data.txt) for anyone who linked to them.

When this stops being enough

Once the template needs loops or conditionals (a VLAN block per row of a second CSV, an interface section only for some devices) you have outgrown envsubst. Reach for Jinja2 at that point: pip install jinja2-cli gives you j2 template.j2 data.yaml, and Ansible’s template module is the same engine with inventory on top. The CSV-and-shell approach above is the right size for “a few hundred files, one row each”, and it will still be working in another eighteen years.

If you are working on a project requiring that you generate hundreds or thousands of similar but unique text files, you probably will be looking for some type of task automation process. For example, a recent project required that we configure hundreds of Cisco switch configuration files, each being uniquely individualized with IP addresses, OSPF configurations, random passwords, etc. We also needed to configure an import data file for over a thousand DHCP ranges, obviously not something we felt like doing by hand.

For tasks like these, the best tools of the trade are text file templates that represent what the finished product should look like, spreadsheets for basic math and to create import data in the form of CSV’s (comma-separated values), and GNU/Linux shell scripting to pull it all together.

The process can be summarized as follows:

1. Use a template text file and identify the parts you wish to customize with easy to recognize variables.

2. Generate unique data in the CSV format that will serve to populate the template.

3. Run a Linux shell script that performs a search and replace function against the template, and outputs new text files with the values obtained from the CSV file.

In the following example, we will work with a basic Cisco switch configuration file that is condensed for brevity. Notice, we clearly label text within the template that we will use to search for and replace with values obtained from the CSV data. These variables are VAR_HOSTNAME, VAR_IPADDR, VAR_NETMASK, VAR_OSPF_KEY, VAR_TACACS_KEY.

Sample Template: import-template.txt

!
hostname VAR_HOSTNAME
!
interface Vlan100
ip address VAR_IPADDR VAR_NETMASK
ip ospf message-digest-key 1 md5 VAR_OSPF_KEY
!
tacacs-server host 1.1.1.1
tacacs-server key VAR_TACACS_KEY
!

The CSV file in our example is organized as follows:
Field 1=”Host name”, Field 2=”IP address”, Field 3=”Subnet Mask”, Field 4=”OSPF key”, and Field 5=”TACACS key”

Sample Data CSV File: import-data.txt

switch-01,10.1.1.1,255.255.255.0,ospf-key-1,tacacs-key-1
switch-02,10.1.1.2,255.255.255.0,ospf-key-2,tacacs-key-2
switch-03,10.1.1.3,255.255.255.0,ospf-key-3,tacacs-key-3
switch-04,10.1.1.4,255.255.255.0,ospf-key-4,tacacs-key-4
switch-05,10.1.1.5,255.255.255.0,ospf-key-5,tacacs-key-5

Here’s how we pull all of this together using a Linux shell script. First, we define the location of our CSV import file. Next we run a “for loop” that reads the CSV line by line, assigning each data value to a variable name. Lastly, the script concatenates the template, replacing each defined search result with values from the CSV, and sends the output to individualized text files. These text files are appropriately named according the unique value, which in this case is the switches host name.

Import Shell Script: import-script.sh

#!/bin/bash
IMPORT="./data/import-data.txt"
TEMPLATE="./data/import-template.txt"
 
for i in `cat ${IMPORT}`
do
VAR_HOSTNAME=`echo $i | awk -F, '{print $1}'`
VAR_IPADDR=`echo $i | awk -F, '{print $2}'`
VAR_NETMASK=`echo $i | awk -F, '{print $3}'`
VAR_OSPF_KEY=`echo $i | awk -F, '{print $4}'`
VAR_TACACS_KEY=`echo $i | awk -F, '{print $5}'`
cat $TEMPLATE | sed -e s/VAR_HOSTNAME/$VAR_HOSTNAME/g 
-e s/VAR_IPADDR/$VAR_IPADDR/g 
-e s/VAR_NETMASK/$VAR_NETMASK/g 
-e s/VAR_OSPF_KEY/$VAR_OSPF_KEY/g 
-e s/VAR_TACACS_KEY/$VAR_TACACS_KEY/g 
| tee ./output/$VAR_HOSTNAME.txt 1>/dev/null
done

Step By Step:

To follow along, you can download the above mentioned files, or copy and paste them from the text above. Place the import-data.txt and import-template.txt files in a new directory called “data”, create a directory called “output”, and make the import-script.sh file executable. The final step of course is to run the import script. Here is a step by step to get you going.

wget http://savvyadmin.com/download/import-script/import-script.sh
wget http://savvyadmin.com/download/import-script/import-data.txt
wget http://savvyadmin.com/download/import-script/import-template.txt
mkdir data output
mv ./import-template.txt ./import-data.txt ./data/
chmod 755 ./import-script.sh
./import-script.sh

After you run the script successfully, list the “output” directory to see the newly created files.

ls -l output/
total 20
-rw-r--r-- 1 gmendoza gmendoza 184 2008-03-06 21:33 switch-01.txt
-rw-r--r-- 1 gmendoza gmendoza 184 2008-03-06 21:33 switch-02.txt
-rw-r--r-- 1 gmendoza gmendoza 184 2008-03-06 21:33 switch-03.txt
-rw-r--r-- 1 gmendoza gmendoza 184 2008-03-06 21:33 switch-04.txt
-rw-r--r-- 1 gmendoza gmendoza 184 2008-03-06 21:33 switch-05.txt

The contents of the first file in the example can be viewed as follows. Note that all the variables from the template have been replaced appropriately by the data from the CSV.

cat output/switch-01.txt
!
hostname switch-01
!
interface Vlan100
ip address 10.1.1.1 255.255.255.0
ip ospf message-digest-key 1 md5 ospf-key-1
!
tacacs-server host 1.1.1.1
tacacs-server key tacacs-key-1
!

I certainly hope this gives you some great ideas on how to make effective use of Linux scripts to make large amounts of work into manageable tasks. Comments and questions are welcome.