Back to contents PHP Python Ruby Choose a language:

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.

For more details, read PHP's str_getcsv documentation.

Reading 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 str_replace function)

$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');

Split it into a array of lines.

$lines = explode("\n", $data);

You can then loop through the lines, and use the PHP str_getcsv function to make an array.

foreach($lines as $row) { $row = str_getcsv($row); printf("£%s spent on %s\n", $row[7], $row[3]); }

Saving to the datastore

Conventionally the first line gives the names for the columns. Extract it separately first.

$header = str_getcsv(array_shift($lines));

To save the data, you need to convert everything to an associative array. We also convert the 'Amount' row to a number type, so that it can then be added and sorted.

foreach($lines as $row) { $row = str_getcsv($row); if ($row[0]) { $record = array_combine($header, $row); $record['Amount'] = (float)$record['Amount']; scraperwiki::save(array('Transaction Number', 'Expense Type', 'Expense Area'), $record); } }