Filter data in log file using VBScript

I have to analyze data from logfile that stored in "C:read" directory, and output the data in the system. There is multiple log file, name "drivermonitor". The logfile contain device name, lotid, socket and wafer result. Each device have different socket quantity and three different wafer result. Filter data is start from "Bin2 Test Started" and wafer result for this logfile is B20000A1, B20000A2, B20000B2

Drivermonitor logfile example:

I already have the script but not working well:

Option Explicit
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objSTR, objEND, objTYP, objEXT, objKEY, objFILE, objKEY2
Dim Folder, SubFolder testfile,b,a,one,two,three,four,five,six,seven,eight,match,array,i,status,array2,array3,array4,array5,array6,d,mambastr,ten,n,eleven,twelve,k,nine
dim dtend,dtStart,bibid,driverid
dim objConnection,objRecordSet,ConnectionString,field,bin2
dim satu,dua,tiga,ti,empat,lima,enam,tujuh,lapan,sembilan,sepuluh,sebelas,duabelas,tigabelas,empatbelas,limabelas,enambelas
dim data1,data2,data3,data4,data5,data6
objSTR = "C:read" 'Folder To search through.
'objEND = "D:SQLDEVbin_miss_matcharchive" 'Folder To copy too.
objEXT = "log" 'do not include leading period '.' on the extension.
objTYP = "Text Document"
objKEY = "drivermonitor" 'keyword To search for along with the extension.
objKEY2 = "devicedata" 'keyword To search for along with the extension.

'outfile.writeline "lotid,date1,date2,bibcode,dontknow,slotno,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60"

OpenADOObjects2

For Each objFILE In objFSO.GetFolder(objSTR).Files
b = objFSO.GetAbsolutePathName(objfile.Path)
Set testfile = objFSO.OpenTextFile(b, 1)
Set one = New RegExp
Set two = New RegExp
Set three = New RegExpa
Set four = New RegExp
Set five = New RegExp
Set six = New RegExp
Set seven = New RegExp
Set eight = New RegExp
Set ten = New RegExp
Set eleven = New RegExp
Set twelve = New RegExp
Set nine = New RegExp
Set sepuluh = New RegExp
Set sebelas = New RegExp
Set duabelas = New RegExp

Set satu = New RegExp
Set dua = New RegExp
Set tiga = New RegExp
Set ti = New RegExp
Set empat = New RegExp
Set lima = New RegExp
Set enam = New RegExp
Set tujuh = New RegExp
Set lapan = New RegExp
Set sembilan = New RegExp
Set tigabelas = New RegExp
Set empatbelas = New RegExp

Set limabelas = New RegExp
Set enambelas = New RegExp

Set data1 = New RegExp
Set data2 = New RegExp
Set data3 = New RegExp

Set data4 = New RegExp
Set data5 = New RegExp
Set data6 = New RegExp
bin2 = 0
n = 0 'default calypso100&176&256 And mamba 324&416
'one.Pattern = "[0-9]+/[0-9]+/[0-9]+s[0-9]+:[0-9]+:[0-9]+s(AM|PM)s-s(FLS|fls|FSL|fsl|KLM|klm)_[A-Za-z]+_[A-Za-z]+_[0-9]+,[0-9]+,[0-9]+/[0-9]+/[0-9]+s[0-9]+:[0-9]+:[0-9]+s(AM|PM),[0-9]+/[0-9]+/[0-9]+s[0-9]+:[0-9]+:[0-9]+s(AM|PM),[0-9]+,[0-9]+,[0-9]+:s"

ten.Pattern = "bDevicesType:s(Space2|SPACE2)"
eleven.Pattern = "bDevicesType:s(Bolero|BOLERO)"
twelve.Pattern = "bDevicesType:s(((Panther|PANTHER)-CC3E)|((Quasar0|QUASAR0|Quasar2|QUASAR2)-CC3E))b"
sepuluh.Pattern = "bDevicesType:s(Calypso|CALYPSO)"
sebelas.Pattern = "bDevicesType:s((Mamba|MAMBA)|4HR|6HR|8HR|12HR|CC3E)(-CC3E|)b"
duabelas.Pattern = "bDevicesType:s(MPC551x|Mpc551X)"

