Importing Test Data with CSV Fixtures

Problem

You want to import data into your test database from another data source. Perhaps that source is an comma-separated values (CSV) file from a spreadsheet program or even a database. You could use YAML fixtures, but with large datasets, that would be tedious.

Solution

Use CSV fixtures to create test data from the output of another program or database.

Assume you have a database with a single countries table. The following migration sets up this table:

db/migrate/001_create_countries.rb:

class CreateCountries < ActiveRecord::Migration
 def self.up
 create_table :countries do |t| 
 t.column :country_id, :string
 t.column :name, :string
 end 
 end 
 def self.down
 drop_table :countries
 end 
end

If your test database is empty, initialize it with the schema from your development database:

$ rake db:test:clone_structure

Now create a country model using the Rails model generator:

$ ruby script/generate model country
 exists app/models/
 exists test/unit/
 exists test/fixtures/
 create app/models/country.rb
 create test/unit/country_test.rb
 create test/fixtures/countries.yml

Create a CSV file containing a list of countries. The first line contains the field names in the table that the rest of the data corresponds to. Here are the first 10 lines of countries.csv:

test/fixtures/countries.csv:

country_id,name ABW,Aruba AFG,Afghanistan AGO,Angola AIA,Anguilla ALB,Albania AND,Andorra ANT,Netherlands Antilles ARE,United Arab Emirates ARG,Argentina

As with YAML fixtures, CSV fixtures are loaded into the test environment using Test::Unit's fixtures method. The symbol form of the fixture's filename, excluding the extension, is passed to fixtures.

test/unit/country_test.rb:

require File.dirname(__FILE__) + '/../test_helper'
class CountryTest < Test::Unit::TestCase
 fixtures :countries
 def test_country_fixtures
 countries = Country.find(:all)
 assert_equal 230, countries.length
 end end

Discussion

Running the test shows that the fixtures were loaded successfully. The assertionthat there are 230 country recordsis also successful.

$ ruby test/unit/country_test.rb 
Loaded suite test/unit/country_test Started
.
Finished in 0.813545 seconds.
1 tests, 1 assertions, 0 failures, 0 errors

YAML fixtures are more readable and easier to update by hand than CSV files, but CSV fixtures are valuable since a number of programs support CSV exports. For example, using a CSV import would be helpful to reproduce a bug that's occurring only in your production environment. To reproduce the bug, export a snapshot of the tables on your production server to CSV files.

These files can be put in a temporary directory where they won't interfere with any existing fixtures. The syntax for specifying fixtures in a subdirectory of the standard fixtures directory is a bit different. Call the create_fixtures method of the Fixtures class, which takes the directory and table name as arguments. Here's the test class again, loading the countries fixtures file from the live_data subdirectory:

require File.dirname(__FILE__) + '/../test_helper'
class CountryTest < Test::Unit::TestCase
 Fixtures.create_fixtures(File.dirname(__FILE__) +
 '/../fixtures/live_data',
 :countries)
 def test_truth
 countries = Country.find(:all)
 assert_equal 230, countries.length
 end end

Importing Test Data with CSV Fixtures

Problem

Solution

Discussion

See Also