Link to home
Start Free TrialLog in
Avatar of rjef
rjefFlag for United States of America

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
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Is that data already in a multi-dimensional array?
Avatar of rjef

ASKER

no- it is just how you see it.
are the addresses stored in a file (e.g. in a text file)?
Avatar of rjef

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

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

Open in new window

Avatar of HooKooDooKu
HooKooDooKu

'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:
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

Open in new window


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)

Open in new window

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
Option Explicit

Public City As String
Public State As String
Public Zip As String
Public Country As String

Open in new window


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

Open in new window

Avatar of rjef

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).

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))

Open in new window


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

Open in new window

Avatar of rjef

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
Avatar of vb_elmar
vb_elmar
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rjef

ASKER

good job