rjef
asked on
cleanest vb6 code for city state zip and country
i need the cleanest vb6 code to put the below data into variables.
city st zip country
Dallas, TX 75067 United States
Dallas, TX 75067 United States
Dallas, TX 75067-1234 United States
Dallas, TX 75067-1234 United States
city st zip country
Dallas, TX 75067 United States
Dallas, TX 75067 United States
Dallas, TX 75067-1234 United States
Dallas, TX 75067-1234 United States
Is that data already in a multi-dimensional array?
ASKER
no- it is just how you see it.
are the addresses stored in a file (e.g. in a text file)?
ASKER
yes
if the addresses in the file looks like the sample.txt then I have a vb6 code to put all the data into variables.
sample.txt
==========
Dallas, TX, 75067, United States
Dallas, TX, 75067, United States
Dallas, TX, 75067-1234, United States
Dallas, TX, 75067-1234, United States
sample.txt
==========
Dallas, TX, 75067, United States
Dallas, TX, 75067, United States
Dallas, TX, 75067-1234, United States
Dallas, TX, 75067-1234, United States
Private Type pt
city As String
state As String
zip As String
country As String
End Type
Private Sub Command1_Click()
Dim data1() As pt, dCnt As Long, tmp As String, sp, hf
hf = FreeFile
Open "C:\sample.txt" For Input As #hf
Do Until EOF(hf)
ReDim Preserve data1(dCnt)
Line Input #hf, tmp
sp = Split(tmp, ",")
data1(dCnt).city = sp(0) 'city
data1(dCnt).state = sp(1) 'st
data1(dCnt).zip = sp(2) 'zip
data1(dCnt).country = sp(3) 'country
dCnt = dCnt + 1
Loop
Close #hf
MsgBox dCnt & " data set found"
MsgBox data1(0).city
MsgBox data1(0).state
MsgBox data1(0).zip
MsgBox data1(0).country
MsgBox data1(1).city
MsgBox data1(1).state
MsgBox data1(1).zip
MsgBox data1(1).country
MsgBox data1(2).city
MsgBox data1(2).state
MsgBox data1(2).zip
MsgBox data1(2).country
MsgBox data1(3).city
MsgBox data1(3).state
MsgBox data1(3).zip
MsgBox data1(3).country
End Sub
'ReDim Preserve' every time thru a loop is NOT what I would call clean VB code. The entire array gets recopied in memory every time thru the loop.
The better solution would be to ReDim the array only once every 10th or 100th time through the loop. And to get started, you could guess the estimated size of the array from the file size.
Here's some example Pseudocode:
As for loading the data... I see that the only comma is between the city and state. So a Split function isn't going to work.
So you're likely going to simply do a bunch of Instr commands.
Load The Data Pseudocode:
The better solution would be to ReDim the array only once every 10th or 100th time through the loop. And to get started, you could guess the estimated size of the array from the file size.
Here's some example Pseudocode:
nSize = GetFileSize() / 40 + 10 'Update 40 to any number that estimates the average char per line of file
ReDim Data(1 to nSize)
nCount = 0
Do Until EOF
If nCount == nSize Then
nSize = nSize + 20
ReDim Preserve Data(1 to nSize)
End If
Load The Data
Loop
Optionally do a final ReDim Preserve using the now know size of the array
As for loading the data... I see that the only comma is between the city and state. So a Split function isn't going to work.
So you're likely going to simply do a bunch of Instr commands.
Load The Data Pseudocode:
Line Input str
nCount = nCount + 1
n1 = InStr( str, ",")
Data(nCount).City = Left$(str, n)
n2 = n1 + 2 'This should be the start of the State Field
Data(nCount).State = Mid$(str, n2, 2)
n3 = InStr( n2 + 1, str, " ")
Data(nCount).Zip = Mid$(str, n2+3, n3 - n2 - 2)
Data(nCount).Country = Mid$(str, n3 + 1)
Of course before using the n1, n2, and n3 values, you might have to think some about data checking. This pseuodcode assumes that the formatting is VERY consistent. Some failures that might occur would include a city with a comma in its name (is that possible?), extra spaces getting inserted between fields, a state getting spelled out rather than just the 2 char abbreviation, zip code with spaces rather than dashes, especially if it is a zip+4+2 (12345-6789 01).
Here's what I'd do. No redimming required.
Add a class (that I've called CData) that looks like this
Then it''s easy.
Add a class (that I've called CData) that looks like this
Option Explicit
Public City As String
Public State As String
Public Zip As String
Public Country As String
Then it''s easy.
Dim FF As Integer
Dim lngIndex As Long
Dim strParts() As String
Dim strLine As String
Dim C As CData
' This requires a Reference to Microsoft Scripting Runtime
Dim dicAddresses As New Dictionary
FF = FreeFile
Open "C:\temp\CSZc.txt" For Input As FF
Do While Not EOF(FF)
Line Input #FF, strLine
strParts = Split(strLine, ",")
Set C = New CData
C.City = strParts(0)
C.State = Trim(strParts(1))
C.Zip = Trim(strParts(2))
C.Country = LTrim(strParts(3))
dicAddresses.Add C, C
Loop
' Display the third item
Debug.Print dicAddresses.Items(2).City
Debug.Print dicAddresses.Items(2).State
Debug.Print dicAddresses.Items(2).Zip
Debug.Print dicAddresses.Items(2).Country
Close
ASKER
there is only 1 comma in each line and also there are sometimes 2 spaces and sometimes only 1 space between values. Also the city can be a two word city with a space between. There will be no city with comma. states will always be 2 letters. there will only be 2 type of zip codes xxxxx and xxxxx-xxxx and if it is extended zip it will be a dash inbetween values.
Something like the following would be able to pull out the pieces and utilize Trim to take care of extra spaced (and assumes a line of data has been read into str).
If you follow MartinLiss' idea to forgo Arrays entirely, you could also use a Collection in place of a Dictionary. A Collection is an inherent list object, and doesn't require a key,value pair.
n = InStr$(str,",") 'Find the location of the 1st Comma
C.City = Left$(str, n-1) 'Will Crash if no comma was found
str = Trim$(Mid$(str,n+1) 'Remove City and leading spaces
C.State = Left$(str,2)
str = Trim$(Mid$(str,3) 'Remove State and leading spaces
n = InStr$(str, " ") 'Find end of Zip Code
C.Zip = Left$(str,n-1)
C.Country = Trim$(Mid$(str,n+1))
If you follow MartinLiss' idea to forgo Arrays entirely, you could also use a Collection in place of a Dictionary. A Collection is an inherent list object, and doesn't require a key,value pair.
Private Type pt
city As String
state As String
zip As String
country As String
End Type
Private Sub Command1_Click()
Dim data2() As pt, dCnt As Long, tmp As String, sp, hf, cnt, o
hf = FreeFile
Open "C:\mytext.txt" For Input As #hf
Do Until EOF(hf)
ReDim Preserve data2(dCnt)
Line Input #hf, tmp
tmp = Replace(tmp, ",", "") 'remove the comma after city
sp = Split(tmp, " ")
cnt = 0
For Each o In sp
If o <> "" Then
cnt = cnt + 1
If cnt = 1 Then data2(dCnt).city = o 'city
If cnt = 2 Then data2(dCnt).state = o 'st
If cnt = 3 Then data2(dCnt).zip = o 'zip
If cnt = 4 Then data2(dCnt).country = o 'country 1st part
If cnt > 4 Then
data2(dCnt).country = data2(dCnt).country & " " 'add space
data2(dCnt).country = data2(dCnt).country & o 'country 2nd part
End If
End If
Next
dCnt = dCnt + 1
Loop
Close #hf
MsgBox dCnt & " data set found"
MsgBox data2(0).city
MsgBox data2(0).state
MsgBox data2(0).zip
MsgBox data2(0).country
MsgBox data2(1).city
MsgBox data2(1).state
MsgBox data2(1).zip
MsgBox data2(1).country
MsgBox data2(2).city
MsgBox data2(2).state
MsgBox data2(2).zip
MsgBox data2(2).country
MsgBox data2(3).city
MsgBox data2(3).state
MsgBox data2(3).zip
MsgBox data2(3).country
End Sub
ASKER
vb_elmar2014 i am sorry i forgot to mention that it is possible to get a city with a space in it. example fort worth
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good job