Splitting a large field up into six new fields

D

David

Hi All,
Can anyone help. I am rather rusty on Access 2007 and I work for a small
charity that has a database of 150,000 addresses. Each address is all in one
field separated by commas but they are all different lengths within a single
field, for example:-

Rec1: 14,Hawthorn Avenue, Anytown, CHX X01.
Rec2: The Firs,Strentham Road, Anyplace, ST54 XXX.
Rec3: Dunroamin,45,Heysham Street,Any old Town,GH45 6XX.
Rec4: Flat 12,Sloan Villas,Oxton Road,Anycity,FT43 9XO.
Rec5: Apartment 2,Yew Tree Heights,45,Stuart Drive,Anyplace,PO56 7RX

I need to spilt then into six fields with the following field names of:-
PremisesNo, PremisesName, HouseNo, Street, Town, PostCode
I can't devise a query to do this using the commas as separators as some
records have three commas as separators and some have five commas. If I
export as a csv then re-import then the fields get all mixed up as it
re-imports reading from left to right. Any help much appreciated.

Many thanks, David (UK)
 
B

Bob Barrows

David said:
Hi All,
Can anyone help. I am rather rusty on Access 2007 and I work for a
small charity that has a database of 150,000 addresses. Each address
is all in one field separated by commas but they are all different
lengths within a single field, for example:-

Rec1: 14,Hawthorn Avenue, Anytown, CHX X01.
Rec2: The Firs,Strentham Road, Anyplace, ST54 XXX.
Rec3: Dunroamin,45,Heysham Street,Any old Town,GH45 6XX.
Rec4: Flat 12,Sloan Villas,Oxton Road,Anycity,FT43 9XO.
Rec5: Apartment 2,Yew Tree Heights,45,Stuart Drive,Anyplace,PO56 7RX

I need to spilt then into six fields with the following field names
of:- PremisesNo, PremisesName, HouseNo, Street, Town, PostCode
I can't devise a query to do this using the commas as separators as
some records have three commas as separators and some have five
commas. If I export as a csv then re-import then the fields get all
mixed up as it re-imports reading from left to right. Any help much
appreciated.
Many thanks, David (UK)
Click to expand...

You're going to need some human eyes looking at this. The best you can do is
process the records with the 5 commas, providing the placement of the data
within the strings is consistent along the lines of Rec5. Those are
relatively easy, using the Split function:

select Split([address],",")(0) As PremisesNo,
Split([address],",")(1) As PremisesName,
.... ,
Split([address],",")(5) As Postcode
From [raw_addresses]
WHERE ubound(Split([address],",")) = 5

Then get some help to look at the ones that are left over. It does not
appear that you will be able to automate those.
 
J

John Spencer

You could try using a custom VBA function

Public Sub fFixAddresses()
Dim vAddressParts As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb().OpenRecordset("SELECT * FROM AddressTable" & _
" WHERE CurrentAddressField is not Null")

Do While rst.EOF = False

vAddressParts = Split(rs!CurrentAddressField, ",")
Select Case UBound(vAddressParts)
Case 6
rst.Edit
rst!PremisesNo = vAddressParts(0)
rst!PremisesName = vAddressParts(1)
rst!HouseNo = vAddressParts(2)
rst!Street = vAddressParts(3)
rst!Town = vAddressParts(4)
rst!PostCode = vAddressParts(5)
rst.Update

Case 5 'This one is tricky since the missing
'element is undetermined, at a minimum you could populate street, town and
'postcode and then rely on a human to fix the remainder

'You could try solve this by examining elements 0 and 1
'and assigning values based on whether or not the elements
'were numeric in the position. Obviously, there will be
'errors introduced at this stage.

rst.Edit
If Not IsNumeric(vAddressParts(0) then
rst!PremisesNo = vAddressParts(0)
ElseIF Not IsNumeric(vAddressParts(1) Then
rst!Premises = vAddressPart(1)
End IF

If IsNumeric(vAddressParts(0) then
rst!HouseNo = vAddressParts(0)
ElseIF IsNumeric(vAddressParts(1) Then
rst!HouseNo = vAddressPart(1)
End IF

rst!Street = vAddressParts(2)
rst!Town = vAddressParts(3)
rst!PostCode = vAddressParts(4)
rst.Update

Case 4 'This one is even more complex since the missing
'elements are undetermined
rst.Edit
'rst!PremisesNo = vAddressParts(?)
'rst!PremisesName = vAddressParts(?)
'rst!HouseNo = vAddressParts(?)
rst!Street = vAddressParts(2)
rst!Town = vAddressParts(3)
rst!PostCode = vAddressParts(4)
rst.Update

End Select
rst.MoveNext
Wend

End Sub



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Top