Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Stripping lines from text file with PowerShell

    Hello,

    I am absolutely new to Powershell but this task can be achieved very easily by the experts,
    I need to browse to a text file and delete/strip off the unwanted lines from text file.
    1. Browse to file

    2. Capture the DATE inside the text file and write it to first line of the text file.

    3. Remove the strings/lines from text file, I tried with
    Code:
    Get-Content C:\Users\danny\Desktop\test\sample.txt | Where { $_ -notmatch "^|BRANCH|REPORT ID|======|Selection" } | Set-Content C:\Users\danny\Desktop\test\filteredfile.txt
    finding impossible to delete this lines
    | SrNo | Name And | TAN No. | Amount | Date of | Account No. | Date of | Amount of WithDrawal | Particulars | Comment | | Address | | Deposited | Deposit | | WithDrawal | of Each |
    4. To modify string with this lines (BY Card No. : numeric value), modify [:] to [#] and save the output with finally

    5. Calling Excel file to execute the macro within the file.

    I have attached the sample file and result file for more details.

    Many Thanks in advance.

    Script found on internet for search file. FileDialog
    Code:
    # Example use:
    $file = Select-FileDialog -Title "Select a file" -Directory "C:\Users\danny\Desktop\test" -Filter “Text files (*.txt)|*.txt”
    
    
    function Select-FileDialog
    {
        param([string]$Title,[string]$Directory,[string]$Filter="All Files (*.*)|*.*")
        [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
        $objForm = New-Object System.Windows.Forms.OpenFileDialog
        $objForm.InitialDirectory = $Directory
        $objForm.Filter = $Filter
        $objForm.Title = $Title
        $Show = $objForm.ShowDialog()
        If ($Show -eq "OK")
        {
            Return $objForm.FileName
        }
        Else
        {
            Write-Error "Operation cancelled by user."
        }
    }
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Danny,

    This will do the PowerShell part as far as I can tell.
    Code:
    $FileLocation = "G:\BEKDocs\Scripts"  #Change to your location!
    $FileFilter = "sample.txt"                    #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    Clear-Host
    
    ForEach ($FSpec in $FileList) {
      
      $a = (get-content $FSpec.FullName)
      $b=$a[2].split(":")
      $a[0] = "Date :$($b[2].Trim(" "))"
    
      $a = $a | Where-Object {$_ -notlike " *"}               |
                Where-Object {$_ -notLike "BRANCH???*"}       |
                Where-Object {$_ -notLike "*| SrNo*"}         |
                Where-Object {$_ -notlike "*|-----*"}         |
                Where-Object {$_ -notlike "*|=====*"}         |
                Where-Object {$_ -notlike '*REPORT ID*'}      |
                Where-Object {$_ -notlike "*Address*"}        |
                Where-Object {$_ -notlike "*Selection*"}      |
                Where-Object {$_ -notlike "*Product Code*"}   |
                Where-Object {$_ -notlike "*All/Select*"}     |
                Where-Object {$_ -notlike "*Select Account*"} |
                Where-Object {$_ -notlike "*From Account*"}   |
                Where-Object {$_ -notlike "*To Account*"}     |
                Where-Object {$_ -notlike "*Acct Type*"}      |
                Where-Object {$_ -notlike "*From Date*"}      |
                Where-Object {$_ -notlike "*To Date*"}        |
                Where-Object {$_ -notlike "*Filter*"}         |
                Where-Object {$_ -notlike "*Debit*"}          |
                Where-Object {$_ -notlike "*All Specific*"}
    
     Set-Content -path  "$($FileLocation)\SampleOut.txt" $a
    }
    Note: The first Where-Object -notlike is not a blank * but a special character that I copied out of your file!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    You could also try it this way, to run your Excel file after processing the text file.

    Code:
    param (
    	$infile = '.\sample.txt',
    	$outfile = '.\sampleout.txt',
    	$excelfile = '.\excelfile.xlsm'
    )
    
    $text = Get-Content $infile
    Set-Content $outfile ($text | Select-String 'DATE :\d{2}/\d{2}/\d{4}' | Select-Object -First 1).Matches.Value
    Add-Content $outfile $(($text -match '^\|\s*Branch Code|\|\s*\d|\|\s*Total') -replace ':(\s\d)','#$1')
    
    Invoke-Expression $excelfile

  4. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hello,

    Thanks RG for the response my 3 problems solved but the 4 one is still pending.

    4. To modify string with this lines in the text file (BY Card No. : numeric value), modify [:] to [#] and save the output .
    sample: BY Card No. : 2167 3287 7374 8487 convert to BY Card No. # 2167 3287 7374 8487

    Thanks.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Danny,

    Sorry about that!

    Revised Code:
    Code:
    $FileLocation = "G:\BEKDocs\Scripts"  #Change to your location!
    $FileFilter = "sample.txt"            #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    Clear-Host
    
    ForEach ($FSpec in $FileList) {
      
      $a = (get-content $FSpec.FullName)
      $b=$a[2].split(":")
      $a[0] = "Date :$($b[2].Trim(" "))"
    
      $a = $a | Where-Object {$_ -notlike " *"}               |
                Where-Object {$_ -notLike "BRANCH???*"}       |
                Where-Object {$_ -notLike "*| SrNo*"}         |
                Where-Object {$_ -notlike "*|-----*"}         |
                Where-Object {$_ -notlike "*|=====*"}         |
                Where-Object {$_ -notlike '*REPORT ID*'}      |
                Where-Object {$_ -notlike "*Address*"}        |
                Where-Object {$_ -notlike "*Selection*"}      |
                Where-Object {$_ -notlike "*Product Code*"}   |
                Where-Object {$_ -notlike "*All/Select*"}     |
                Where-Object {$_ -notlike "*Select Account*"} |
                Where-Object {$_ -notlike "*From Account*"}   |
                Where-Object {$_ -notlike "*To Account*"}     |
                Where-Object {$_ -notlike "*Acct Type*"}      |
                Where-Object {$_ -notlike "*From Date*"}      |
                Where-Object {$_ -notlike "*To Date*"}        |
                Where-Object {$_ -notlike "*Filter*"}         |
                Where-Object {$_ -notlike "*Debit*"}          |
                Where-Object {$_ -notlike "*All Specific*"}  
                 
     $a = $a -replace "BY Card No. :","BY Card No. #"
    
     Set-Content -path  "$($FileLocation)\SampleOut.txt" $a
    }
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    danny69 (2015-11-04)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •