Of Batch Files, Double-Quoted Comma-Delimited Text Files, and VBScripts

Hello World!

It's been such a long time since the last post. Almost 2 years! Time does fly.

Why the sudden resurrection of this blog? Let's just say it's the desire to learn has been rekindled!

I'd mentioned on several previous posts (I think I did at least) on this blog that I used to work as a technical translator for a software dev company back in the Philippines. The team I served with my language skills (greatly diminishing instead of improving - I cringe at the multiple and redundant grammatical errors my posts here contain!!) were Java developers. They tried to teach me how to code in Java. I remember that "Beer Song" exercise which when I'd resolved it, I dropped Java like a too hot pancake in my hands. Since it had splattered on the floor already, why bother picking it up? There were other more interesting and yummy confections at that time (ehem - like the HP Mini 311 Darwin Project at InsanelyMac.com) to bite into!

I remember explaining to my then team mate and close friend (who's a freakin' awesome Java programmer) that I just couldn't accept why some stuff (methods? classes? etc.) must be declared first. On retrospect now,  perhaps there was just the general lack of a motivation for me to force my brain to wrap itself around the proclivities of decent Java coding: i.e. the need to make things work.

For I now realize I did end up dipping my elbows in code when I was finding my way through the cog works of hackintoshing, specifically DSDT patching. I was surprised to be informed of my the techie team mates that it involved, in fact, coding at a much much lower system level than say the commonly known object-oriented programming languages like Java, Python, etc.. Java worked inside the Operating System and interacted mostly with the files and file system therein, while dealing with DSDT meant interacting with the BIOS. (More info on DSDT here).

Now what was different from this machine gibberish from Java when they're both code? There was a requirement - specifically a personal need - my dream of experiencing having a Mac ^_^

I had to patch my own DSDT to make sure low system level processes like wake-up from sleep triggered by mechanical actions like closing the lid, hibernation, fan speed, etc. for my own hackintoshes because most of the hacking guides available were for North America models of said machines and I was in Asia. I had to do a lot comparison and reading the code lines of those other hackers posted and looked out for patterns. Some object names would differ by one character between those posted codes and the actual DSDT extracted from my own Asian release model laptop or netbook.

It was trial and error, testing, and squeezing out comprehension (read the original post from 2009!) from cryptic lines as the example section below:

I remember this is to correctly trigger sleep from closing the laptop lid and if my memory serves me right, after long hours of sleepless nights, in order to arrive at this, I tried adding 'S' to the 'LID' entries in there and adding if-then lines. I remember the dull headache when I had ran out of logical things to try even after scouring the interwebs and even downloading one manual laptop BIOS and system events (ACPICA and always maintained). Miraculously, it was the key (adding that 'S' in there and if-then logic)!

Though it was the most boring piece of literature I ever laid eyes on, I don't regret reading the manual because I was able to deduce some of the lines' function . I put all I could understand after  those double forward slashes "//" when saving and sharing my resulting dsdt.aml file to the hackintosh community that I considered my friends, mentors, and educators.

Those eureka moments of "Oh so that 0 there is closed lid and 1 for open lid!" were fun and exhilirating even ("nakaka-kilig" in my native Tagalog). Perhaps it was akin to how Jean-François Champollion felt when he finally deciphered the Egyptian heiroglyphs after years of studying the Rosetta Stone.

Of course, if you asked me if I still understand DSDT now, the answer is an honest "No, not at all". Hehehe. There's just no urgency for my lazy brain to even switch on and make an effort to re-learn, that after all, I've been blessed now with a real Mac (Finally! Thank You, Lord, for the blessing!) - why bother?

Recently, I had to find a way to manipulate .csv files (comma-delimited text files) to do a sort-of-vlookup process and all to be done via batch file to complete a "project". It was a challenge for me.

I hate Excel, formulas, and numbers in general. It's beyond question that I even try to learn macros much more VBA. It's natural human instinct to stay away from things we abhor, right?

But the problem of making that vlookup routine work in an automation project piqued my interest and I was hooked like a fish to line and bait. To what extent was I obsessed to figure how things worked? Well, the fact that I gave up my time for watching Korean dramas each night to spend it instead in lurking in Stackoverflow forums should give you an idea. I was interested enough to take some of my personal time outside of working hours even if this was work-related (because I still had to deliver my cases - enter those orders in SAP everyday; there wasn't just time!) and managed to make space for tinkering in between other responsibilities at church and personal quiet time (Bible time, yehey!!) that had to keep (for I cannot survive without them!).

I'd say the fasting from all Hallyu was far from futile for I was rewarded with a VB Script that I only needed to edit a bit - after I understood how it worked first - and it became the missing, crucial piece of the puzzle to make that automation work.

By "understanding" I refer to doing that familiar method of trial and error and pattern search similar to that problem of DSDT / BIOS stuff in my old days of hackintoshing. Finally, after a late night testing, the lines of heiroglyphs - err, sorry code - made sense.  (Praise God!!)

I present to you for scrutiny and judgment, the discoveries :

Disclaimer: I admit they may be wrong: it's the first time I ever looked into VBScript which my expert dev cousin says is so outdated. In my mind, I imagine it may well have come all the way back from the Palelithic age! Hehehe 
*credits for this specimen of wonderful VBScript (the expert guy Bill Prew says it's ' Pure' - anything pure is good in my vocab just like pure orange juice hehehe): Experts Exchange forums

....

Set oInFile1 = oFSO.OpenTextFile(cInFile1, cForReading)

Do Until oInFile1.AtEndOfStream 'reads the file until the last row

aLine = Split(oInFile1.Readline, ",") 'reads lines in file, taking entries as individual items as spearated by a comma / delimiliter

If Not oDict.Exists(aLine(0)) Then 'adds each item and corresponding values to the dictionary array where we will use later (loaded into memory?)

oDict.Add aLine(0), aLine(1) 'there are 2 items per line, 0 and 1 - begins with 0!!

   End If

Loop

oInFile1.Close

Set oInFile2 = oFSO.OpenTextFile(cInFile2, cForReading) 'is the table I'm looking up matching values in

Set oOutFile = oFSO.OpenTextFile(cOutFile, cForWriting, True) 'this is for writing the output csv file

Do Until oInFile2.AtEndOfStream 'just so that we get to that last entry!!

aLine = Split(oInFile2.Readline, ",") 'reads lines in file, taking entries as individual items as spearated by a comma / delimiter

ReDim Preserve aLine(UBound(aLine)+1) 'inserts a new item in the rows

aLine(5) = aLine(4) 'moves 3rd item in row to the 4th 

aLine(4) = aLine(3) 'moves 2nd item in row to the 3rd

If oDict.Exists(aLine(2)) Then 'this is the lookup part - checks if 3rd item in a row has a dictionary entry

aLine(3) = oDict.Item(aLine(2)) 'loads the value of that item found rom the dictionary in memory to the 4th item in a row

different

End If

oOutFile.WriteLine Join(aLine, ",") 'joins the items in a file yay!

Loop

.....

Now how to make Excel palatable? because I'm convinced (by my cousin) that I need to acquire VBA for Excel literacy.