Posted by david on 8. January 2009 21:26
my test application for the map site is a list of all Seattle parks with tags indicating their services.
the source page is here
a list of 301 parks, with 54 service descriptions – from “Tidelands” to “Off Leash Areas” ( my real interest )
There was no way, that I could see, to get a list of all parks ^ all services – the page returned all parks for a subset ( about 8 ) of services.
I copied it all into excel & spent an hour or so cleaning things up. Tried pivot table, but turns out that pivot tables have limits to the number of columns.
So, then imported the excel sheet into Access. Then ran the following query:
SELECT Sheet1.aPakrName, Sum(Sheet1.BaseballSoftball) AS SumOfBaseballSoftball,
Sum(Sheet1.BasketballFullCourt) AS SumOfBasketballFullCourt,
Sum(Sheet1.BasketballHalfCourt) AS SumOfBasketballHalfCourt,
Sum(Sheet1.[Bike Trail]) AS [SumOfBike Trail],
Sum(Sheet1.Cricket) AS SumOfCricket,
Sum(Sheet1.Lacrosse) AS SumOfLacrosse,
etc etc etc
FROM Sheet1
GROUP BY Sheet1.aPakrName;
I needed to since I copied the List of Parks into excel, I lost access to the URL … Excel only shows the friendly text, and no built in function to extract it. Ozgrid helped out, like many times before.
go into the code window, insert new module, paste :
Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "", "")
End Function
Then you can use GetAddress(cell) as a standard formula, drag to copy, etc.
So, eventually, loaded the excel sheet into access so I could do an easy grouping on the park name. Next step, get that data into the SQL POI table.
ironically, i have spent a while cleaning up this data & getting things all ready to go … but i seemed to have forgotten to get the specific address information for the parks. Doh.