Find missing values between text files using VBScript

Question

EDIT 2

Just to make it clear - this question is not asking for "DA CODEZ", just an idea as to possible approaches in VBScript. It's not my weapon of choice, and if I needed to do this on a un*x box then I wouldn't be bothering anyone with this...

I have two text files. File A contains a list of keys:

00001 13756 000.816.000 BE2B
00001 13756 000.816.000 BR1B
00002 16139 000.816.000 BR1B
00001 10003 000.816.000 CH1C
00001 10003 000.816.000 CH3D
00001 13756 000.816.000 CZ1B
....
....

File B, tab separated, contains two columns the keys, and a UUID:

00003 16966 001.001.023 2300    a3af3b1d-ea04-4948-ba25-59b36ae364ae
00001 12119 001.001.023 CZ1B    e6efe825-0759-48b0-89b9-05bbe5d49625
00002 16966 001.001.023 BR1B    d3a1d62b-a0d5-43c3-ba49-a219de5f32a5
00001 12119 001.001.023 BR1B    5d74af27-ed4b-4f90-8229-90b6d807515b
00001 10009 001.001.024 BR1B    590409cc-496a-49eb-885c-9bbc51863363
00002 24550 001.001.024 2100    46ecea5d-f8f5-4df9-92cf-0b73f6c81adc
00001 12119 001.001.024 CZ1B    e415ce6f-7394-4a66-a7f8-f76487e78086
00002 16966 001.001.024 CZ1B    c591a726-4d71-4f61-adfd-63310d21d397
....
....

I need to extract, using plain VBScript, the UUIDs for those entries in File B which have no matching entry in File A. (I need to optimise for speed, if that's an important criteria.) The result should be a file of orphaned UUID codes.

If this isn't easy/possible, that's also an answer - I can do it in the db we're using but the performance is woefully inadequate. Just been blown away by how much faster VBScript was than db solution for a previous processing task.

EDIT

Someone's suggested using some sort of ADO library, after converting the file to CSV, which I'm looking into.

What have you tried do you have some code you can share?, as you are probably well aware [so] is not *code* for "GIV ME DA CODEZ!". If not see [ask] and providing a [mcve]. — user692942, Sep 15 '16 at 11:20
What kind of volumes are we talking about here?, if the database can't handle it I doubt VBScript is the way to go. — user692942, Sep 15 '16 at 11:23
No indeed Lankymart. I will endeavour so to do, shortly once I've tried to pick apart ADO stuff. — Dycey, Sep 15 '16 at 11:23
About 400 files. Mode, about 1k lines, max about 2.5m lines. — Dycey, Sep 15 '16 at 11:24
Wow, not a job I would entrust to VBScript, what is your database architecture? — user692942, Sep 15 '16 at 11:26

score 2 · Accepted Answer · answered Sep 15 '16 at 11:39

2

Maybe the fastest way to do it is just to ask the OS to do it

Dim orphan
    orphan = WScript.CreateObject("WScript.Shell").Exec( _ 
        "findstr /g:keys.txt /v uuids.txt" _ 
    ).StdOut.ReadAll()

    WScript.Echo orphan

That is, use findstr to check the uuids.txt file for lines that do not match any of the lines in the keys.txt file

answered Sep 15 '16 at 11:39

MC ND

69,615
8
84
126

Thanks @MCND Just so you know - this shaved a 1h49min process to less than 2 mins ;-) I think that's a 60x gain ;-) Much appreciated. It's always wkward when you're not on familiar turf, and Windows scripting is quite unfamiliar to me. One small point - there's an answer worth a read for the [issues with Findstr](http://stackoverflow.com/questions/8844868/what-are-the-undocumented-features-and-limitations-of-the-windows-findstr-comman). – Dycey Sep 15 '16 at 13:48
Decent approach. Was VBScript always a requirement then @Dycey or could you just as easily do this from the command line or batch file? – user692942 Sep 15 '16 at 14:36
The server environment is locked down. It is however possible to run a VBScript from within a FileMaker schedule. It's awkward and uncomfortable, and entirely unfamiliar. The whole thing is maneuvering an RV down a side-alley backwards in a blindfold, listening to thrash metal all the way up to 11. There seems to be no "easily" for this particular job. ;-) – Dycey Sep 16 '16 at 07:30

Find missing values between text files using VBScript

EDIT 2

EDIT

1 Answers1