How do I create a Windows Server script to remove error records, AND the previous record to each, from a file with the results written to a NEW file

Question

I’m trying to solve a production problem. We receive an error file daily (Mon-Fri) from the bank that contains error records. These records are 94 bytes in length. On occasion there will be some error codes in the file that cause some significant problems when processed by a system at the State.

I was asked to “filter out” the error records from the file that gets sent to the State. To do this, I created a one line FINDSTR command (below) to locate records containing the error code “R02” (no quotes) in positions 4-6 of the records, and remove them.

      FindStr /V "R02" INPUT_FILE > OUTPUT_FILTERED_FILE_%DATE%_%TIME%

This worked as I had hoped and the requesting users were happy, BUT one of the managers found that the record immediately preceding the record containing the error code ALSO MUST BE DELETED from the file, because it is associated with the record containing the error code. The problem is that this preceding record does NOT contain an error code in it. There is a 6 digit number in positions 89-94 that could be related to the error record, but I don't want to guess, or over-complicate the script.

As you can see in the example data (below), there are 5 error records (5, 7, 9, 11, and 27), containing error code “R02.” My FindStr command worked for removing the R02 records and creating an output file without any of the error records (containing “R02”).

WHAT I NEED NOW is to be able to remove the “associated records” that go with the R02 records in the example data below. Done properly, the following records would be removed from the example file in a single process: 4, 5, 6, 7, 8, 9, 10, 11, 26 and 27.

I need to delete both the “R02 error records AND the associated record above each of those error records simultaneously, and write the output to a NEW file, leaving the original file intact AS-IS – because it is retained locally by our user department.

Below, is what the INPUT record content looks like (error codes in BOLD), with a record number pre-pended for reference purposes. Sorry, but I can’t supply the full 94 byte record images due to security issues. Below that, is what my desired output file should look like.

I don’t know if this can be done with FindStr, but I’m sure PowerShell can do the job, BUT I know nothing about PowerShell. The script will be executed on Windows Server.

Can anyone help me with creating a script that will accomplish the processing to transform the input file into the desired output file?

Thanks very much in advance for your assistance.

****** Example Data ******

Input File

Nbr - - Record Content - -

01    HEADER RECORD                    
02    CONTROL RECORD                   
03    5200SAN                          
04    62112200                         
05    799**R02**12                         
06    62112200                         
07    799**R02**12                         
08    62112200                         
09    799**R02**12                         
10    62112200                         
11    799**R02**12                         
12    82000000                         
13    5200SAN                          
14    62112200                         
15    798C0312                         
16    62112200                         
17    798C0312                         
18    62112200                         
19    798C0312                         
20    62112200                         
21    798C0312                         
22    62112200                         
23    798C0312                         
24    82000000                         
25    5200SAN                          
26    62112200                         
27    799**R02**12                         
28    TRAILER RECORD                   

Desired New Output File

Nbr - - Record Content - -

 01    HEADER RECORD                
 02    CONTROL RECORD               
 03    5200SAN                      
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
       #  DELETED  #                
 12    82000000                     
 13    5200SAN                      
 14    62112200                     
 15    798C0312                     
 16    62112200                     
 17    798C0312                     
 18    62112200                     
 19    798C0312                     
 20    62112200                     
 21    798C0312                     
 22    62112200                     
 23    798C0312                     
 24    82000000                     
 25    5200SAN                      
       #  DELETED  #                
       #  DELETED  #                
 28    TRAILER RECORD
           

Ketosis

Please show your current code to read the file and use FindStr to remove the R02 records – Fortenberry

I included my original FindStr "code" under paragraph 2 above. – Ketosis

Answer 1

The following Powershell is untested, but should do basically what you're asking for. There very well may be bugs in my logic, but this will give the basic framework of what needs to happen.

[cmdletbinding()]
Param
(
    [string] $InputFilePath
)

# Read the text file
$InputFile = Get-Content $InputFilePath

# Get the time
$Time = Get-Date -Format "yyyyMMdd_hhmmss"

# Set up the output file name
$OutputFileFiltered = "Output_Filtered_File_$Time.txt"

# Initialize the variable used to hold the output
$OutputStrings = @()

# Loop through each line in the file
# Check the line ahead for "R02" and add it to the output
# or skip it appropriately    
for ($i = 0; $i -lt $InputFile.Length - 1; $i++)
{
    if ($InputFile[$i + 1] -notmatch "R02")
    {
        # The next record does not contain "R02", add it to the output 
        $OutputStrings += $InputFile[$i]
    }
    else
    {
        # The next record does contain "R02", skip it
        $i++
    }
}

# Add the trailer record to the output
$OutputString += $InputFile[$InputFile.Length - 1]

# Write the output to a file
$OutputStrings | Out-File $OutputFileFiltered

Save that as FilterScript.ps1 (or whatever you prefer) and execute it in Powershell with the following:

FilterScript.ps1 -InputFilePath "C:\Path\To\Your\InputFile.txt"

Gretel

I just gave your script a try and ended up with the following error: FilterR02.ps1 : The term 'FilterR02.ps1' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1 + FilterR02.ps1 -InputFilePath C:\Path\To\Your\InputFile.txt + ~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (FilterR02.ps1:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException Will look into aftr lunch; good for 1st run – Ketosis

I saved the script as FilterR02.ps1 and ran it as: FilterR02.ps1 -InputFilePath "C:\EZTRIEVE\Banking\Data\INPUT_EFT_FILE". From the message, I'm thinking that maybe something needs to be added to the [cmdletbinding( )] line at the top, but I haven't been able to find anything concrete that explains it. More searching tomorrow. – Ketosis

The error indicates the script is not saved in a location that powershell searches for scripts. The easiest remedy is to give the full path to the script. So use something like C:\Folder\Subfolder\FilterRO2.ps1 in place of "FilterR02.ps1" – Gretel