Ruby on Rails: Import CSV data into database

This entry was posted in Programming and tagged , , , , . Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

Today I needed to migrate data from a Drupal site to Ruby on Rails. It is pretty easy to export data from my Drupal database (MySQL) into CSV format that I can use to import into my Rails application (Postgre SQL). So that’s where I started. I’m running Drupal 6.x, here is my database query to pull nodes out of the database. I ran this in phpMyAdmin then exported the results to a CSV file.

Back in Rails, I already have my application setup to support the press releases I’ll be importing and I won’t be covering that code here, it’s a standard scaffolding type MVC. You’ll need to update the code below to match your application naming.


First, we need a model to store the uploaded file and some related information. To upload and attach the CSV data, I’m going to use the Paperclip plugin, thanks to jimneath.orgfor the blog post on integrating Paperclip into your rails application. If you have more questions on Paperclip, please refer to their documentation or jimneath.org.

 

Continuing on with the code, you’ll need to generate a model for you data.

Edit the model import.rb

Edit the migration

Update your database:


Next, lets generate a controller to upload files and process the data

 

In imports_controller.rb I’m going to add methods to process the data I’ll be importing as well as a private methods to read the CSV file and store the records in the database. Note: I installed the gem, fastercsv | http://fastercsv.rubyforge.org/ to parse the CSV data.


Last, we need to create two views, the first allows you to upload the CSV file, the second shows the results of the upload and allows you to process the data. Note: At this point you should probably reload your web serverso the fastercsv gem and paperclip plugin are available.

 

new.html.erb

Upload a CSV file to import into the database

show.html.erb

Results of your CSV Upload

Update your routes.rb, add the following line:

That’s pretty much it. You can alter/expand on this to support multiple models in your application. Just make sure to pay close attention to detail when your mapping columns in the CSV file to values in your model. You may want to add a call somewhere to destroy old CSV import records since you likely don’t need to store the CSV data indefinately. Also, there could be a lot more error checking to this process to make sure the upload is valid data and each row is processed correctly. I hope to follow up this post soon with a Part 2 which adds more data processing details. Last, I know there is a lot of stuff placed in the controller, it’s something i’ve been in the habit of doing. Any input on moving processing to the model would be welcome.

Be Sociable, Share!