satu.Pattern = "bDevicesType:s(Batray|BATRAY)"
dua.Pattern = "bDevicesType:s(Bonito|BONITO)"
tiga.Pattern = "bDevicesType:s(Flipper|FLIPPER)-(144lds|144LDS)-(CC3|)b"
ti.Pattern = "bDevicesType:s((Flipper|FLIPPER)-(80lds|80LDS))|((Sailfish|SAILFISH)(-|)(80lds|80LDS))"
empat.Pattern = "bDevicesType:s(JELLYFISH|Jellyfish)"
lima.Pattern = "bDevicesType:s(Kipper|KIPPER)-(80lds|80LDS)b"
enam.Pattern = "bDevicesType:s(Knifefish|KNIFEFISH)"
tujuh.Pattern = "bDevicesType:s(Sailfish|SAILFISH)-(144lds|144LDS)"
lapan.Pattern = "bDevicesType:s((SNAKEFISH|Snakefish)-3H-CC3)"
sembilan.Pattern = "bDevicesType:s(YellowFin|YELLOWFIN)"
tigabelas.Pattern = "bDevicesType:s(Kipper|KIPPER)-(144lds|144LDS)"
empatbelas.Pattern = "bDevicesType:s(SpaceBridge|SPACEBRIDGE)"

limabelas.Pattern = "bDevicesType:s(Marlin2|MARLIN2)"
enambelas.Pattern = "bDevicesType:s(Torpedo|TORPEDO)"

one.Pattern = "[0-9]+/[0-9]+/[0-9]+s[0-9]+:[0-9]+:[0-9]+s(AM|PM)s-s(FLS|fls|FSL|fsl|KLM|klm)"
two.Pattern = "-"
two.global = True
three.Pattern = ",000000A1,$"
four.Pattern = ",000000A2,$"
five.Pattern = ",000000B2,$"
'---------------Group 1-------------------------
six.Pattern = ",B20000A1,$"
seven.Pattern = ",B20000A2,$"
eight.Pattern = ",B20000B2,$"
'---------------Group 2-------------------------
data1.Pattern = ",C1,$"
data2.Pattern = ",C2,$"
data3.Pattern = ",D2,$"
'---------------Group 3-------------------------
data4.Pattern = ",00A1,$"
data5.Pattern = ",00C2,$"
data6.Pattern = ",00D2,$"
'---------------Group 4-------------------------
data4.Pattern = ",00A1,$"
data5.Pattern = ",00C2,$"
data6.Pattern = ",00D2,$"

nine.Pattern = "^Bin2.Test Started"

Do Until testfile.AtEndOfStream
a = testfile.ReadLine
'MsgBox a

If (ten.Test(a) = True) Or (empatbelas.Test(a) = True) Then 'space ,spacebridge
n = 28
End If

If (eleven.Test(a) = True) Then 'bolero 100&144&176
n = 55
End If

If (twelve.Test(a) = True) Or (duabelas.Test(a) = True) Then 'quasar0&2 And panther 144&257 And MPC
n = 40
End If

If (sepuluh.Test(a) = True) Or (sebelas.Test(a) = True) Then 'calypso100&176&256 And mamba 324&416
n = 36
End If

If (satu.Test(a) = True) Or (tiga.Test(a) = True)or (tujuh.Test(a) = True) Or (tigabelas.Test(a) = True)then 'batray 112 And flipper 144 And sailfish144 And kipper 144
n = 60
End If


If (dua.Test(a) = True) Or (ti.Test(a) = True) Or (empat.Test(a) = True) Or (enam.Test(a) = True) Or (lapan.Test(a) = True) Or (sembilan.Test(a) = True) Or (lima.Test(a) = True) Then 'bonito And flipper 80 And jellyfish100 And knifefish64&80 And snakefish80&64 And yellowfin And kipper 80 And marlin And torpedo
n = 72
End If
If (limabelas.Test(a)= True)OR (enambelas.Test(a) = True)then
n = 702
End If

