Hi, this is probably impossible, but worth asking as it could save me hours! I have a spread sheet In that spread sheet is a column with 537 post codes I need the local ward for each of these postcodes This is done by copy/pasting the post code into an online tool called NOMIS https://www.nomisweb.co.uk/reports/lmp/ward/contents.aspx Is there anyway to use the 'web Query function in excel to do this for me rather than mnaually copy pasting 537 postcodes one way and copying 537 ward names back across into the next column?
if you could you would need access to the api of the website generating the values. I come across this quite a lot at work, for the cost and effort to make it work, a quick copy and paste is as easy as it's going to get.
I'd agree here, in my (a bit limited, granted) experience, a copy and paste operation is your friend...
Interested with this problem also.. If you can share the step by step guide and better.. I'd definitely bookmarking the page for later use.
Nomis is very simple to use. Just use the link above and copy paste your postcode in for a break down of that area. Sadly it only handles one postcode at a time, which is the issue!
It could have been done, however, there is no table to import the data from on the page he linked to. Personally, i believe it would mean you would need access to the API of the website in order to access the raw data. Create a PHP or ASP script that will reflect the value you want in a single cell table. Use the web import excel function to import the information from the webpage just created. You can pass GET values through the query which would only need to include the postcode (without spaces ). Once that's been done you could import all of the information at once.
I believe the raw API data is open-source from the Oridence Survey people, however this is well beyond my understanding of excel! There's something or other about it here.... http://www.uk-postcodes.com/api
Unfortunately not, Parliament constituencies are much wider areas than wards. Plus it only finds the areas one at a time, which was the original issue also.
I can't seem to find an open API for the datasource at the beginning. If you could find one ( you may have to email ) i would be happy to help you with this.
Weirdly enough I've done the same lookup and the API you want is here: http://mapit.mysociety.org/ Specifically the postcode function that outputs JSON eg: http://mapit.mysociety.org/postcode/NR21NH or HTML http://mapit.mysociety.org/postcode/NR21NH.html (using Norwich City Hall postcode as an example) Can't help with the Excel side of things but the API should help
I did something similar. It's possible in excel to control Internet Explorer such as entering values into input boxes and getting return values. Does require understanding the html of the site and you are vulnerable if they change the page layout. It takes time to setup but saves a lot in the long run. Obviously the site api would be much quicker but this is a good bodge around Sent from my HTC One S using Tapatalk
I don't understand the whole API thing, anyway its possible to put in a list of postcodes and get a list of wards back would be amazing, regardless of excel!
I don't do Excel, but in Powershell this will do what you want. It's not pretty code, but it's the best I could do in a 15min coffee break Code: $inputfilename = Join-Path (Split-Path -parent $PSCommandPath) input.txt $outputfile = Join-Path (Split-Path -parent $PSCommandPath) output.csv $input = Import-Csv -Path $inputfilename -Header @("Postcode") ForEach ($line in $input) { $url = "http://mapit.mysociety.org/postcode/" + $line.Postcode $J = Invoke-WebRequest -Uri $url | ConvertFrom-Json $Postcode = $j.postcode $WardID = $j.shortcuts[0].ward[0].district $WardName = $j.areas[0].$($WardID)[0].name Add-Content -Encoding ASCII -Path $outputfile -Value "$Postcode,$WardName" } Save the code as "postcode.ps1", then create a txt file called "input.txt" in the same folder. Put one postcode per line in the txt file, using Norwich Wetherspoon Pubs & UEA as examples: Code: NR3 1LA NR6 5JB NR1 3QB NR4 7TJ Run the powershell script and "output.csv" will be generated with the postcodes and wards listed. Obviously your computer will need powershell and the permissions to run ps1. Often both of these are restricted in corporate environments. Final note.... You may want to run it in smaller batches (something like 100 postcodes every 3 mins) so you don't hit the limits of the API. And you're limited to 50,000 API calls per year without a license. Shouldn't be an issue but worth noting!
If you really want to jump in at the deep end all the actual raw data cross referencing postcodes and ward codes is here: https://geoportal.statistics.gov.uk/geoportal/catalog/content/filelist.page The 1GB+ "Postcode Directories : ONSPD_NOV_2013" csv file wouldn't fit in my Excel spreadsheet though
You'll prob need Office 2010 64bit to open it. There's a few Analysts at work who have it to go beyond the 2GB addressable memory limits that 32-bit applications have.
Good to see your work are following security best practice, powershell is crazy powerful in the wrong hands! Was a fun little exercise, learned how to read from http and deal with JSON in powershell. There's no error handling or data validation so it might go weird if it finds an invalid postcode.
