How To Parse A Text File With VBScript!

I thought today we would switch gears from SQL Server and T-SQL and do something a little more exciting. Today I am going to introduce you to VBScript and what it can do for a DBA. Now some of you may already know VBScript and some of you may not which is ok. I have found VBScript useful in splitting text files, creating text files, interacting with files on the OS level, etc. Today’s example will be splitting a text file into 2 separate files.

You can write VBScript in any type of text editor and save the file with a .vbs extension. I have downloaded VbsEdit from here. The interface is straight forward and it is easy to use. Below is the script that parses one main work file:

‘ ****************************************
‘ **
‘ ** WORK FILE SPLITTER
‘ ** Brett Davis
‘ ** 2007.11.01
‘ **
‘ ****************************************

‘ Declare variables.
Dim fileDate
Dim objFSO
Dim objFile
Dim objFileA
Dim objFileD
Dim strDate
Dim strLine
Dim strCharacter

Set objFSO = CreateObject(”Scripting.FileSystemObject”)

‘ Get Work File.
Set objFile = objFSO.OpenTextFile(”C:\WORK\WORKFILE.WRK”, 1)

‘ Verify if first work file WORKFILEA.txt exists.
If objFSO.FileExists(”C:\WORK\WORKFILEA.txt”) Then
Set objFileA = objFSO.GetFile(”C:\WORK\WORKFILEA.txt”)
fileDate = objFileA.DateLastModified

strDate = DatePart(”yyyy”, fileDate) & Right(”0″ & _
DatePart(”m”, fileDate), 2) & Right(”0″ & DatePart(”d”, fileDate), 2)

‘ Append date to file if it already exists.
 Set objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
Else
Set objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
End If

‘ Append date to file if it already exists.
 Set objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
Else
Set objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
End If