Extract certain substring from string in VB 6.0

Dear all,

This is a tricky situation. I want to extract a certain substring from the strings given below, using VB 6.0.
I process each string (row) in a for loop one by one.


13-08-2008,10:30:00,30,13-08-2008,10:29:00,No Consumption,13-08-2008,10:29:00,Voltage Cut,-,13-08-2008,10:33:00,AC Power Down,-,13-08-2008,10:35:00,AC Power Down,-
13-08-2008,11:00:00,20,13-08-2008,11:14:00,Voltage Cut,-,13-08-2008,11:14:00,AC Power Down,-
13-08-2008,11:30:00,20,13-08-2008,11:30:00,AC Power Down,-
04-08-2008,19:00:00,527,04-08-2008,18:47:00,AC Power Down,-
25-08-2008,18:30:00,254,25-08-2008,18:25:00,AC Power Down,-
25-08-2008,19:00:00,0,25-08-2008,19:09:00,Current Reversal,25-08-2008,19:12:00,AC Power Down,-
27-08-2008,12:00:00,560,27-08-2008,11:59:00,AC Power Down,-,27-08-2008,11:59:00,Voltage Cut,-
27-08-2008,15:00:00,0,27-08-2008,15:12:00,Voltage Cut,-
27-08-2008,16:30:00,350,27-08-2008,16:17:00,Meter Opened,27-08-2008,16:20:00,Voltage Cut,-
28-08-2008,06:30:00,30,28-08-2008,06:16:00,Voltage Cut,-
28-08-2008,15:00:00,320,28-08-2008,14:46:00,Zero Tolerance,28-08-2008,14:47:00,AC Power Down,-,28-08-2008,14:49:00,Voltage Cut,-
28-08-2008,17:30:00,10,28-08-2008,17:19:00,Voltage Cut,-
29-08-2008,00:30:00,100,29-08-2008,00:36:00,No Consumption,29-08-2008,00:38:00,Voltage Cut,-,29-08-2008,00:39:00,AC Power Down,-
29-08-2008,01:00:00,80,29-08-2008,01:00:00,AC Power Down,-


I am checking for two major events.
1. Voltage Cut
2. Ac Power Down

In each string I want find the first occurence of a "Voltage Cut" or "AC Power Down" event (whichever comes first).
After finding the first occurence in the string, I want to extract the "time" when a Voltage Cut or Ac Power Down event occurs.

As you can see in each of the strings above, before a Voltage Cut or Ac Power Down event happens, the most previous data before them is the "time" > Want to extract this value.
So for my data above, I should have something like this:


10:29:00
11:14:00
11:30:00
18:47:00
18:25:00
19:12:00
11:59:00
15:12:00
16:20:00
06:16:00
14:47:00
17:19:00
00:38:00
01:00:00


This has become tricky, due to the inclusion of other non-important events such as, "No consumption", "Meter Opened", "Zero Tolerance" and etc, which I want to ignore.

All help is appreciated.

If you have an hour or so to spare, spend it on a Regular Expressions (en.wikipedia.org/wiki/Regular_expression) tutorial (several linked from the article). They are the ideal approach to this sort of problem, and investing the time now will quickly pay off.

To use them in VB, you need to set a reference to the "Microsoft VBScript Regular Expressions" library.

There are other approaches though.

The Instr (http://msdn.microsoft.com/en-us/library/aa445031(VS.60).aspx) function returns the position of a substring within another string; you optionally pass the starting point to begin the search. If the substring is not found within the target string, a value of 0 is returned.

This function, in combination with the MID (http://msdn.microsoft.com/en-us/library/aa445073(VS.60).aspx) function, could be used to build your function.


However, it would get complex, even with just the two variables you're searching for, as you have a bi-state for the return value from MID. (0 means the value was not found, value greater than 0 means the position that it was found. So, you could have neither search value being found, one of the two values being found, or, both the value being found.)

As a simpler approach, applicable to your specific application, VB6 has the SPLIT (http://msdn.microsoft.com/en-us/library/aa263365(VS.60).aspx) function, which could be very useful to you. Since you have a set of fields on each line, with the fields separated by commas, and, IF either (or both) of the search strings is located inside the target string, the field immediately before the first field where the target string is found is what you're wanting. So, you could split the target string into an array of strings, using the comma as the delimiter. Then, iterate through the resulting array of string, looking for either of your target strings. If either is found, return the field immediately prior to the current field & exit. If neither are found after scanning all the fields, return an empty string.

Private Function FindPowerCut (strSearch as String) as String
Dim sFields() as string
Dim N as Long

' split the target string into an array, using comma as delimiter
sFields = Split (strSearch, ",")

' iterate through the fields, searching for the first instance of the search
For N = lbound(sFields) to ubound(sfields)
If sFields(N) = "Voltage Cut" or sFields(N) = "AC Power Down" then
FindPowerCut = dFields(N-1)
Exit Function
End If
Next N

FindPowerCut = ""
End Function

In use, (I assume you're reading a log file of some sort, a line at a time into a variable named sLine, with file handle #1)

Dim sTmp as String

' Open the file using file handle #1
' ...

Do while not EOF(1)
sLine = Line Input (#1)
sTmp = FindPowerCut (sLine)

if Len(sTmp)>0 then
Debug.Print sTmp
Endif
Loop

Actually, I am aware of the InStr() and StrComp() functions. If I want to check if some text is inside a string I use Instr() > 0, will give me it matches. But I didn't know the return value will also give position of the substring in the string.

I am also aware with the split() and mid(), left(), right() functions. Just had some problem with coming up with the logic for this task.

Thank you for the help. This code works, exactly as I want it to. The logic is easy.

1. Split the csv string on ",".
2. Go through all the split elements (using for loop with lbound and ubound) and find the first element with "Voltage Cut" or "Ac Power Down".
3. Save the element number
3. Exit the for loop
4. Subtract 1 from the saved element number to retrieve the time.

Thank you. This was very helpful. :)