VBScript to read binary files and load data into Excel - Life in the old dog yet





4.00/5 (7 votes)
VBScript may be old and ugly, but there are some quick and dirty tricks for which it is still really handy.
Why bother?
The snag with VBScript is that it is ugly, old, and very very useful. I still find myself using this ancient technology from time to time, and wanted to post an example of the sort of thing it can still be used for if you want something 'quick and dirty'. Here is an example of doing a couple of things we don't always associate with VBScript: reading binary files and loading data into Excel.
Reading binary files
VBScript can read binary files because it can consume COM objects and ADO is COM, and it can read binary files. Here is a function which does that very thing:
function GetGifComment(gifFilePath)
dim inStream,buff,commentLen,commentStr,myIndex
dim myByte,myByteValue,myCharacter
set inStream=WScript.CreateObject("ADODB.Stream")
inStream.Open
inStream.type=1
inStream.LoadFromFile gifFilePath
buff=inStream.Read()
inStream.Close
commentStr=""
for myIndex = 1 to 6
' Extract 1 byte from the buffer
myByte = MidB(buff,myIndex,1)
' Gets its numeric value
myByteValue = AscB(myByte)
' Convert that numeric value into a character
myCharacter = Chr(myByteValue)
' Append that character to the string
commentStr = commentStr & myCharacter
next
GetGifComment = commentStr
end function
The above function uses a ADODB.Stream
object. I guess these were designed for loading files into databases; however, they work nicely for reading small binary files from VBScript. I suspect the entire file is read into memory so I would not try loading huge files this way. Once we have the file in memory, we can get at its contents in a local variable via its read
method (again, not efficient - but that is not the point). Once the contents of the file are in a local variable, they can be inspected using the MidB
, AscB
, and Chr
built-in functions.
Putting this to use
In the below script, I have used the WScript.Shell
object to then get at the SpecialFolders
enumeration which has given me access to the current user's desktop directory. I can then use Scripting.FileSystemObject
to look for any GIF files on the desktop. I also directly open an Excel instance and create inside it a new workbook. The final trick is then to look for any GIF files on the desktop and use my binary file trick to read inside the file to get the GIF version type. The name of the file and the version are then listed in Excel.
Option Explicit
Dim myShell, sendToFolder, myPicturesPath, myShortcut
Dim fso,myFolder, myFile, fileName, comment, myExcel
Dim myWorkbook, myRow, mySheet
' Find "My Pictures"
Set myShell = CreateObject("WScript.Shell")
myPicturesPath = myShell.SpecialFolders("Desktop")
' Open "My Pictures" as a folder so we can see
' which files are inside it
Set fso=CreateObject("Scripting.FileSystemObject")
Set myFolder=fso.GetFolder(myPicturesPath)
' Set Up Excel To receive The Data
Set myExcel=CreateObject("Excel.Application")
Set myWorkBook=myExcel.WorkBooks.Add
Set mySheet=myWorkBook.Sheets(1)
myRow=2
mySheet.Cells(1,1).Value="Name"
mySheet.Cells(1,2).Value="GIF Type"
myExcel.Visible=TRUE
' Loop through each file found and see
' if its file extension is .gif
' If a file is a .gif file then call our function
' which opens it as a binary file and reads the
' version label
for each myFile in myFolder.Files
fileName=myFile.name
fileName=Lcase(fileName)
if Right(fileName,4)=".gif" then
' Read the version label
comment=GetGifComment(myFile.path)
' Place the data in the spreadsheet
mySheet.Cells(myRow,1).Value=fileName
mySheet.Cells(myRow,2).Value=comment
' Step down to the next Row
myRow=myRow+1
else
WScript.echo fileName
end if
next
' Make the spreadsheet look a bit nicer
With mySheet.Range("A1:B1").Font
.FontStyle = "Bold"
.Size = 12
End With
mySheet.Columns(1).Autofit
mySheet.Columns(2).Autofit
'Script ends here
function GetGifComment(gifFilePath)
dim inStream,buff,commentLen,commentStr,myIndex
dim myByte,myByteValue,myCharacter
set inStream=WScript.CreateObject("ADODB.Stream")
inStream.Open
inStream.type=1
inStream.LoadFromFile gifFilePath
buff=inStream.Read()
inStream.Close
commentStr=""
for myIndex = 1 to 6
' Extract 1 byte from the buffer
myByte = MidB(buff,myIndex,1)
' Gets its numeric value
myByteValue = AscB(myByte)
' Convert that numeric value into a character
myCharacter = Chr(myByteValue)
' Append that character to the string
commentStr = commentStr & myCharacter
next
GetGifComment = commentStr
end function
Conclusions
Is this a really useful script by itself - I doubt it. However, the various bits of it show how a script can be really handy in those nasty system admin or data management tasks for which firing up Visual Studio and writing a full blown application might be overkill. I suspect a lot of this could also be done at least as well in PowerShell. Maybe that should be a topic for another post?