I have a lot of address lists to work with at work. Each one is in a different format and each time they need to be used they need to be converted to a specific template format.
I can't just keep them once they are converted because they change from month to month (they are subscriber lists for magazines and journals) so each time I need to use one of them I need to convert it fresh from a fresh data download.
I can't get them sent to me in the correct format because there are well over 300 different customers, all of whom have established databases that run into their other systems and getting them all to change would be a nightmare (plus there are new customers coming in all the time)
So... I need to find some way to at least mostly automate the variable data coming in to go into a set structure that is the same every time. Within this structure the town, state/county and country all need to be in specific cells.
I have found Yahoo Placefinder which seems to me a good way to automate pulling out these fields as part of the xml string it outputs looks like the following:
<city>Stevenage</city> <county>Hertfordshire</county> <state>England</state> <country>United Kingdom</country> <countrycode>GB</countrycode> <statecode>ENG</statecode> <countycode>HRT</countycode> <uzip>AL4 0</uzip>
So what I am currently thinking is to export the address lists from Excel in an XML format, then use some code (and here is my problem) to do a Placefinder query off of set elements within each record in turn, and append the results from the Placefinder query back into the record. Then re-import back into Excel. The Excel bits are simple enough, but I don't have a clue which code to try and use for the Placefinder bit. I've tried googling around everywhere but I have about 10 different answers from 10 different websites.
Can someone please help a total programming novice to find the simplest and easiest way to do something like this? I'm not looking for someone to just hand me the code all written (tho that would be lovely) - I don't mind learning how to write the code myself, I just want to know that I'm learning the right thing before I spend ages reading tutorials and guides and slowly writing up a test code to do the above.
Thanks in advance for any helpful replies