If (nine.Test(a) = True) Then
bin2 = 1
End If

If (three.Test(a) = True) Then
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (four.Test(a) = True) Then
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (five.Test(a) = True) Then
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (six.Test(a) = True And bin2 = 1) Then '",B20000A1,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (seven.Test(a) = True And bin2 = 1) Then '",B20000A2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (eight.Test(a) = True And bin2 = 1) Then '",B20000B2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data1.Test(a) = True And bin2 = 1) Then '",000000C1,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data2.Test(a) = True And bin2 = 1) Then '",000000C2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data3.Test(a) = True And bin2 = 1) Then '",000000D2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data4.Test(a) = True And bin2 = 1) Then '",00A1,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data5.Test(a) = True And bin2 = 1) Then '",00C2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If

If (data6.Test(a) = True And bin2 = 1) Then '",00D2,$"
array = Split(a,",",-1,1)
mamba
'outfile.writeline mambastr
additem
End If
Loop
Next

CloseADOObject

Sub mamba
If (n = 28) Then '28 socket
mambastr = ""
'mambastr =array(6)&","& array(7)&","& array(8)&","& array(9)&","& array(10)&","& array(11)&","& array(12)&","& array(13)&","& array(14)&","& array(15)&","& array(16)&","& array(17)&","& array(18)&","& array(19)&","& array(20)&","& array(21)&","& array(22)&","& array(23)&","& array(24)&","& array(25)&","& array(26)&","& array(27)&","& array(28)&","& array(29)&","& array(30)&","& array(31)&","& array(32)& "," &array(33)
For k=6 To 32
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(33)
End If

If (n = 36) Then '36 socket
mambastr = ""
'mambastr =array(6)&","& array(7)&","& array(8)&","& array(9)&","& array(10)&","& array(11)&","& array(12)&","& array(13)&","& array(14)&","& array(15)&","& array(16)&","& array(17)&","& array(18)&","& array(19)&","& array(20)&","& array(21)&","& array(22)&","& array(23)&","& array(24)&","& array(25)&","& array(26)&","& array(27)&","& array(28)&","& array(29)&","& array(30)&","& array(31)&","& array(32)& "," &array(33)& ","&array(34)&","& array(35)&","& array(36)&","& array(37)&","& array(38)&","& array(39)&","& array(40)&","& array(41)
For k=6 To 40
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(41)
End If

If (n = 55) Then '55 socket
mambastr = ""
'mambastr =array(6)&","& array(7)&","& array(8)&","& array(9)&","& array(10)&","& array(11)&","& array(12)&","& array(13)&","& array(14)&","& array(15)&","& array(16)&","& array(17)&","& array(18)&","& array(19)&","& array(20)&","& array(21)&","& array(22)&","& array(23)&","& array(24)&","& array(25)&","& array(26)&","& array(27)&","& array(28)&","& array(29)&","& array(30)&","& array(31)&","& array(32)& "," &array(33)& ","&array(34)&","& array(35)&","& array(36)&","& array(37)&","& array(38)&","& array(39)&","& array(40)&","& array(41) &","& array(42) &","& array(43)&","& array(44)&","& array(45)&","& array(46)&","& array(47)&","& array(48)&","& array(49)&","& array(50) &","& array(51)&","& array(52)&","& array(53)&","& array(54)&","& array(55)&","& array(56)&","& array(57)&","& array(58)&","& array(59)&","& array(60)
For k=6 To 59
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(60)
End If

If (n = 40) Then '40 socket
mambastr = ""
'mambastr =array(6)&","& array(7)&","& array(8)&","& array(9)&","& array(10)&","& array(11)&","& array(12)&","& array(13)&","& array(14)&","& array(15)&","& array(16)&","& array(17)&","& array(18)&","& array(19)&","& array(20)&","& array(21)&","& array(22)&","& array(23)&","& array(24)&","& array(25)&","& array(26)&","& array(27)&","& array(28)&","& array(29)&","& array(30)&","& array(31)&","& array(32)& "," &array(33)& ","&array(34)&","& array(35)&","& array(36)&","& array(37)&","& array(38)&","& array(39)&","& array(40)&","& array(41) &","& array(42) &","& array(43)&","& array(44)&","& array(45)
For k=6 To 44
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(45)
End If

