Generate a Single TMSL File for all records in the Excel/CSV file in Powershell-CodePudding

I currently, have an Excel sheet which is used as a configuration file. The contents of the CSV/Excel is as below

"DatabaseName","SchemaName","TableName","PartitionName"
"AdventureWorks2","fact","Internet Sales","Internet Sales - Current Month"
"AdventureWorks2","fact","Internet Sales","Internet Sales - M-1"
"AdventureWorks2","fact","Internet Sales","Internet Sales - M-2"

I also have a Refresh Template in TMSL format for Creating/Refreshing/Deleting of Partitions TMSL format to Delete

{
  "refresh": {
    "type": "automatic",
    "objects": [
      {
        "database": "~DBName~",
        "table": "~TableName~",
        "partition": "~PartitionName~"
      }
    ]
  }
}

I want to read the contents from the CSV/Excel file and generate a single TMSL file as below

{
  "refresh": {
    "type": "automatic",
    "objects": [
      {
        "database": "AdventureWorks2",
        "table": "Internet Sales",
        "partition": "Internet Sales - Current Month"
      },
      {
        "database": "AdventureWorks2",
        "table": "Internet Sales",
        "partition": "Internet Sales - M-1"
      },
      {
        "database": "AdventureWorks2",
        "table": "Internet Sales",
        "partition": "Internet Sales - M-2"
      }
    ]
  }
}

Currently, I am able to generate the File as independent files for each partition

#SourcePaths
$TemplatePath = "..\PartitionTemplate.tmsl"
$ConfigFile = "..\Delete-ParttionConfigFile.xlsx"
$TgtPath = "..\03_ProcessedTMSLFiles\"

#use the Import-Excel Module
$PartitionExtract = Import-Excel  $ConfigFile

ForEach($PartitionRecord in $PartitionExtract)
{
    $DatabaseName   = $PartitionRecord.database
    $TableName      = $PartitionRecord.table
    $PartitionName  = $PartitionRecord.PartitionName

    $TMSLFileName = "$TableName - Delete $PartitionName.tmsl"
    $FinalDestinationTMSLPath = "$TgtPath$TMSLFileName"
    
    Copy-Item -Path $TemplatePath -Destination $FinalDestinationTMSLPath
    $ReadTMSLFile = Get-Content -path $FinalDestinationTMSLPath -Raw 
    $NewTMSLFileContent = $ReadTMSLFile.Replace("~DatabaseName~",$DatabaseName).
                                        Replace("~TableName~",$TableName).
                                        Replace("~PartitionName~",$PartitionName)
    $NewTMSLFileContent | Set-Content -Path $FinalDestinationTMSLPath

Need some assistance in this regard.

Thanks

CodePudding user response：

I built a tool that does this kind of thing a few years ago. It was an exercise to gain proficiency. My tool indicates variables in the template differently than you do. Instead of enclosing template variables in squiggles, I prefix them with a dollar sign, making them look like powershell variables. Here is the tool I came up with:

<#
.NOTES
    Script: Expand-Csv    Rev:  3.2
    Author: DGC           Date: 2-21-19
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    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.
#>
function Expand-csv {
    [CmdletBinding()]
    Param (
       [Parameter(Mandatory=$true)] [string] $driver,
       [Parameter(Mandatory=$true)] [string] $template
    )
    Process {
       Import-Csv $driver | % {
           $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
           Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)} 
       }
    }
}

I make my tool available for down load from Github. link here

You might benefit from two techniques I use in my tool.

First, I use a powershell internal function called ExpandString to plug the actual values in for the powershell variables encountered in the template.

Second, I don't gather the output into a single file. I just send the output down the pipeline. This pipelined output can be written to an output file using redirection.

Here is how I applied it to your case.

Here is the csv file that I set up. It's exactly what you posted.

"DatabaseName","SchemaName","TableName","PartitionName"
"AdventureWorks2","fact","Internet Sales","Internet Sales - Current Month"
"AdventureWorks2","fact","Internet Sales","Internet Sales - M-1"
"AdventureWorks2","fact","Internet Sales","Internet Sales - M-2"

Here is the template file I set up. I derived this from your template, but I made a few changes. First off, this is only the portion of your template that is to be repetitive. Second, the variables are prefixed with a dollar sign instead of being enclosed in squiggles.
Third, I changed the spelling of one of the variables to make it agree with the header record of the csv.

  {
    "database": "$DatabaseName",
    "table": "$TableName",
    "partition": "$PartitionName"
  }

Here is the command I used to pass the csv file and the template through Expand-csv. Notice that I have redirected the output to a file.

Expand-Csv test.csv test.tmplt > test.out

And here is what the output file looks like:

  {
    "database": "AdventureWorks2",
    "table": "Internet Sales",
    "partition": "Internet Sales - Current Month"
  }
  {
    "database": "AdventureWorks2",
    "table": "Internet Sales",
    "partition": "Internet Sales - M-1"
  }
  {
    "database": "AdventureWorks2",
    "table": "Internet Sales",
    "partition": "Internet Sales - M-2"
  }

This generates the portion of your desired script that repeats. You still have to paste a little text at the front, and a little text at the end in order to produce a complete script.