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.

4 thoughts on “Import Excel data with FasterCSV

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    
    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
  2. Hey David, thanks for the hint, might try this when I have the need to pull data out of excel again.

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

Leave a Reply

Your email address will not be published. Required fields are marked *