calculate from values in a text file's first column, ":" delimited, bat, VBs, PS

[kdb2012]

Please help me program the scenario below:
contents of myfile.txt:
ServerName: KDB2012
----------------------------
2:Black\Bird
16:Black\Dog
20:Black\Cat
----------------------------
6:Animal
15:Animal
65:Animal

Now, I want to check "Animal" by its number, if that number (6 in our case) > 2 and < 16 (Black\whatever) then print Animal = Black\Bird
similarly if 15 > 16 and 15 < 20 then print Animal = Black\Dog
             if 65 > 20 then print Animal = Black\Cat

I am in desperate need to do this, either using batch, VBs, Powershell. Much Appreciated!

[Salmon Trout]

Why don't you put what is really in the file?

[kdb2012]

ServerName: KDB2012\ADUsers
---------------------
2:KALDB\Developers
16:KALDB\ProdDevs
20:KALDB\BADATAREADER
25:KALDB\ZADATAREADER
115:KALDB\AREADER
168:KALDB\PREADER
190:KALDB\PWRITER
192:KALDB\READER
257:KALDB\WRITER
261:KALDB\SWRITER
299:KALDB\ITDataProc
316:KALDB\RDevelopment
340:KALDB\ReadOnly
-------------------------
152:Kalvin Rodger
183:Kalvin Rodger
239:Kalvin Rodger
289:Kalvin Rodger

[Salmon Trout]

There will be 4 lines printed

152:Kalvin Rodger
152 is greater than 115 and less than 168, so print Kalvin Rodger = KALDB\AREADER

183:Kalvin Rodger
183 is greater than 168 and less than 190 so print Kalvin Rodger = KALDB\PREADER

239:Kalvin Rodger
239 is greater than 192 and less than 257 so print Kalvin Rodger = KALDB\READER

289:Kalvin Rodger
289 is greater than 261 and less than 299 so print Kalvin Rodger = KALDB\SWRITER

Is that correct?

[kdb2012]

Correct!
And the contents of the file are dynamic. They will change from time to time. The groups and their number and the ADUser and its number will keep on changing.

[kdb2012]

Any Progress?

[Sidewinder]

Any Progress?

Didn't know we were on the clock. 

The hard part was retrieving chunks of the file for individual processing. VBScript was a possible solution as it supports regular expressions, but so does Powershell so I went with the latter.

Code: [Select]

$file = "c:\myfile.txt"
$arrTitles = @()
$arrEmps = @()

# Load the Job Titles into Array
#
(Get-Content -Path $file) -match "^[0-9]{1,3}:[A-Z]{1,}\\[A-Z]{1,}$" |
  ForEach-Object {
    $arrTitles += , @( [Convert]::ToDecimal($_.split(":")[0]), $_.split(":")[1] )
  }
$arrTitles.GetEnumerator() | Sort-Object | Out-Null
 
# Load the Employees into Array
#
(Get-Content -Path $file) -match "^[0-9]{1,3}:[A-Z]{1,}\s[A-Z]{1,}$" |
  ForEach-Object {
    $arrEmps += , @( [Convert]::ToDecimal($_.split(":")[0]), $_.split(":")[1] )
  } 
 
