[Talk-GB] Q2 2020 Quarterly project GP Surgeries and health sites

[Brian Prangle]

Rather than mappers up and down the country with varying evels of Excel
skills spending many dozens of hours cleaning up this csv could somebody be
kind enough to publish somewhere a cleaned up copy? It would be a great
resource for the QP.

Regards

Brian

On Thu, 16 Apr 2020 at 16:00, Peter Neale via Talk-GB <
talk-gb at openstreetmap.org> wrote:

> Thanks for pointing out how to import and convert the file.  After a bit
> of trial and error, I discovered how to get Excel to use the "¬" as the
> delimiter and (as you said), the addresses are quite inconsistent, but the
> data all lines up again in the Post Code Column.  There are some further
> issues in the ParentName Column, with the County sometimes duplicated there
> and sometimes there instead of the County Column.
>
> Thank you for taking me a step forward in my "How to Use Excel" course!
>
> Regards,
> Peter
>
>
> On Thursday, 16 April 2020, 13:52:06 BST, Robert Whittaker (OSM lists) <
> robert.whittaker+osm at gmail.com> wrote:
>
>
> On Thu, 16 Apr 2020 at 12:27, Peter Neale <nealepb at yahoo.co.uk> wrote:
> > I tried following the link to your proposed new source of “official”
> data, but none of the 3 links to the data worked very well for me.
> >
> > Link 1:  (API format) led to http 404 error.
> > Link 2  (CSV(TSV) format – led to http 404 error
> > Link 3  (XSV format) downloaded a file with a “.csv” file extension that
> seemed to be tab-separated, rather than comma-separated.  I took that into
> a text editor and did a global Find and Replace of Tab with Comma.  The
> resultant .csv file loaded into Excel just fine, but it has over 11,000
> lines and many of them must now have additional commas, because a number of
> fields are right-shifted (Post Code in the Latitude Column, Latitude in the
> Longitude Column, etc.)  Also, over 700 have Blank in the Address1 Field,
> with the whole address in Address 2, Address 3, etc.  Then quite a few
> (from my sample in the first 30) have County values in the ParentName
> Field.  So I fear that, unless you can do a better conversion than I did
> (and you almost certainly could, I know!) you will have a lot of manual
> cleaning up to do, before you can use this data.
>
> Yes, the first two links at
> https://data.gov.uk/dataset/e373eb6a-fffd-48e5-b306-71eb17f97af2/pharmacies
> are broken for me as well. For the third link, it looks like they
> tried to do CSV, but didn't understand how to escape commas within the
> fields, and so opted to use a different character "¬" instead. If you
> import this into a spreadsheet, and tell it to use just "¬" as the
> column separator, I think it works out fine, with all the entries in
> the right place. (You can certainly do this with LibreOffice; I'm not
> sure about Excel.) The address lines seem to be used inconsistently,
> but everything is back aligned when you get to the postcode field.
>
> Best wishes,
>
>
> Robert.
>
> --
> Robert Whittaker
>
> _______________________________________________
> Talk-GB mailing list
> Talk-GB at openstreetmap.org
> https://lists.openstreetmap.org/listinfo/talk-gb
> _______________________________________________
> Talk-GB mailing list
> Talk-GB at openstreetmap.org
> https://lists.openstreetmap.org/listinfo/talk-gb
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/talk-gb/attachments/20200417/76684371/attachment.htm>