Generate Text From Templates, Scripts and CSV Data

Posted by admin on March 6, 2008 under Tech Tips | 11 Comments to Read

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

interface Vlan100
ip ospf message-digest-key 1 md5 VAR_OSPF_KEY
tacacs-server host
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


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:

for i in `cat ${IMPORT}`
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}'`
| tee ./output/$VAR_HOSTNAME.txt 1>/dev/null

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 file executable. The final step of course is to run the import script. Here is a step by step to get you going.

mkdir data output
mv ./import-template.txt ./import-data.txt ./data/
chmod 755 ./

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
ip ospf message-digest-key 1 md5 ospf-key-1
tacacs-server host
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.

Be Sociable, Share!


  • blacky said,

    If the number of variables needed increases, the sed solution can
    become quite unwieldy. Also, having different sets of defined
    variables isn’t quite as easy. Finally, if you have many switches
    (and many admins), it may be easier to have one file per switch
    to facilitate the use of a version control system. To this end,
    using the C preprocessor (or a similar macro system) may be

    For example:

    $ cat import-template.txt
    hostname HOSTNAME
    interface Vlan100
    ip address IPADDR NETMASK
    ip ospf message-digest-key 1 md5 OSPF_KEY
    tacacs-server host
    tacacs-server key TACACS_KEY

    $ cat switches/switch-01
    #define HOSTNAME switch-01
    #define IPADDR
    #define NETMASK
    #define OSPF_KEY ospf-key-1
    #define TACACS_KEY tacacs-key-1
    $ cpp -P -include switches/switch-01 import-template.txt

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

  • gmendoza said,

    Hey there, Blacky. Thanks for contributing again.

    The scenario I had in mind was purely for short-term time savings through bulk processing. The whole point of using a CSV is that it’s easy to import and parse thousands of records, as each iteration of the for-loop only has to process a single line to obtain the definitions separated by a delimiter. For this type of task, creating a CSV is easier to generate than cpp precompiler directives for subsitution, because the latter as you pointed out requires not only multiple lines for the definitions, but multiple files to serve as the import data.

    However, what I really like about your suggestion is that if the intention is to maintain a version control system, individualizing the import data as a template of it’s own would be quite beneficial! Plus, you have the added benefit of simply being able to generate a single configuration from your cpp template, rather than having to create a new CSV for the job.

    Now of course, I would still recommend using the method mentioned in the article to both generate and populate the cpp templates to get things started. But for continual maintenance of such text, your suggestion is certainly one that readers should look into.

    Thanks again.

  • Larry said,

    Sweet scripts, not that it’s much use to me.

  • Herouth said,

    Here is how I would do the loop, which I think is more efficient (not invoking awk) – but requires that the import file does not have any empty fields:

    exec 4<“$IMPORT”


    # sed part remains unchanged


    By the way, why are you using tee if you’re not doing anything with standard output?

  • Herouth said,

    Oops, forgot the crucial part above: read -u 4

  • gmendoza said,

    @Herouth, Re: using tee…

    Meh… that’s actually kind of funny. It was probably something left over from something else I was doing. It could easily be replaced with: 1>>./output/$VAR_HOSTNAME.txt

  • Laz said,

    This has been extremely useful for both routers and switches. I had to configure about 45 devices, with this I was able to do it in about an hour after making the CSV file. I have one question though ( as I am a new to linux) what is “1>>./output/$VAR_HOSTNAME.txt” mean?

    Thanks again!

  • gmendoza said,

    1>file.txt sends output to a text file called “file.txt”.
    1>>file.txt sends output to a text file called “file.txt”, but appends if it already exists.

    2>file.txt sends errors to a text file called “file.txt”.
    2>>file.txt sends errors to a text file called “file.txt”, but appends if it already exists.

    Here’s a decent tutorial on the subject.

    I’m really glad this was able to help you.

  • Marco said,

    Improved version. The original script breaks if there are white spaces in the fields.


    # for i in `cat ${IMPORT}`

    cat $IMPORT |
    while read i

    # echo $i # debug

    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}’`
    | tee ./output/”$VAR_HOSTNAME.txt” 1>/dev/null

  • Celal Dikici said,

    The solution/s above is good for a couple of files. But if a csv file contains e.g. 3000 records, working line for line may slow.

    How can this be improved? I mean:
    the vars in template file shall be the same names as the field titles in the csv file.

    ip address VAR_IPADDR

    …cut 3000+ lines…

    the call like
    csvreplace template.txt <names.csv
    (or however) should now generate the output file at one go; not processing line by line.

    any awk/perl/ or xyz-script ideas?


  • j4d said,

    gmendoza, thanks for this. I am a newb to scripting and this has made my life somewhat easier. Last question, what if I wanted to use /mask notation instead of the 255.255.255.x that cisco use, what do I need to modfy the script. I changed it to a /24 however it says : “: bad flag in substitute command: ‘/’

    any pointers would be greatly appreciated.


Add A Comment