如何在 Powershell 中将模板与 CSV 文件结合使用 [英] How to combine a template with a CSV file in Powershell

问题描述

我想组合一个看起来像这样的模板:

I want to combine a template that looks like this:

grant $privs 
   on $table 
   to $user;

使用如下所示的 CSV 文件:

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"

生成如下所示的 SQL 脚本:

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;

模板具有三个看起来像 Powershell 变量的参数.CSV 文件有足够的数据指定模板的五个副本.在现实生活中，它更像是 200 份.

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.

我还希望能够将相同的技术应用于各种 CSV 文件，其中大部分不要来自数据库.而且我想用各种模板，大部分都没有生成 SQL.出于这个原因，我想要一种处理纯文本文件的技术，而不是附加到数据库.

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.

推荐答案

我已经编写了一个函数 Expand-Csv 来执行此操作.这是:

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.

我已经尝试使用它在 Powershell 本身中生成脚本，但需要注意的是.ExpandString 执行一些相同的处理Invoke-Command 会这样做，所以你可能会得到不想要的结果.我只用它对于最简单的 PS 代码，例如对某些外部应用程序的一系列调用.

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.

这篇关于如何在 Powershell 中将模板与 CSV 文件结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！