Striping out numeric values and writing to SQL

[tourcd]

I've been asked to write a script that will parse a text file which holds licensing information. I need to strip out the numeric values and write them to an SQL database.

I have only very basic scripting knowledge and I'm having a couple of problems, the first being getting the numbers out as they could be any length, the second writing the results to an SQL database.

I'd be really grateful if someone could offer any pointers.

--------------------
Line1
Line2
Line3

Active Licenses: 123
Unused Licenses: 456
Total: 0

 

[tsuji]

I can offer you a concise script which should work without conceding much.
[tt]
dim sinfile,soutfile,fso,rx,s,t

sinfile="d:\xyz\input.txt" [blue]'your data[/blue]
soutfile="d:\xyz\output.txt" [blue]'your data: if write to sql db, just use t below and no need of this[/blue]

set fso=createobject("scripting.filesystemobject")

if not fso.fileexists(sinfile) then
set fso=nothing
wscript.quit 99 'early exit [1]
end if

s=""
if fso.getfile(sinfile).size<>0 then
s=fso.opentextfile(sinfile,1,false).readall
end if

set rx=new regexp
with rx
.ignorecase=true
.global=true
.pattern="(Active Licenses|Unused Licenses)\s*)(\b\d+\b)"
end with

t=rx.replace(s,"$1$2") [blue]'this is the string to send to db[/blue]

[green]'if you want to see persistent file transformed
fso.opentextfile(soutfile,2,true).write t[/green]

set fso=nothing
[/tt]

 

[tourcd]

Thanks for the script! I do have a couple of questions though.

I see that the following bit of code take the whole file and replaces this with "$1$2".

t=rx.replace(s,"$1$2")

Then this line writes the output to the soutfile.

fso.opentextfile(soutfile,2,true).write t

However when I open the file it's exactly the same as the original, less the numbers, is this what I'm expecting? I'm still not cracked how I can pass the numbers to SQL.

Sorry for asking what are probably dumb questions.

 

[tsuji]

Is not it what you want? You want to keep the number? Don't tell me yes.

 

[tourcd]

You don't want to hear this but yes, I need to keep the numbers and pass them to SQL. The idea is that I can then track the license numbers over a period of time.

 

[tsuji]

>I need to strip out the numeric values and write them to an SQL database.
So you only want number, not the Active Licenses junk? Then what is line1 etc.

 

[dm4ever]

tourcd,

Post a few lines that are exactly like the one's in your text file and provide detailed information as to what you're trying to do.

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[tsuji]

dm4ever, please take over. I'm being fed up.

 

[tourcd]

Hi dm4ever,

Here's an cut & paste from the file I'm working with, it is created from a batch file called status.bat and written to c:\temp

Total Concurrent Callers: 4
License Ports: 432
Available Ports: 428

What I need to do is ignore what's above this text and then extract the number of licensed & available ports. I'd then like to write these to a SQL database which is a table called "license" and has two columns "totallicense" and "available".

If I run status.bat say every 10 mins it will give me an opportunity to find out what time of day I'm using the most licenses.

tsuji script was great, nice and compact but wasn't quite what I am after, can you help?

 

[dm4ever]

Well if you're just looking at getting those two values you can use what tsuji provided you with a few modifications.

...code
set rx = new regexp
with rx
    .ignorecase=True
    .global=true
    .pattern="(License Ports|Available Ports)(:\s*)(\d+)"
End With

Dim colMatches : Set colMatches = rx.Execute(s)
Dim strLicPorts : strLicPorts = colMatches(0).SubMatches(2)
Dim strAvailPorts : strAvailPorts = colMatches(1).SubMatches(2)

WScript.Echo strLicPorts 
WScript.Echo strAvailPorts

If the above worked then you should be able to use those variables to feed it into your SQL db.

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[tsuji]

This is my effort see if it finally gets the problem over. (I assume each file has only one set of data to extract - not strip off).
[tt]
dim sinfile, soutfile, fso, rx, s, cm, nActiveLicenses, nUnusedLicenses

sinfile="d:\xyz\input.txt" 'your data
soutfile="d:\xyz\output.txt" 'your data: if write to sql db, just use t below and no need of this

set fso=createobject("scripting.filesystemobject")

if not fso.fileexists(sinfile) then
set fso=nothing
wscript.quit 99 'early exit [1]
end if

s=""
if fso.getfile(sinfile).size<>0 then
s=fso.opentextfile(sinfile,1,false).readall
end if

set rx=new regexp

with rx
.ignorecase=true
.global=false
.pattern="(Active Licenses)\s*)(\b\d+\b)"
end with
set cm=rx.execute(s)
if cm.count=0 then
nActiveLicenses="n/a" 'or something depending on the convention for unfound case
else
nActiveLicenses=cm(0).submatches(2)
end if

with rx
.ignorecase=true
.global=false
.pattern="(Unused Licenses)\s*)(\b\d+\b)"
end with
set cm=rx.execute(s)
if cm.count=0 then
nUnusedLicenses="n/a" 'or something depending on the convention for unfound case
else
nUnusedLicenses=cm(0).submatches(2)
end if

'if you want to see persistent data extracted
fso.opentextfile(soutfile,8,true).write "Active Licenses: " & nActiveLicenses & vbcrlf & "Unused Licenses: " & nUnusedLicenses & vbcrlf

set fso=nothing

'nActiveLicenses and nUnusedLicenses are available to feed to the db here
[/tt]