Comma-separated value files are a quite commonly used text form of spreadsheet. To process them often requires special cases, such as parsing dates or removing bad rows.
Download the CSV file first. (If there are quirks in the input file, you might at this point want to preprocess the data using, for example, the .gsub method)
data = ScraperWiki::scrape("http://s3-eu-west-1.amazonaws.com/ukhmgdata-cabinetoffice/Spend-data-2010-11-01/Spend-Transactions-with-descriptions-HMT-09-Sep-2010.csv")
Load it into the standard Ruby CSV reader. It needs to be an array of lines.
require 'fastercsv' csv = CSV.new(data)
You can then loop through the rows as if they were an array.
for row in csv: puts format("%s spent on %s", row, row) endThe above example, is available as a complete scraper.
Saving to the datastore
Conventionally the first line gives the names for the columns. You can get the standard reader to load in each row as a hash, where the keys are those names.
csv = CSV.new(data, :headers => true)
This makes it easy to save the data. By default everything comes out as strings. We convert the 'Amount' row to a number type, so that it can then be added and sorted.
for row in csv if row['Transaction Number'] row['Amount'] = row['Amount'].to_f ScraperWiki::save_sqlite(unique_keys=['Transaction Number', 'Expense Type', 'Expense Area'], row.to_hash) end end