If (n = 60) Then
mambastr = ""
For k=6 To 64
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(65)
End If

If (n = 72) Then
mambastr = ""
For k=6 To 76
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(77)
End If

If (n = 702) Then
mambastr = ""
For k=6 To 76
mambastr = mambastr + array(k)&","
Next
mambastr = mambastr + array(77)
End If
End Sub

Sub OpenADOObjects2
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=SQLNCLI11; Server=192.168.30.11; Database=d.Test2; UID=sa; Pwd=Win32API"
objConnection.Open
Set objRecordSet = CreateObject("ADODB.RecordSet")
objRecordSet.Open "Select * from tb.Test", objConnection, 1, 3
End Sub

Sub CloseADOObject
objRecordSet.Close
objConnection.Close
End Sub

Sub additem
objRecordSet.AddNew
objRecordSet("data")= a
objRecordSet("sockstr")= mambastr
objRecordSet.Update
End Sub


Edit: reconstructed the code.

Option Explicit
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
'dim outfile: Set outfile = objFSO.CreateTextFile("C:UsersfaeqDesktopBIN2 Missmatchoutputtestdata.log",2,true)
'dim outfolder : Set outfolder = objFSO.GetFolder("C:UsersfaeqDesktopDest ")
Dim objSTR, objEND, objTYP, objEXT, objKEY, objFILE, objKEY2
Dim testfile,b,a,dateTime,dash,array,mambastr,sock,k,nine
dim devnames, line, Split
dim objConnection,objRecordSet,ConnectionString,bin2, m
dim waf1,waf2,waf3,waf4, waf5, waf6, waf7, waf8, waf9, waf10, waf11, waf12

objSTR = "C:read" 'Folder to search through.
'objEND = "D:SQLDEVbin_miss_matcharchive" 'Folder to copy too.
objEXT = "log" 'do not include leading period '.' on the extension.
objTYP = "Text Document"
objKEY = "drivermonitor" 'keyword to search for along with the extension.
objKEY2 = "devicedata" 'keyword to search for along with the extension.

'-------------------------------------------------------------------------------
OpenADOObjects2

For Each objFILE in objFSO.GetFolder(objSTR).Files
Set testfile = objFSO.OpenAsTextStream
line = ""
Do Until testfile.AtEndOfStream
line= testfile.ReadLine

set Split =new regexp
set dateTime =new regexp
set dash =new regexp
set a =new regexp
Set m = New regExp
set nine = new regexp
set line = new regexp
Set re2 = New RegExp
Set re = New regExp
Set waf1 = New regExp
Set waf2 = New regExp
Set waf3 = New regExp
Set waf4 = New regExp
Set waf5 = New regExp
Set waf6 = New regExp
Set waf7 = New regExp
Set waf8 = New regExp
Set waf9 = New regExp
Set waf10 = New regExp
Set waf11 = New regExp
Set waf12 = New regExp
re.Pattern = "^DevicesType:s(.*)"
For Each m In re.Execute(line)
devnames = m.Submatches(24)
Next

re2.Pattern = "^[^-]*-|-[^-]*$"
re2.Global = True
devnames = re2.Replace(devnames, "")

Set devnames = CreateObject("Scripting.Dictionary")

devnames.Add "Bolero", True
devnames.Add "Habanero", True
devnames.Add "Spacebridge", True
devnames.Add "Marlin", True
devnames.Add "Torpedo", True
devnames.Add "Calypso", True
devnames.Add "Mamba", True
devnames.Add "Mpc", True
devnames.Add "Panther", True
devnames.Add "Quasar", True
devnames.Add "Swordfish", True
devnames.Add "Batray", True
devnames.Add "Bonito", True
devnames.Add "Flipper", True
devnames.Add "Jelly", True
devnames.Add "Kipper", True
devnames.Add "Knifefish", True
devnames.Add "Sailfish", True
devnames.Add "Snakefish", True
devnames.Add "Yellowfin", True
devnames.Add "Agave", True
devnames.Add "Grusonia", True
devnames.Add "Oroya", True

