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.
objFileA.Name = “WORKFILEA_” & strDate & “.txt”

Set objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
Else
Set objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
End If

‘Verify if second work file WORKFILED.txt exists.
If objFSO.FileExists(”C:\WORK\WORKFILED.txt”) Then
Set objFileD = objFSO.GetFile(”C:\WORK\WORKFILED.txt”)
fileDate = objFileD.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.
objFileD.Name = “WORKFILED_” & strDate & “.txt”

Set objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
Else
Set objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
End If

‘ Start iterating through the main file and begin split. Look for the first character to be either an ‘A’ or a ‘D’.
Do Until objFile.AtEndOfStream
strCharacter = objFile.Read(1)

If strCharacter = “A” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileA.WriteLine strLine
End If

If strCharacter = “D” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileD.WriteLine strLine
End If
Loop

‘ Destroy objects.
objFile.Close
objFileA.Close
objFileD.Close

MsgBox “Files have been processed.”

Tags: SQL Server, text files, VBScript, VbsEdit