check numbers from a text register file

Welcome Forums General PowerShell Q&A check numbers from a text register file

Viewing 15 reply threads
  • Author
    Posts
    • #35310
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Member
      Topics: 9
      Replies: 2322
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 5
      Replies: 278
      Points: 177
      Helping Hand
      Rank: 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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member
      $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
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 6
      Replies: 236
      Points: 0
      Rank: Member

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

    • #35390
      Participant
      Topics: 1
      Replies: 6
      Points: 0
      Rank: Member

      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

Viewing 15 reply threads
  • The topic ‘check numbers from a text register file’ is closed to new replies.