OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

When a Hack Goes Wrong… Google Spreadsheets and Yahoo Pipes

One of my most successful posts in terms of lifetime traffic numbers has been a recipe for scraping data from a Wikipedia page, pulling it into a Google spreadsheet, publishing it as CSV, pulling it into a Yahoo Pipe, geo-coding it, publishing it as a KML file, displaying the KML in Google maps and embedding the map in another page (which could in principle be the original WIkipedia page): Data Scraping Wikipedia with Google Spreadsheets.

A variant of this recipe in other words:

wikipediamap

Running the hack now on a new source web page, we get the following data pulled into the spreadsheet:

pipe broken

And the following appearing in the pipe (I am trying to replace the first line with my own headers):

imported data

The CSV file appears to be misbehaving… Downloading the CSV data and looking at it in TextWrangler, a text editor, we start to see what’s wrong:

text editor

The text editor creates line numbers for things it sees as separate, well formed rows in the CSV data. We see that the header, which should be a single row, is actually spread over four rows. In addition, the London data is split over two rows. The line for Greater Manchester behaves correctly…: if you look at the line numbers, you can see line 7 overflows in the editor (the … in the line number count shows the CSV line (a separate dataflow) has overflowed the width of the editor and been wrapped round in the editor view).

If I tell the editor to stop “soft wrapping” each line of data in the CSV file, the editor displays each line of the CSV file on a single line in the editor:

text editor nowrap

So… where does this get us in fixing the pipe? Not too far. We can skip the first 5 lines of the file that we import into the pipe, and that gets around all the messed up line breaks at the top of the file, but we lose the row containing the data for London. In the short term, this is probably the pragmatic thing to do.

Next up, we might look to the Wikipedia file and see how the elements that appear to be breaking the the CSV file might be fixed to unbreak them. Finally, we could go to the Google Spreadsheets forums and complain about the pile of crap broken CSV generation that the Googlers appear to have implemented…

PS MartinH suggests workaround in the comments, wrapping a QUERY round the import and renaming the columns…

Written by Tony Hirst

February 5, 2013 at 5:25 pm

5 Responses

Subscribe to comments with RSS.

  1. […] (NOTE – Google spreadsheets’ CSV generator can be a bit crap at times and may require some fudging (and possibly a loss of data) in the pipe – here’s an example: When a Hack Goes Wrong… Google Spreadsheets and Yahoo Pipes.) […]

  2. A way around Google’s crappy csv generation is wrapping the importHTML in a QUERY which lets you choose which columns you’d like and while your at it rename them:

    =QUERY(ImportHtml(“http://en.wikipedia.org/wiki/List_of_largest_United_Kingdom_settlements_by_population”,”table”,2),”SELECT Col1, Col2, Col3 LABEL Col1 ‘rank’, Col2 ‘city’, Col3 ‘population'”)

    mhawksey

    February 5, 2013 at 9:07 pm

  3. @mhawksey Ah, thanks Martin.. seems as if you’re now *completely* my Goog Spreadsheets go-to person ;-)

    Tony Hirst

    February 6, 2013 at 9:01 am

  4. Thanks Tony, Martin. I’ll pass this on to the students. This must have happened quite recently as it *did* work when I tried it recently. I got stuck on the geo-tagging in Yahoo Pipes, which was resolved by using the Loop module and the location builder (as suggested at the time by Tony).

    Joss

    February 6, 2013 at 3:16 pm

    • @Joss – I updated the original post with an example of how to use the loop/location builder combination

      Tony Hirst

      February 6, 2013 at 4:24 pm


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 844 other followers

%d bloggers like this: