Bulk Insert of Addresses into Table

[dal1503]

I've got a spreadsheet full of addresses that I need to put into a table in an Access database, but the problem I've got is that in the spreadsheet the addresses are listed as, for example:

| House Number | Street Name | Postcode |
| _____________|____________|_________|
| 1-30 (all) | This Street | LA1 1AA |
| 2-20 (even) | That Street | LA2 2BB |
| 21-39 (odd) | That Street | LA2 2BB |


Does anyone know of any way that I could 'expand' the house numbers so that I have a separate row for each individual address and hence insert these addresses in bulk, or due to the format I've been given them do I simply have to bite the bullet and type them all in manually e.g.

1, This Street, LA1 1AA
2, This Street, LA1 1AA
3, This Street, LA1 1AA
...
2, That Street, LA2 2BB
4, That Street, LA2 2BB
...
21, That Street, LA2 2BB
23, That Street, LA2 2BB
etc., etc...

There will be thousands of addresses so ideally I'd want to avoid manually entering them!

Any suggestions would be much appreciated.

P.s. Sorry about my awful formatting of my example table, I wasn't sure if there was a way of doing a proper one!

 

[JHB]

First you should import the spreadsheet into MS-Access.
Then build some VBA code, containing a DAO-Recordset, a loop and the function Split.
For further help, post a MS-Access database with the imported spreadsheet, zip it when you haven't 10 post.

 

[Ranman256]

your example already shows each street on its own row.
you would just run excel import command.

 

[dal1503]

First you should import the spreadsheet into MS-Access.
Then build some VBA code, containing a DAO-Recordset, a loop and the function Split.
For further help, post a MS-Access database with the imported spreadsheet, zip it when you haven't 10 post.

I've imported a portion of the spreadsheet into an access database which is attached.

I'm sorry I have no idea about writing VBA code with a DAO-dynaset, loop and Split function! If you can help me at all now that you can see my database I'd be really grateful.

Thanks.

 

[dal1503]

your example already shows each street on its own row.
you would just run excel import command.

My example shows each street on its own row, but I need to show each house on its own row. If you look at the sample database I attached in my reply to JHB above it may make it clearer what I'm trying to achieve.

Thanks.

 

[JHB]

