Script to replace variable text by reading files in the folder

PowerShell Help
1

I have got 2 types of files in my folder a) error files txt file b) a sample template sql file
I need to read all error files and save them in a loop. Then read the template file and replace text ‘TGT_TBL’ with the basename of each error file and then save the file as sql with basename.sql

The template file with some code is below
BULK INSERT dbo.TGT_TBL
FROM ‘D:\emi\data\incoming\2017_03\Errors\TGT_TBL_error.log_mod’

So if there are 5 error files i need 5 sql files having the template to be created. I got something like this but it does not work

cd D:\emi\code\pdi\ELX_DMP\Error_Modifications
$files = Get-ChildItem . error_mod.sql -rec
foreach ($file in $files)
{
(Get-Content $file) |
Foreach-Object { $_ -replace “YY_mm”, “${YEAR_MONTH}” } |
Foreach-Object { $_ -replace “TGT_TBL”, “${TARGET_TABLE}” } |
Set-Content D:\emi\code\pdi\ELX_DMP\Error_Modifications"YY_mm"“TGT_TBL”$file

2

This should help to get you started:

$folder = 'D:\emi\code\pdi\ELX_DMP\Error_Modifications'
 $sqlTemplatePath = 'PATH'
 $files = Get-ChildItem -Path -Recurse -Filter *.txt
 $template = Get-Content $sqlTemplatePath
 foreach ($file in $files) {
    $baseName = $file.BaseName
    #at root level
    $sqlPath = Join-Path $folder ($baseName + '.sql')
    #if you want sql files within current folder
    #$sqlPath = Join-Path $file.Directory.FullName ($baseName + '.sql')
    $template.Replace('TGT_TBL',$baseName) | Set-Content
}
3

Thanks Dirk for the script much appreciated. I tested the script it works