Prev: Using Color as Criteria in Countif Function
Next: Create a list with multiple criteria
From: Phillip Holmes on 28 May 2010 00:07


MCSDPhil
Hi there,
When I do this kind of thing, where the data is a bit rubbishy, in VB I
just pick at the line until the data comes apart. I did this which is
about the best that I could do with it.

Public Class Form1
Private Const DATA_SURNAME = 1
Private Const DATA_INIT1 = 2
Private Const DATA_INIT2 = 3
Private Const DATA_ADDRESSNAMENUMBER = 4
Private Const DATA_ADDRESS = 5
Private Const DATA_TOWN = 6
Private Const DATA_STATE = 7
Private Const DATA_POSTCODE = 8
Private Const DATA_TELEPHONE1 = 9
Private Const DATA_TELEPHONE2 = 10

Private Sub btnParseData_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnParseData.Click
Dim strFilePath As String
Dim strLine As String
Dim intPos As Integer
Dim strData(10) As String
Dim strTemp1 As String
Dim strTemp2 As String
Dim strTemp As String

'OPEN FILE CODE HERE

'SUBSTITUTE READ LINE CODE HERE
strLine = "ALBINATI M J 115 STRADWICK RI SW CALGARY, AB T3H1G7
(403) 242-1028 (403) 242-1028"

Do While strLine <> ""
intPos = InStr(strLine, ",")

If intPos > 0 Then
strTemp1 = Trim(strLine.Substring(0, intPos - 1))
strTemp2 = Trim(strLine.Substring(intPos))
Debug.Print("strTemp1=" & strTemp1 & vbCrLf)
Debug.Print("strTemp2=" & strTemp2 & vbCrLf)
'Get surname
intPos = InStr(strTemp1, " ")
strData(DATA_SURNAME) = strTemp1.Substring(0, (intPos -
1))
Debug.Print("strData(DATA_SURNAME)=" &
strData(DATA_SURNAME))
strTemp1 = Trim(strTemp1.Substring(intPos))
'Get initials
'Find first initial
intPos = InStr(strTemp1, " ")
If intPos = 2 And Not IsNumeric(strTemp1.Substring(0,
1)) Then
'It is the first initial
strData(DATA_INIT1) = strTemp1.Substring(0, 1)
Debug.Print("strData(DATA_INIT1)=" &
strData(DATA_INIT1))
strTemp1 = Trim(strTemp1.Substring(intPos))
'See if there is a second initial
intPos = InStr(strTemp1, " ")
If intPos = 2 And Not
IsNumeric(strTemp1.Substring(0, 1)) Then
'This is the second initial
strData(DATA_INIT2) = strTemp1.Substring(0, 1)
Debug.Print("strData(DATA_INIT2)=" &
strData(DATA_INIT2))
strTemp1 = Trim(strTemp1.Substring(intPos))
End If
End If
intPos = InStr(strTemp1, " ")
strTemp = Trim(strTemp1.Substring(0, intPos))
If IsNumeric(strTemp) Then
'We probably have the house number
strData(DATA_ADDRESSNAMENUMBER) = strTemp
Debug.Print("strData(DATA_ADDRESSNAMENUMBER)=" &
strData(DATA_ADDRESSNAMENUMBER))
strTemp1 = Trim(strTemp1.Substring(intPos))
End If
'Put the rest of strTemp1 in Address
strData(DATA_ADDRESS) = Trim(strTemp1)
Debug.Print("strData(DATA_ADDRESS)=" &
strData(DATA_ADDRESS))
'Sort out the second part of the string
intPos = InStr(strTemp2, "(")
If intPos > 0 Then
'Get the State and PostCode
strTemp = Trim(strTemp2.Substring(0, intPos - 1))
strTemp2 = Trim(strTemp2.Substring(intPos - 1))
'Get the state
If InStr(strTemp, " ") = 3 Then
'We should have the first 2 digits as the state
strData(DATA_STATE) = Trim(strTemp.Substring(0,
2))
Debug.Print("strData(DATA_STATE)=" &
strData(DATA_STATE))
strTemp = Trim(strTemp.Substring(2))
End If
'Put it all in the postcode bit
strData(DATA_POSTCODE) = Trim(strTemp)
Debug.Print("strData(DATA_POSTCODE)=" &
strData(DATA_POSTCODE))
'Get the Phone Numbers
intPos = InStr(3, strTemp2, "(")
If intPos > 0 Then
strData(DATA_TELEPHONE1) =
Trim(strTemp2.Substring(0, intPos - 1))
Debug.Print("strData(DATA_TELEPHONE1)=" &
strData(DATA_TELEPHONE1))
strData(DATA_TELEPHONE2) =
Trim(strTemp2.Substring(intPos - 1))
Debug.Print("strData(DATA_TELEPHONE2)=" &
strData(DATA_TELEPHONE2))
Else
strData(DATA_TELEPHONE1) = Trim(strTemp2)
Debug.Print("strData(DATA_TELEPHONE1)=" &
strData(DATA_TELEPHONE1))
End If
End If
End If
'READ NEXT LINE CODE HERE i.e. strLine = ReadLine etc.
Loop

'CLOSE FILE CODE HERE
End Sub
End Class

Hope this helps.
Regards, Phil.

*** Sent via Developersdex http://www.developersdex.com ***
First  |  Prev  | 
Pages: 1 2 3
Prev: Using Color as Criteria in Countif Function
Next: Create a list with multiple criteria