\nsudo gem install fastercsv<\/pre>\nNow 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.<\/p>\n
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.<\/p>\n
\nrequire 'fastercsv'\nFasterCSV.foreach(\"#{RAILS_ROOT}\/myfile.csv\") do |row|\n record = User.new(\n :name => row[0], # first column of csv file\n :address => row[1], # second column\n :email => row[2] # third\n )\n record.save\nend<\/pre>\nThat’s pretty awesome; now how can I export that stuff in the database back out to Excel again?<\/p>\n
Lets use Ruport, the Ruby report gem!<\/p>\n
\nsudo gem install acts_as_reportable<\/pre>\nToss 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.<\/p>\n
\nrequire 'ruport'\nclass User < ActiveRecord::Base\n acts_as_reportable\nend<\/pre>\nNow I can do this kind of stuff to export to a csv file (again with script\/console, but a migration should work equally well):<\/p>\n
content = User.report_table.as(:csv) # convert your model table to CSV<\/p>\n
or<\/p>\n
\ncontent = User.report_table_by_sql(\"SELECT name, address, email FROM users\").as(:csv)<\/pre>\nThen write that to a file like so:<\/p>\n
\nfile = File.open(\"#{RAILS_ROOT}\/report.csv\", \"w\") # open file\nfile.print(content) # print that csv content to the open file\nfile.close # close the file<\/pre>\nOpen that CSV file with Excel and amaze the sales team, your boss, or whoever.<\/p>\n
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.<\/p>\n
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.<\/p>\n
I hope this post serves to help someone else in a similar situation.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,13,14,15,17],"tags":[31],"_links":{"self":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts\/23"}],"collection":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=23"}],"version-history":[{"count":0,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts\/23\/revisions"}],"wp:attachment":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}