check numbers from a text register file

PowerShell Help
1

I get a text register file from our check processing vendor daily and need to pull out the starting check number and ending check number to report out to a email and/or a dashboard.
the text file is Tab delimited, the check numbers are value position 31-37 in the file
how can I do this with powershell?

2

I’m not sure what you mean by “positions 31-37”… are there 7 check numbers on the same line, and multiple lines in the file?

3

Assumption: You will be reading a text file with multiple lines. The data you are interested in extracting (the check number) appears in columns 31-37 (seven digits) on each line.

Here is a sample using RegEx to skip the first 30 characters to the data in colums 31-37 (one-based column count, not zero-based).

$sample = @"
1foo4123412341234123412341234123412311234123412341234123412341delta34123412341234123412341234
2123bar341234123412341234123412341232123412341234123412341234123412southwest41234123412341234
3123412tango4123412341234123412341233123412341234123412341234123412341234123jetblue3412341234
412341234123delta12341234123412341234123412341234123412341234123412341frontier234123412341234
5123412341234123412341234123412341235burlington3412341234123412341234123412341234123412341234
61234123412341234123412341234123412361234123412northern34123412341illinoiscentral123412341234
7123412341234123412341234123412341237123412341234123412santafe2341234123412341234123412341234
"@ -split "`r`n"
$pattern = [regex]"^.{30}(?'chk'\d{7})"
$line = 0
foreach ($item in $sample) {
    $line++
    if ($item -match $pattern) 
    {
        Write-Host "Line number $line check number $($Matches['chk'])"
    }
}

Of course populate your data however. This is just to show the matching.

4

Dave,
here is a sample of the text file, I need to know the 1st check number and the last.

512 6052922 000000011664 2/14/16blah blah blah 123 blah st

512 6052923 000000016587 2/14/16blah blah blah 123 blah st

512 6052924 000000018908 2/14/16blah blah blah 123 blah st

512 6052925 000000022761 2/14/16blah blah blah 123 blah st

512 6052926 000000010400 2/14/16blah blah blah 123 blah st

5

If “000000016587” is an example of a check number than this will export first and last numbers.

Get-Content .\test.txt | Select-Object -First 1 -Last 1 | 
ForEach-Object {$check = $_ -match "\d+ \d+ (?'check'\d+)"
[pscustomobject]@{check=$Matches.check}} | Export-Csv .\check.csv -NoTypeInformation
6

I guess I’m a little confused from your initial description versus your sample data.

  • What was the deal about columns 31-37?
  • Which numbers in your sample data represent the check number? Your sample data makes it look like columns 13-24.
  • Is it space-delimited fields? If so a simple split would do what you want.
  • Do ALL the lines in the text file match this pattern?
  • Is this a simple text file, or a true CSV file? If the latter, what are the column headings?
  • When you say, "I need to know the 1st check number and the last," what does that mean? You want an interactive display on the console? Output to a file (text, CSV, XML)?
Please don't make us guess.
7 
$sample = @"
512 6052922 000000011664 2/14/16blah blah blah 123 blah st 
512 6052923 000000016587 2/14/16blah blah blah 123 blah st 
512 6052924 000000018908 2/14/16blah blah blah 123 blah st 
512 6052925 000000022761 2/14/16blah blah blah 123 blah st 
512 6052926 000000010400 2/14/16blah blah blah 123 blah st 
"@ -split "\n"
$first, $last = $sample | select -First 1 -Last 1 | 
    foreach { ($PSItem -split "\s")[2] }
Write-Host "$first, $last"
8

when I copied the text to post it did not did not format correctly.
the layout is columns 1 -3 is always 512
columns 31 -37 is the check numbers the next is batch number, followed by date, vendor, and address but those are irrelevant, I just need to port out the 1st check number and the last check number, there is 1 check number per line. sometimes as little as 4-5 checks other times 2-3,000 check numbers.
so in my example above I would report out 1st check # 6052922, last 6052926
sorry for the confusion and I appreciate the assist and learning experience.

9

Then in my code the following line changes …

    foreach { ($PSItem -split "\s")[1] }

Again, that assumes the sample data is correct – that is, the check number is the second field in a set of space-delimited file. We can do this by columns (my first example) or by fields.

In the future, please provide real-world data (sanitized as necessary). The made up stuff almost never is right the first of second times and just wastes everyone’s time.

10

Bob, this is great stuff, and I really appreciate your help.
how am I going to get this to read the context of this text file I get daily
for example the text file is currently in the path c:\temp\tmp\376602820160217000.txt

11

Is it the only file in that directory?
Or do you want to grab today’s file based on the date portion of the file name, e.g., the ‘20160217’ part of the name?

pushd C:\Ephemeral  # my temp directory
$file = Get-ChildItem -Path . -Filter "*$(Get-Date -f yyyyMMdd)*"
$first, $last = Get-Content $file | select -First 1 -Last 1 | 
    foreach { ($PSItem -split "\s")[1] }
Write-Host "$first, $last"
12

the contents of the folder would be one file for each day
376602820160212000.txt 02/12/2016
376602820160213000.txt 02/13/2016
376602820160214000.txt 02/14/2016
376602820160215000.txt 02/15/2016
376602820160216000.txt 02/16/2016
376602820160217000.txt 02/17/2016

13

But that doesn’t tell me how you plan on running the script. The script above determine today’s day and looks for a matching file. Would you ever want to run the script against a file from another date? And if so, do you want to be prompted, or give the date on the command line? How do you want to do it>

14

I will put in a sch task to run a script and grab the beginning check number and last check number from that days file which will output to a new generic named text file to be used to email to those who need to know. for now. later it will be incorporated into a sharepoint dashboard for financial users

15

So do you only want to keep the check numbers (first and last) from the last run in your master file?

16

Hi Bob, so I was planning to parse out the 1st and last check number out to another text file just called checks.txt which would be replaced daily as this script read that days checks