Script to replace variable text by reading files in the folder

Welcome Forums General PowerShell Q&A Script to replace variable text by reading files in the folder

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 year, 11 months ago.

  • Author
    Posts
  • #73262

    Participant
    Topics: 1
    Replies: 1
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 0
    Replies: 19
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 1
    Replies: 1
    Points: 0
    Rank: Member

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

The topic ‘Script to replace variable text by reading files in the folder’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort