| ' ----------------------------------------------------
' Debug routines
' ----------------------------------------------------
sub raise(msg)
Err.Raise 10000, "Runtime Error", msg
end sub dim g_flagLogEnabled : g_flagLogEnabled = True
dim g_vapLog : g_vapLog = Null sub SetVAPLog(vapLog)
set g_vapLog = vapLog
end sub sub puts(str)
if g_flagLogEnabled then
if not IsNull(g_vapLog) then
g_vapLog.Log str
end if
end if
end sub sub EnableLog(bEnabled)
g_flagLogEnabled = bEnabled
end sub ' ----------------------------------------------------
' sub: CreateCSVSchema
' Generate a schema file for a CSV file
' ----------------------------------------------------
sub CreateCSVSchema(byval csvFullName)
dim csvPath, csvFileName
call ExtractFileName(csvFullName, csvPath, csvFileName) dim schemaFileName
schemaFileName = csvPath & "schema.ini" dim fso, file
set fso = CreateObject("Scripting.FileSystemObject")
set file = fso.CreateTextFile(schemaFileName)
file.WriteLine("[" & csvFileName & "]")
file.WriteLine("ColNameHeader=False")
file.WriteLine("Format=CSVDelimited")
dim colcount
colcount = GetCsvColumnCount(csvFullName)
dim i
for i = 1 to colcount
file.WriteLine("Col" & i & "=Column" & i & " Text")
next file.Close
end sub ' ----------------------------------------------------
' sub: ExtractFileName
' Extract a filename to path and filename
' ----------------------------------------------------
sub ExtractFileName(byval fullpath, byref path, byref file)
dim pos
pos = InStrRev(fullpath, "\") if pos <> 0 then
' split the path and filename
path = mid(fullpath, 1, pos)
file = mid(fullpath, pos + 1)
else
' if path is not specified, use the directory of current script as default
path = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
file = fullpath
end if
end sub ' ----------------------------------------------------
' function: GetCsvColumnCount
' returns the count of columns in CSV
' ----------------------------------------------------
function GetCsvColumnCount(byval filename)
dim adoCsvConnection, adoCsvRecordset
dim csvPath, csvFileName
call ExtractFileName(filename, csvPath, csvFileName) ' open connection to the csv file.
set adoCsvConnection = CreateObject("adodb.connection")
set adoCsvRecordset = CreateObject("adodb.recordset") ' open csv file with header line.
adoCsvConnection.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & csvPath & ";" & _
"extended properties=""text;hdr=no;fmt=delimited""" adoCsvRecordset.open "select * from " & csvFileName, adoCsvConnection
dim fields: fields = adoCsvRecordset.fields.count ' clean up.
adoCsvRecordset.close
adoCsvConnection.close GetCsvColumnCount = fields
end function ' ----------------------------------------------------
' function: ReadCsvFile
' returns the content of CSV to an 2 dimensions array
' ----------------------------------------------------
function ReadCsvFile(filename)
puts "ReadCsvFile: " & filename
dim csvPath, csvFileName
dim fso : set fso = CreateObject("Scripting.FileSystemObject") call ExtractFileName(filename, csvPath, csvFileName)
filename = csvPath + csvFileName ' Create a temp directory
csvPath = csvPath + "\temp\"
if not fso.FolderExists(csvPath) then fso.CreateFolder csvPath ' Replace blanks and () to under score
' if the source CSV file contains space, the SQL statement will be invalid
dim regex : set regex = new RegExp
regex.Pattern = "[\s\(\)]"
regex.Global = True
csvFileName = regex.replace(csvFileName, "_") regex.Pattern = "\.csv\d+"
regex.Global = true
csvFileName = regex.replace(csvFileName, ".csv") puts("Copy CSV file from " & filename & " to " & csvPath + csvFileName)
' Copy the csv file to temp dir
fso.CopyFile filename, csvPath + csvFileName
filename = csvPath + csvFileName
' uses string for all fields
CreateCSVSchema(filename) dim adoCsvConnection, adoCsvRecordset ' open connection to the csv file.
set adoCsvConnection = CreateObject("adodb.connection")
set adoCsvRecordset = CreateObject("adodb.recordset") ' open csv file with header line.
adoCsvConnection.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & csvPath & ";" & _
"extended properties=""text;hdr=no;fmt=delimited""" adoCsvRecordset.open "select count(*) from " & csvFileName, adoCsvConnection
dim rowcount: rowcount = adoCsvRecordset.fields(0).value
adoCsvRecordset.close adoCsvRecordset.open "select * from " & csvFileName, adoCsvConnection
dim fields: fields = adoCsvRecordset.fields.count dim arr()
redim arr(rowcount - 1, fields - 1) ' read the csv file.
dim row: row = 0
dim k do until adoCsvRecordset.eof
' display all fields.
for k = 0 to adoCsvRecordset.fields.count - 1
arr(row, k) = adoCsvRecordset.fields(k).value
next adoCsvRecordset.movenext
row = row + 1
loop ' clean up.
adoCsvRecordset.close
adoCsvConnection.close ReadCsvFile = arr
end function ' ----------------------------------------------------
' function: cell
' returns the cell of table by row and col
' row: >= 1
' col: from 'A' ~ 'Z'
' ----------------------------------------------------
function cell(table, row, col)
dim i, j
i = row - 1
j = asc(col) - asc("A")
cell = table(i, j)
end function ' ----------------------------------------------------
' function: LPad
' Pad spaces to the left of a string
' ----------------------------------------------------
function LPad(str, width)
dim length: length = len(str)
if length < width then
LPad = space(width - length) + str
else
LPad = str
end if
end function ' ----------------------------------------------------
' function: RPad
' Pad spaces to the right of a string
' ----------------------------------------------------
function RPad(str, width)
dim length: length = len(str)
if length < width then
RPad = str + space(width - length)
else
RPad = str
end if
end function ' ----------------------------------------------------
' function: FindRow
' Find the row with key in column[col] in a table
' col: from 'A' to 'Z'
' return value: row index based on 1.
' ----------------------------------------------------
function FindRow(table, col, key)
dim iRow, iCol : iCol = asc(col) - asc("A")
dim value for iRow = lbound(table) to ubound(table)
value = table(iRow, iCol)
if value = key then
FindRow = iRow - 1
exit function
end if
next
FindRow = -1
end function ' ----------------------------------------------------
' function: LeadZero
' Padding zero to a number
' ----------------------------------------------------
function LeadZero(n, width)
dim s : s = CStr(n)
if len(s) < width then
LeadZero = String(width - len(s), "0") + s
exit function
end if
LeadZero = s
end function ' ----------------------------------------------------
' Enumerate IBPA files in a joined format
' ----------------------------------------------------
function EnumerateFiles(rootPath, key)
dim folder: set folder = CreateObject("Scripting.FileSystemObject").GetFolder(rootPath & "\" & key)
dim file, list
for each file in folder.Files
EnumerateFiles = JoinString(EnumerateFiles, file.Path, "|")
next
end function ' ----------------------------------------------------
' function: FormatString
' returns a formatted string
' fmtstr: a string like "Hello {1}, Welcome {2}."...
' ----------------------------------------------------
function FormatString(fmtstr, args())
dim i, s : s = fmtstr
for i = lbound(args) to ubound(args)
s = replace(s, "{" & CStr(i + 1) & "}", args(i))
next
FormatString = s
end function ' ----------------------------------------------------
' function : FindString
' Find the first matched string in an array
' ----------------------------------------------------
function FindString(arr, substr)
dim i
for i = LBound(arr) to UBound(arr)
if InStr(UCase(arr(i)), UCase(substr)) > 0 then
FindString = arr(i)
exit function
end if
next
FindString = Empty
end function ' ----------------------------------------------------
' function : JoinString
' Join a string with another string
' ----------------------------------------------------
function JoinString(original, newItem, delimiter)
if Len(original) = 0 then
JoinString = newItem
else
JoinString = original & delimiter & newItem
end if
end function | 