#Do the Lookups
#
for($j=0; $j -lt $arrEmps.Count; $j++) {
  for($i=0; $i -lt $arrTitles.Count - 1; $i++) {
    if( ($arrEmps[$j][0] -ge $arrTitles[$i][0]) -and ($arrEmps[$j][0] -le $arrTitles[$i+1][0]) ) `
      { Write-Host $arrEmps[$j][1], "=", $arrTitles[$i][1] }
  }
}

Save the script with a PS1 extension and run from the Powershell command prompt. Powershell does not search the current directory for a script so use the .\ pointer if you need to point to the current directory.

The first line of code is the file name. Change as needed.

 

[Salmon Trout]

Well done, Sidewinder!

[kdb2012]

Excellent! Thanks you sooo much.

[Salmon Trout]

I just had to finish it...

Save with .vbs extension
call with cscript.exe //nologo
pass input filename as parameter

example

cscript //nologo Scriptname.vbs input.txt

input.txt...

Code: [Select]

ServerName: KDB2012\ADUsers
---------------------
2:KALDB\Developers
16:KALDB\ProdDevs
20:KALDB\BADATAREADER
25:KALDB\ZADATAREADER
115:KALDB\AREADER
168:KALDB\PREADER
190:KALDB\PWRITER
192:KALDB\READER
257:KALDB\WRITER
261:KALDB\SWRITER
299:KALDB\ITDataProc
316:KALDB\RDevelopment
340:KALDB\ReadOnly
-------------------------
152:Kalvin Rodger
183:Kalvin Rodger
239:Kalvin Rodger
289:Kalvin Rodger


The script...

Code: [Select]

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ReadFile  = objFSO.OpenTextFile   (wscript.arguments(0),  ForReading)
strText = ReadFile.ReadAll
Readfile.Close
arrFileLines = Split(strText, vbCrLf)
i=3
l=0
Dim LimitList()
Do
sline=arrFileLines(i)
ReDim Preserve LimitList (l)
LimitList (l) = sline
l = l + 1
i = i + 1
Loop until Mid(sline,1,10)="----------"

Dim LimitValues()
Dim OutPutText()
For j = 0 To (UBound(LimitList)-1)
MyString=LimitList(j)
arrTokens = Split (MyString, ":")
ReDim Preserve LimitValues(j)
LimitValues(j) = arrtokens(0)
ReDim Preserve OutPutText(j)
OutPutText(j) = arrTokens(1)
Next

Dim LookupCode()
For j = 0 To (UBound(LimitValues)-1)
ReDim Preserve LookupCode(j)
LookUpCode (j) = "If (numval > " & LimitValues(j) & ") AND (numval < " & LimitValues(j+1) & ") Then wscript.echo nameval & " & Chr(34) & " = " & OutPutText(j) & Chr(34)
Next

ReDim Preserve LookupCode(j)
LookUpCode (j) = "If (numval > " & LimitValues(UBound(LimitValues)) & ") Then wscript.echo nameval & " & Chr(34) & " = " & OutPutText(j) & Chr(34)

For j = i To UBound(arrFileLines)
MyString = arrfilelines(j)
arrTokens = split(MyString,":")
numval=arrTokens(0)
Nameval=arrTokens(1)
For k = 0 To UBound(LookUpCode)
ExecuteGlobal LookUpCode(k)
Next
Next

output...

Code: [Select]

Kalvin Rodger = KALDB\AREADER
Kalvin Rodger = KALDB\PREADER
Kalvin Rodger = KALDB\READER
Kalvin Rodger = KALDB\SWRITER

[kdb2012]

Viola great my friend.

[Sidewinder]

In the interest of accuracy, I have since discovered that the Powershell solution has a serious logic flaw and an improper use of the Sort-Object cmdlet. The script worked in spite of the flaws, but will produce inaccurate results under certain circumstances.

A newer and better version is posted below:

Code: [Select]

$file = Join-Path -Path $pwd -ChildPath myfile.txt
$arrTitles = @()
$arrEmps = @()

# Load the Job Titles into Array
#
(Get-Content -Path $file) -match "^[0-9]{1,3}:[A-Z]{1,}\\[A-Z]{1,}$" |
  ForEach-Object {
    $arrTitles += , @( ( [Convert]::ToDouble($_.split(":")[0]) ).ToString("000"), $_.split(":")[1] )
  }
 
$arrTitles = $arrTitles | Sort-Object @{Expression={$_[0]}; Ascending=$true}

# Load the Employees into Array
#
(Get-Content -Path $file) -match "^[0-9]{1,3}:[A-Z]{1,}\s[A-Z]{1,}$" |
  ForEach-Object {
    $arrEmps += , @( ( [Convert]::ToDouble($_.split(":")[0]) ).ToString("000"), $_.split(":")[1] )
  }
   
# Do the Lookups
#
for($j=0; $j -lt $arrEmps.Count; $j++) {
  for($i=0; $i -lt $arrTitles.Count - 1; $i++) {

    if( ($arrEmps[$j][0] -eq $arrTitles[$i+1][0]) ) `
      { Write-Host $arrEmps[$j][1], "=", $arrTitles[$i+1][1]; break }

    if( ($arrEmps[$j][0] -ge $arrTitles[$i][0]) -and ($arrEmps[$j][0] -lt $arrTitles[$i+1][0]) ) `
        { Write-Host $arrEmps[$j][1], "=", $arrTitles[$i][1] }
  }       
}

Sorry for any inconvenience. 

[Salmon Trout]

I've been prettying up my effort too...

Code: [Select]


Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Read whole input file at once into string
Set ReadFile = objFSO.OpenTextFile (wscript.arguments(0),  ForReading)
strText = ReadFile.ReadAll
Readfile.Close

' Split string into individual lines
arrFileLines = Split(strText, vbCrLf)
Dim LimitList()
Dim LimitValues()
Dim OutPutText()

' Get first part into array
' Parsed stored file up to second dashed line

' Start at 3rd line
InputLine=3
LimitLine=0

' Read file lines one by one
Do
sline=arrFileLines(InputLine)
ReDim Preserve LimitList (LimitLine)
LimitList (LimitLine) = sline
LimitLine = LimitLine + 1
InputLine = InputLine + 1
Loop until Mid(sline,1,10)="----------"

' InputLine now points to first line after dashes
DataStart = InputLine

' Get band limit values
' Read each line of first block
For j = 0 To (UBound(LimitList)-1)
' Split line into 2 tokens at : character
arrTokens = Split (LimitList(j), ":")
ReDim Preserve LimitValues(j)
' First token is numerical value
LimitValues(j) = arrtokens(0)
ReDim Preserve OutPutText(j)
' Second token is text string for that band
OutPutText(j) = arrTokens(1)
Next

' Process second part of input file
For j = DataStart To UBound(arrFileLines)
' Split line into 2 tokens at : character
arrTokens = split(arrfilelines(j),":")
numval=Int(arrTokens(0))
Nameval=arrTokens(1)
' For each line in second part
For k = 0 To UBound(Limitvalues)
LineToWrite = nameval & " = " & OutPutText(k)
' This is lower test value
lower = Int(Limitvalues(k))
' If not last line check if number is above this band lower limit
' and below next band lower limit
If k < UBound(Limitvalues) Then
' This Is next band lower limit
upper = Int(Limitvalues(k+1))
' Do test
If (numval > lower) AND (numval < upper) Then
wscript.echo LineToWrite
End If
Else
' If this is last line just test if number is above limit
If (numval > lower) Then
wscript.echo LineToWrite
End If
End If
Next
Next



[kdb2012]

Not sure why am I getting this:

RESULTS ARE OK, but
in old script
C:\Kaldb\adu.vbs(42, 4) Microsoft VBScript runtime error: Subscript out of rang
e: '[number: 0]'
in new script
C:\Kaleem\now.vbs(53, 2) Microsoft VBScript runtime error: Subscript out of rang
e: '[number: 0]'

53: numval=Int(arrTokens(0))
54: numval=arrTokens(1)

[Salmon Trout]

Not sure why am I getting this:

RESULTS ARE OK, but
in old script
C:\Kaldb\adu.vbs(42, 4) Microsoft VBScript runtime error: Subscript out of rang
e: '[number: 0]'
in new script
C:\Kaleem\now.vbs(53, 2) Microsoft VBScript runtime error: Subscript out of rang
e: '[number: 0]'

53: numval=Int(arrTokens(0))
54: numval=arrTokens(1)

Possibly there is a line in input file not as your description; best thing is to learn scripting and then you can write and fix scripts yourself. Does Sidewinder's script gives error?