For Each m In re.Execute(line)
devnames = m.Submatches(24)
Next

bin2 = 0
sock = 0

dateTime.pattern = "[0-9]+/[0-9]+/[0-9]+s[0-9]+:[0-9]+:[0-9]+s(AM|PM)s-s(FLS|fls|FSL|fsl|KLM|klm)"
dash.pattern = "-"
dash.global = True
waf1.pattern = ",000000A1,$"
waf2.pattern = ",000000A2,$"
waf3.pattern = ",000000B2,$"
'---------------Group 1-------------------------
waf4.pattern = ",B20000A1,$"
waf5.pattern = ",B20000A2,$"
waf6.pattern = ",B20000B2,$"
'---------------Group 2-------------------------
waf7.pattern = ",000000C1,$"
waf8.pattern = ",000000C2,$"
waf9.pattern = ",000000D2,$"
'---------------Group 3-------------------------
waf10.pattern = ",00A1,$"
waf11.pattern = ",00C2,$"
waf12.pattern = ",00D2,$"

If devnames.Exists("Bolero") or devnames.Exists("Mpc") Then
sock = 55
End If
If devnames.Exists("Habanero") Then
sock = 20
End If
If devnames.Exists("Marlin") or devnames.Exists("Torpedo") or devnames.Exists("Bonito") or devnames.Exists("Flipper") or devnames.Exists("Jelly") or devnames.Exists("Kipper") or devnames.Exists("Knifefish") or devnames.Exists("Sailfish") or devnames.Exists("Snakefish") or devnames.Exists("Yellowfin")Then
sock = 72
End If
If devnames.Exists("Calypso") Then
sock = 36
End If
If devnames.Exists("Mamba") or devnames.Exists("Spacebridge") Then
sock = 28
End If
If devnames.Exists("Panther") or devnames.Exists("Quasar") Then
sock = 40
End If
If devnames.Exists("Swordfish") or devnames.Exists("Batray") Then
sock = 60
End If
If devnames.Exists("Agave") Then
sock = 128
End If
If devnames.Exists("Grusonia") or devnames.Exists("Oroya") Then
sock = 120
End If

Do Until testfile.AtEndOfStream
a = Split(testfile.ReadLine, ",")

if (nine.test(a) = true) then
bin2 = 1
end if

if (waf1.test(a) =true) then
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf2.test(a) =true) then
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf3.test(a) = true) then
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf4.test(a) =true and bin2 = 1) then '",B20000A1,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf5.test(a) =true and bin2 = 1) then '",B20000A2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf6.test(a) =true and bin2 = 1) then '",B20000B2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf7.test(a) =true and bin2 = 1) then '",000000C1,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf8.test(a) =true and bin2 = 1) then '",000000C2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf9.test(a) =true and bin2 = 1) then '",000000D2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf10.test(a) =true and bin2 = 1) then '",00A1,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf11.test(a) =true and bin2 = 1) then '",00C2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if

if (waf12.test(a) =true and bin2 = 1) then '",00D2,$"
a = Split(testfile.ReadLine, ",")
mamba
'outfile.writeline mambastr
additem
end if
loop
v = a(UBound(a)-1)
loop
next

CloseADOObject

Sub mamba
if (sock = 28) then
mambastr = ""
For k=6 to 32
mambastr= mambastr + a(k)&","
next
mambastr = mambastr + a(33)
end if

if (sock = 36) then
mambastr = ""
For k=6 to 40
mambastr= mambastr + a(k)&","
next
mambastr = mambastr + a(41)
end if

if (sock = 55) then
mambastr = ""
For k=6 to 59
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(60)
end if

