如何在 Powershell 中将模板与 CSV 文件结合使用

本文介绍了如何在 Powershell 中将模板与 CSV 文件结合使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.