Import Excel data with FasterCSV

So I have a whole list of data which is already in excel and need to put into the Rails application. I could either manually type them into the database, or there must be some plugin out there dealing with this.

Just a quick google bring me to Unixmonkey blog talking about fastercsv.

All you need to do is just

gem install fastercsv

Then copy the excel file(oh, convert it to .csv file first) to the root of your application.

Now as the target table is “Bcode” with fields, term_no, standard_termÂ at the root, ruby script/console

require 'fastercsv'
FasterCSV.foreach("#{RAILS_ROOT}/file.csv") do |row|
  record = Bcode.new(
  :term_no => row[0],
  :standard_term => row[1]
  )
  record.save
end

That is just a few simple line of codes, but believe me, it got me almost 5 times of trying before I got everything right in the console. It is just so frustrated. And only after all that suffering that I find the line saying we can actually put this into data migration script. Arghhh…..

It did take a while (almost 10 minutes, I think) to load in all the 17K rows of cvs data though.

require 'win32ole' xl = WIN32OLE.connect('Excel.Application') ws = xl.ActiveSheet data = ws.Range("A2:B#{ws.UsedRange.Rows.Count}").Value data.each do |row| record = Bcode.new( :term_no => row[0], :standard_term => row[1] ) record.save end

4 Comments

David Mullet

FasterCSV is great. You could also pull the data straight from the Excel worksheet with code similar to this:

admin

Hey David, thanks for the hint, might try this when I have the need to pull data out of excel again.

Chris W

If you need to parse excel directly, you can also try the parseexcel gem. It is cross platform and works well.

Hi Chris, thanks for pointing out that, will try out parseexcel gem.