if (sock = 40) then
mambastr = ""
For k=6 to 44
mambastr= mambastr + a(k)&","
next
mambastr = mambastr + a(45)
end if

if (sock = 60) then
mambastr = ""
For k=6 to 64
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(65)
end if

if (sock = 72) then
mambastr = ""
For k=6 to 76
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(77)
end if
if (sock = 20) then
mambastr = ""
For k=6 to 24
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(25)
end if
if (sock = 128) then
mambastr = ""
For k=6 to 132
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(133)
end if
if (sock = 120) then
mambastr = ""
For k=6 to 124
mambastr=mambastr + a(k)&","
next
mambastr = mambastr + a(125)
end if
end sub
testfile.Close

sub OpenADOObjects2
Set objConnection = CreateObject("ADODB.connection")
objConnection.ConnectionString = "Provider=SQLNCLI11; Server=192.168.30.11; Database=dbtest2; UID=sa; Pwd=Win32API"
objConnection.Open
set objRecordSet = CreateObject("ADODB.RecordSet")
objRecordSet.Open "Select * from tbltest",objConnection,1,3

end sub

sub CloseADOObject
objRecordSet.Close
objConnection.Close
end sub

sub additem
objRecordSet.AddNew
objRecordSet("data")= a
objRecordSet("sockstr")= mambastr
objRecordSet.Update
end sub


RegExp

RegExp

1 Answer
1

This may not actually answer your question (because it's not entirely clear to me what your question is in the first place), but should offer some general guidelines for improving your code.

Usually when processing CSV-style files the best approach is to read the file line by line, split each line, then base the further processing on the values of specific fields in the resulting array.

For Each f In objFSO.GetFolder(objSTR).Files
Set testfile = f.OpenAsTextStream
Do Until testfile.AtEndOfStream
a = Split(testfile.ReadLine, ",")
'...
'further processing occurs here
'...
Loop
testfile.Close
Next


For opening a file it's not necessary to get the full path from a File object first, then call OpenTextFile with that path. You can directly open a text file from the File object with the object's OpenAsTextStream method.

File

OpenTextFile

File

OpenAsTextStream

If you have a file format that prepends CSV-style data with a differently formatted header I'd recommend processing that header first in a separate loop:

For Each f In objFSO.GetFolder(objSTR).Files
Set testfile = f.OpenAsTextStream
line = ""
Do Until testfile.AtEndOfStream Or InStr(line, "Test Started at") > 0
line = testfile.ReadLine
'...
'process headers here
'...
Loop
Do Until testfile.AtEndOfStream
a = Split(testfile.ReadLine, ",")
'...
'process CSV data here
'...
Loop
testfile.Close
Next


Extract the device type from the header section with a single regular expression:

Set re = New RegExp
re.Pattern = "^DevicesType:s(.*)"
For Each m In re.Execute(line)
devtype = m.Submatches(0)
Next


Given your example the variable devtype will then contain the value "ENG-Bolero1M-4HR". You can remove unwanted prefixes and/or suffixes from that string e.g. like this:

devtype

Set re2 = New RegExp
re2.Pattern = "^[^-]*-|-[^-]*$"
re2.Global = True
devtype = re2.Replace(devtype, "")


The above will change the string "ENG-Bolero1M-4HR" to "Bolero1M".

Use a dictionary for mapping the extracted strings to other values:

Set devtypes = CreateObject("Scripting.Dictionary")
devtypes.Add "Bolero1M", 55
...

nval = devtypes(devtype)


or for basing decisions on the presence of a value:

Set devtypes = CreateObject("Scripting.Dictionary")
devtypes.Add "Bolero1M", True
...

If devtypes.Exists(devtype) Then
...
End If


In the CSV section you extract the value of the second-last field like this:

v = a(UBound(a)-1)


Also, if you find yourself doing the same (or very similar) operations repeatedly in your code, you should consider wrapping those operations in a procedure or function. And use descriptive names for procedure/function/variable names.

devnames.Exists(devname)

devname

devnames

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.