Unixmonkey.net

I’ve got some data in an excel file that I need to put in the database and its far too much to do by hand, what will I do?

Lets throw some ruby at the problem!

First, excel it too darn complicated and proprietary a format to even mess with unless you are creating something really worth it, so lets open that .xls with Excel or OpenOffice and do a File -> Save As -> .csv (comma separated values) to get a file that is easier to work with.

Now, we could write our own CSV parser since its such a simple format, but why futz with it when someone else has already put out a good library for that will likely be more error tolerant? Lets use FasterCSV, as its pretty well-known.

Install by issuing:

sudo gem install fastercsv

Now you can just fire up script/console of your Rails app and type in the below, or just put this in a database migration to slurp up all that good spreadsheet data.

The below assumes you have a ‘users’ table with fields name, address, and email that are also rows in your excel file. Adjust as necessary.

require 'fastercsv'
FasterCSV.foreach("#{RAILS_ROOT}/myfile.csv") do |row|
  record = User.new(
    :name    => row[0], # first column of csv file
    :address => row[1], # second column
    :email   => row[2]  # third
  )
  record.save
end

That’s pretty awesome; now how can I export that stuff in the database back out to Excel again?

Lets use Ruport, the Ruby report gem!

sudo gem install acts_as_reportable

Toss the require statement somewhere obvious (like environment.rb or above the model you want to export), and put ‘acts_as_reportable’ in your model declaration.

require 'ruport'
class User < ActiveRecord::Base
  acts_as_reportable
end

Now I can do this kind of stuff to export to a csv file (again with script/console, but a migration should work equally well):

content = User.report_table.as(:csv) # convert your model table to CSV

or

content = User.report_table_by_sql("SELECT name, address, email FROM users").as(:csv)

Then write that to a file like so:

file = File.open("#{RAILS_ROOT}/report.csv", "w") # open file
file.print(content) # print that csv content to the open file
file.close          # close the file

Open that CSV file with Excel and amaze the sales team, your boss, or whoever.

This is a narrow view of what we can do with FasterCSV and Ruport, but I’m sure you can see how you could build out a format.csv in a respond_to block in a Rails controller, or have a setter in your model that sucks in an uploaded CSV to create some records.

These are some pretty great libraries, and I’m very glad they were able to help me load, combine, query and output some data I’d been working with in a pinch.

I hope this post serves to help someone else in a similar situation.