If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
I want to get a list of geocodes into my address database using Yahoo's
geocoding service. I have the database. I have the Yahoo geo I have the username for the geocoding service I even have an Excel spreadsheet that will do exactly what I want to do with Excel VBA Macros but I can't figure out how to 'convert' the excel rows looping into my Access recordset. There's more that I can't figure out, but that gives you an outline of exactly where I am at the moment, which I guess is at the foot of the mountain... I have tried to get my head around the Excel VBA (which looks seductively simple...) but just get brain freeze when I try and 'frame' the whole thing in my mind any help or guidance would be gratefully appreciated. ( I can paste the Excel VBA, but didn't want to just 'dump' it here without putting out the question first.) thanks in advance Eric |
#2
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Hi Eric,
I'm not familiar with Yahoo's Geocodes but, if you can post a copy of an Excel file that has the data in it that you want to bring into Access, then perhaps I or someone else can assist you further. Do you have a personal web space available at your ISP, to upload a small .xls file? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: I want to get a list of geocodes into my address database using Yahoo's geocoding service. I have the database. I have the Yahoo geo I have the username for the geocoding service I even have an Excel spreadsheet that will do exactly what I want to do with Excel VBA Macros but I can't figure out how to 'convert' the excel rows looping into my Access recordset. There's more that I can't figure out, but that gives you an outline of exactly where I am at the moment, which I guess is at the foot of the mountain... I have tried to get my head around the Excel VBA (which looks seductively simple...) but just get brain freeze when I try and 'frame' the whole thing in my mind any help or guidance would be gratefully appreciated. ( I can paste the Excel VBA, but didn't want to just 'dump' it here without putting out the question first.) thanks in advance Eric |
#3
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Tom,
Try this: http://media.juiceanalytics.com/down...ool%20v3.1.xls regards Eric "Tom Wickerath" wrote: Hi Eric, I'm not familiar with Yahoo's Geocodes but, if you can post a copy of an Excel file that has the data in it that you want to bring into Access, then perhaps I or someone else can assist you further. Do you have a personal web space available at your ISP, to upload a small .xls file? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: I want to get a list of geocodes into my address database using Yahoo's geocoding service. I have the database. I have the Yahoo geo I have the username for the geocoding service I even have an Excel spreadsheet that will do exactly what I want to do with Excel VBA Macros but I can't figure out how to 'convert' the excel rows looping into my Access recordset. There's more that I can't figure out, but that gives you an outline of exactly where I am at the moment, which I guess is at the foot of the mountain... I have tried to get my head around the Excel VBA (which looks seductively simple...) but just get brain freeze when I try and 'frame' the whole thing in my mind any help or guidance would be gratefully appreciated. ( I can paste the Excel VBA, but didn't want to just 'dump' it here without putting out the question first.) thanks in advance Eric |
#4
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
What I want to do is:
1. pull a set of addresses from a query 2. geocode them with Yahoo's geocoder 3. write the Lat and Lon back to the respective records within the table. at the moment, the spreadsheet works on excel rows; but I want to 'translate' that action into Access records (fields). regards Eric "efandango" wrote: Tom, Try this: http://media.juiceanalytics.com/down...ool%20v3.1.xls regards Eric "Tom Wickerath" wrote: Hi Eric, I'm not familiar with Yahoo's Geocodes but, if you can post a copy of an Excel file that has the data in it that you want to bring into Access, then perhaps I or someone else can assist you further. Do you have a personal web space available at your ISP, to upload a small .xls file? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: I want to get a list of geocodes into my address database using Yahoo's geocoding service. I have the database. I have the Yahoo geo I have the username for the geocoding service I even have an Excel spreadsheet that will do exactly what I want to do with Excel VBA Macros but I can't figure out how to 'convert' the excel rows looping into my Access recordset. There's more that I can't figure out, but that gives you an outline of exactly where I am at the moment, which I guess is at the foot of the mountain... I have tried to get my head around the Excel VBA (which looks seductively simple...) but just get brain freeze when I try and 'frame' the whole thing in my mind any help or guidance would be gratefully appreciated. ( I can paste the Excel VBA, but didn't want to just 'dump' it here without putting out the question first.) thanks in advance Eric |
#5
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Hi Eric,
I downloaded the spreadsheet file, but there are no latitude and longitude values shown in cells A6:B8, for your three test records. I attempted to click on "Geocode all rows", but I get prompted for a Yahoo ID that I do not have. However, if you are seeing valid latitude and longitude values in your copy of the spreadsheet, then you should be able to easily get these values into your Access table. I highly recommend that when you export your query in Access, to create the spreadsheet that needs the latitude and longitude values looked up, that you include the primary key field for the table in question. If your table does not currently include a primary key field, then add a new field (perhaps name it pkCustomer or whatever looks reasonable). This can simply be an autonumber data type. After retrieving your latitude and longitude data, I would create a named range within the Excel spreadsheet, which includes the four fields: Latitude, Longitude, Precision (if you care about this field), and pkCustomer. Are you familiar with the process of selecting rectangular blocks of data within Excel, in order to create named ranges? Working with named ranges helps to ensure that you only get exactly what you want to get, and nothing more. Now, you simply need an UPDATE query, to update your existing records. Create a linked table to the named range in your Excel spreadsheet. Create a new query. Select your existing table that needs to be updated, along with the linked Excel file. Drag a join line between the two primary key fields, to prevent a cartesian product query result. Add appropriate criteria for the table in your database, so that only records that need to be updated are included in the resulting recordset. This might include, for example, using an Is Null criteria for the Latitude or Longitude fields (make it an OR so that records with either null Latitude or null Longitude [or both] are included in your recordset. When you get this SELECT query returning the desired records, convert it to an UPDATE query (Query | Update Query, if you are using Access 2003 or lesser version). You should see a new Update To row in the QBE (Query By Example) grid. Type in the fully qualified names of the fields from your linked table (a fully qualified name includes the name of the table plus the name of the field, so try to use a short name for your linked table). For example: Field Name: Latitude Update To: [MyExcel].[Latitude] Criteria: Is Null Field Name: Longitude Update To: [MyExcel].[Longitude] Criteria: Is Null Does this help? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: What I want to do is: 1. pull a set of addresses from a query 2. geocode them with Yahoo's geocoder 3. write the Lat and Lon back to the respective records within the table. at the moment, the spreadsheet works on excel rows; but I want to 'translate' that action into Access records (fields). regards Eric |
#6
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
PS. Regarding the Is Null criteria I suggested, you really don't need this
for the Update query, since an Inner Join line between your table and the linked range in your Excel file should be sufficient to select only the records intended for updating. However, you may wish to include the Is Null criteria such that any records with a null latitude OR null longitude are included in your initial SELECT query, which is used to populate the Excel spreadsheet. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html |
#7
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Tom,
I think I have not explained myself clearly enough, and fear that you have been unintentionaly misled. I don't want to import the data from Excel. Instead I want to replicate the actual VBA procedure 'engine' that exists within the Excel spreadsheet into an MS Access VBA 'engine', mainly centering around the excel 'geocoderAddressLookup' procedure. I don't need any of the other stuff in there such as the address cleaning procedure because my Access data is clean and ready to go. I already have my tables/queries set up in Access, I just need someone to tell me how to re-engineer the bit where excel looks at rows of addresses and parses them to yahoo, end then brings them back into excel as geocodes. My Access version would do the same, only it would instead of addressing rows like excel, it would instead address records. Does that make sense? regards Eric "Tom Wickerath" wrote: Hi Eric, I downloaded the spreadsheet file, but there are no latitude and longitude values shown in cells A6:B8, for your three test records. I attempted to click on "Geocode all rows", but I get prompted for a Yahoo ID that I do not have. However, if you are seeing valid latitude and longitude values in your copy of the spreadsheet, then you should be able to easily get these values into your Access table. I highly recommend that when you export your query in Access, to create the spreadsheet that needs the latitude and longitude values looked up, that you include the primary key field for the table in question. If your table does not currently include a primary key field, then add a new field (perhaps name it pkCustomer or whatever looks reasonable). This can simply be an autonumber data type. After retrieving your latitude and longitude data, I would create a named range within the Excel spreadsheet, which includes the four fields: Latitude, Longitude, Precision (if you care about this field), and pkCustomer. Are you familiar with the process of selecting rectangular blocks of data within Excel, in order to create named ranges? Working with named ranges helps to ensure that you only get exactly what you want to get, and nothing more. Now, you simply need an UPDATE query, to update your existing records. Create a linked table to the named range in your Excel spreadsheet. Create a new query. Select your existing table that needs to be updated, along with the linked Excel file. Drag a join line between the two primary key fields, to prevent a cartesian product query result. Add appropriate criteria for the table in your database, so that only records that need to be updated are included in the resulting recordset. This might include, for example, using an Is Null criteria for the Latitude or Longitude fields (make it an OR so that records with either null Latitude or null Longitude [or both] are included in your recordset. When you get this SELECT query returning the desired records, convert it to an UPDATE query (Query | Update Query, if you are using Access 2003 or lesser version). You should see a new Update To row in the QBE (Query By Example) grid. Type in the fully qualified names of the fields from your linked table (a fully qualified name includes the name of the table plus the name of the field, so try to use a short name for your linked table). For example: Field Name: Latitude Update To: [MyExcel].[Latitude] Criteria: Is Null Field Name: Longitude Update To: [MyExcel].[Longitude] Criteria: Is Null Does this help? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: What I want to do is: 1. pull a set of addresses from a query 2. geocode them with Yahoo's geocoder 3. write the Lat and Lon back to the respective records within the table. at the moment, the spreadsheet works on excel rows; but I want to 'translate' that action into Access records (fields). regards Eric |
#8
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Tom,
These are the main procedures within the Excel macro. GEOCODING is done using the following layers geocodeSelectedRows (for each row call geocodeRow) geocodeRow(r) (check that row is geocodable, pass to geocode, parse results) geocode(street,city,state,zip) (clean all variables, pass url to geocoderAddressLookup, if no result then try different permuatations of address) geocoderAddressLookup (query geocoder.us, return result, marshal results) As I see it, I only really need to somehow convert one of them: geocoderAddressLookup (with the functionality of 'geocodeSelectedRows') "Tom Wickerath" wrote: PS. Regarding the Is Null criteria I suggested, you really don't need this for the Update query, since an Inner Join line between your table and the linked range in your Excel file should be sufficient to select only the records intended for updating. However, you may wish to include the Is Null criteria such that any records with a null latitude OR null longitude are included in your initial SELECT query, which is used to populate the Excel spreadsheet. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html |
#9
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
Hi Eric,
Yes, I did misunderstand. I thought you wanted to use the application, as is, to export your address data to the existing Excel spreadsheet, get the latitude and longitude data, and then bring this data back into your Access application. I don't know if it is even possible to use the same, or a modified version, of this code in your Access application. And, I don't have the time or energy to try to pursue that effort. Sorry. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: Tom, I think I have not explained myself clearly enough, and fear that you have been unintentionaly misled. I don't want to import the data from Excel. Instead I want to replicate the actual VBA procedure 'engine' that exists within the Excel spreadsheet into an MS Access VBA 'engine', mainly centering around the excel 'geocoderAddressLookup' procedure. I don't need any of the other stuff in there such as the address cleaning procedure because my Access data is clean and ready to go. I already have my tables/queries set up in Access, I just need someone to tell me how to re-engineer the bit where excel looks at rows of addresses and parses them to yahoo, end then brings them back into excel as geocodes. My Access version would do the same, only it would instead of addressing rows like excel, it would instead address records. Does that make sense? regards Eric |
#10
|
|||
|
|||
How can I get Yahoo Geocodes into Access?
ok,
in my naivety i assumed it would not be too difficult, thanks for taking the time to look at this for me. regards Eric "Tom Wickerath" wrote: Hi Eric, Yes, I did misunderstand. I thought you wanted to use the application, as is, to export your address data to the existing Excel spreadsheet, get the latitude and longitude data, and then bring this data back into your Access application. I don't know if it is even possible to use the same, or a modified version, of this code in your Access application. And, I don't have the time or energy to try to pursue that effort. Sorry. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "efandango" wrote: Tom, I think I have not explained myself clearly enough, and fear that you have been unintentionaly misled. I don't want to import the data from Excel. Instead I want to replicate the actual VBA procedure 'engine' that exists within the Excel spreadsheet into an MS Access VBA 'engine', mainly centering around the excel 'geocoderAddressLookup' procedure. I don't need any of the other stuff in there such as the address cleaning procedure because my Access data is clean and ready to go. I already have my tables/queries set up in Access, I just need someone to tell me how to re-engineer the bit where excel looks at rows of addresses and parses them to yahoo, end then brings them back into excel as geocodes. My Access version would do the same, only it would instead of addressing rows like excel, it would instead address records. Does that make sense? regards Eric |
|
Thread Tools | |
Display Modes | |
|
|