Sorry, but the data for the house numbers in the table, isn't like you showed it in you post #1, so you need to clarify the below.
all (73-79 odd, 80-96, 98, 101-111 odd)
all (100-112 even, 113-135, 137-179 odd)
all (1-40) all (1-28, 30 - 38 even)
all (40-48, 50-64 even, 65-90)
all (50-81, 100) all (1-30)
all (1-10, 31-37)
all (1-14)
all (74-80 even, 81-110,

 

[dal1503]

Sorry, but the data for the house numbers in the table, isn't like you showed it in you post #1, so you need to clarify the below.
all (73-79 odd, 80-96, 98, 101-111 odd)
all (100-112 even, 113-135, 137-179 odd)
all (1-40) all (1-28, 30 - 38 even)
all (40-48, 50-64 even, 65-90)
all (50-81, 100) all (1-30)
all (1-10, 31-37)
all (1-14)
all (74-80 even, 81-110,

Sorry, I tried to simplify my original example to try and explain what I wanted to achieve. For clarification:

• "all (73-79 odd, 80-96, 98, 101-111 odd)"

• The 'all' just means that all houses on this street are to be included, so it can basically be ignored.

• The numbers contained within the parentheses give the details of which houses exist on that particular street.

• '73-79 odd' means that only the odd numbers between 73 and 79 (including 73 and 79 themselves) need to be included.

• '80-96' means that EVERY number (both even and odd) between 80 and 96 need to be included.

• '101-111 odd' is the same as the explanation I gave for '73-79 odd'.

• So, using this example - "all (73-79 odd, 80-96, 98, 101-111 odd)" - the house numbers that I would need to have rows for in my database would be: [73, 75, 77, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 98, 101, 103, 105, 107, 109, 111].

I hope this example gives a clearer explanation?

 

[JHB]

Open the form in the attached database and click the button, result is in table NumberAndAdresses.

 

[dal1503]

Open the form in the attached database and click the button, result is in table NumberAndAdresses.

JHB, you are an absolute superstar! I can't thank you enough. I looked at the code and tried to understand what was going on so I could learn from it but I got completely lost, so all I can do is offer my many thanks!

Two follow up questions:

1. If I want to add a new load of addresses, I add them to the Address table, click the 'Create Numbers' button and the new addresses get added to the NumberAndAdresses table. Is that right?
2. If I try and add an address that is already in the NumberAndAdresses table, does it get duplicated or does it not create the address?

Thank you so much again!

 

[JHB]

First, you're welcome.
The table empties each time you press the button.
Code line:

  dbs.Execute ("Delete * From NumberAndAdresses")

Press the "thumps up" button in the right side of a thread, if you like my solution.

 

[dal1503]

First, you're welcome.
The table empties each time you press the button.
Code line:

  dbs.Execute ("Delete * From NumberAndAdresses")

Press the "thumps up" button in the right side of a thread, if you like my solution.

Ahh yes, that's brilliant, thank you!

I know this may be asking a bit too much, but is there any way you could send me a version of the code with some brief comments attached to explain what each line/section of code does? I'd love to understand it further so that I can create similar solutions myself in future. No worries if not though, you've done way more than I expected already

 

[dal1503]

Ahh yes, that's brilliant, thank you!

I know this may be asking a bit too much, but is there any way you could send me a version of the code with some brief comments attached to explain what each line/section of code does? I'd love to understand it further so that I can create similar solutions myself in future. No worries if not though, you've done way more than I expected already

Sorry for another question... Is there any way that your database can accept a string instead of a number for Address1? Some of the houses have a house name instead of a house number, for example:

all (1-29, 31-53 odd, The Cottage, The Farmhouse)

If this is too difficult or not possible then I can simply enter those values manually, but I just thought I'd ask

Thanks

 

[JHB]

..
all (1-29, 31-53 odd, The Cottage, The Farmhouse)

If this is too difficult or not possible then I can simply enter those values manually, but I just thought I'd ask

Thanks

I wouldn't say it is not possible, but I think it is easier to do it manually!
You ask for some code explanation, which code line do you not understand, post them and I'll try to explain it. Because it is only basic code nothing tricky in it.

 

[dal1503]

I wouldn't say it is not possible, but I think it is easier to do it manually!
You ask for some code explanation, which code line do you not understand, post them and I'll try to explain it. Because it is only basic code nothing tricky in it.

Then I will do it manually

I'm not very good at coding at the moment. With a lot of googling I've attempted to write a comment on each line of code, either saying what I think it is doing, or writing any questions I have about that line. If it's not too much trouble could you have a look and add to any comments with anything I've got wrong or any additional info please?

Thanks!

 

[JHB]

Most of it is correct, I've made some extra comments in it, it starts with ' *****
The I've made a picture of the arrays splt and splt1. I think it would be much clearer as words.

 

[BigHappyDaddy]

So I found this to be an interesting problem and come up with an independent (but very similar) solution. Posted for your review.

Public Sub GetHouseNumbers()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strHouses As String
   Dim arrLine As Variant
   Dim arrHouses As Variant
   Dim lngLowHouse As Long
   Dim lngHighHouse As Long
   Dim lngIndex As Long
   Dim lngHouseIndex As Long
   Dim lngPosition As Long
   Dim lngStep As Long
   Dim strSQL As String
   
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Address", dbOpenDynaset)
   
   Do Until rs.EOF
      strHouses = rs!Address1
      strHouses = Replace(strHouses, "all (", "")
      strHouses = Replace(strHouses, ")", "")
      arrLine = Split(strHouses, ", ")
      For lngIndex = 0 To UBound(arrLine)
         arrHouses = Split(arrLine(lngIndex), " ")
         lngPosition = InStr(arrHouses(0), "-")
         If lngPosition > 0 Then
            lngLowHouse = Left(arrHouses(0), lngPosition - 1)
            lngHighHouse = Mid(arrHouses(0), lngPosition + 1, Len(arrHouses(0)))
         Else
            lngLowHouse = arrHouses(0)
            lngHighHouse = lngLowHouse
         End If
         If UBound(arrHouses) > 0 Then
            lngStep = 2
         Else
            lngStep = 1
         End If
         
         For lngHouseIndex = lngLowHouse To lngHighHouse Step lngStep
            strSQL = "INSERT INTO NumberAndAdresses ( HouseNo, StreetName, Postcode ) " & _
                     "VALUES (" & lngHouseIndex & ", " & _
                           "'" & rs!Address2 & "', " & _
                           "'" & rs!PostCode & "');"
            db.Execute strSQL
         Next
      Next
      rs.MoveNext
   Loop
   
End Sub