Script to replace variable text by reading files in the folder

This topic contains 2 replies, has 2 voices, and was last updated by  Oscar 1 month, 4 weeks ago.

  • Author
    Posts
  • #73262

    Oscar
    Participant

    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

  • #73265

    Dirk
    Participant

    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
    } 
    
  • #73273

    Oscar
    Participant

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

You must be logged in to reply to this topic.