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:
Running the hack now on a new source web page, we get the following data pulled into the spreadsheet:
And the following appearing in the pipe (I am trying to replace the first line with my own headers):
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:
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:
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…
OUseful.Info, the blog... 
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 Ah, thanks Martin.. seems as if you’re now *completely* my Goog Spreadsheets go-to person ;-)
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 – I updated the original post with an example of how to use the loop/location builder combination