How to combine a template with a CSV file in Powershell

Question

I want to combine a template that looks like this:

grant $privs 
   on $table 
   to $user;

With a CSV file that looks like this:

privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"

To produce an SQL script that looks like this:

grant ALL 
   on Employees 
   to DBA;

grant READ 
   on Employees 
   to Analyst;

grant READ, WRITE 
   on Employees 
   to Application;

grant ALL 
   on Departments 
   to DBA;

grant READ 
   on Departments 
   to Analyst, Application;

The template has three parameters that look like Powershell variables. The CSV file has enough data to specify five copies of the template. In real life, it would be more like 200 copies.

I also want to be able to apply the same technique to a variety of CSV files, most of which do not come from databases. And I want to use a variety of templates, most of which do not generate SQL. For that reason, I want a technique that deals with plain text files, instead of attaching to the database.

Note: I am asking this question so as to provide the community with an answer.

Answer 1

I have written a function, Expand-Csv, that does this. Here it is:

<#  This function is a table driven template tool. 

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table 
    (in a csv file) has one column for each variable, 
    and one row for each expansion to be generated.

    12/12/2016

#>

function Expand-csv {
   [CmdletBinding()]
   Param(
      [Parameter(Mandatory=$true)]
      [string] $driver,
      [Parameter(Mandatory=$true)]
      [string] $template
   )
   Process
   {
      $pattern = (Get-Content $template) -join "`n"

      Import-Csv $driver | % {
         foreach ($p in $_.psobject.properties) {
            Set-variable -name $p.name -value $p.value
            }
         $ExecutionContext.InvokeCommand.ExpandString($pattern) 
      }
   }
}

In the case at hand, the call would look like this:

Expand-Csv grants.csv grants.tmplt > grants.sql

The output that would come to the console gets redirected to a file.

I've tried using this for generating scripts in Powershell itself, but a word of caution is in order. ExpandString does some of the same processing that Invoke-Command does, so you can get undesired consequences. I only use it for the simplest kind of PS code, like a series of calls to some external app.