check numbers from a text register file

Tagged: 

This topic contains 15 replies, has 4 voices, and was last updated by Profile photo of Darryl Pitts Darryl Pitts 9 months, 3 weeks ago.

  • Author
    Posts
  • #35310
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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?

  • #35312
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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?

  • #35314
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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.

  • #35319
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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

  • #35321
    Profile photo of random commandline
    random commandline
    Participant

    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
    
  • #35340
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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.

  • #35343
    Profile photo of Bob McCoy
    Bob McCoy
    Participant
    $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"
    
  • #35344
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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.

  • #35345
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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.

  • #35370
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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

  • #35372
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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"
    
  • #35373
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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

  • #35375
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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>

  • #35376
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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

  • #35388
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

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

  • #35390
    Profile photo of Darryl Pitts
    Darryl Pitts
    Participant

    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

You must be logged in to reply to this topic.