I have been using Ruby on Rails for around 6 weeks now (so far I love
it), but I’ve gotten to a point in the application where I need to allow
the user to upload comma deliminated text files that will get loaded
into one of the database tables.
Before I get started doing this, I was wondering anyone knows of any
examples or tutorials that deal with this. I’d rather do it the right
way (if there is one) then try and reinvent the wheel with some hacked
solution. Also, let me know if this is something that might be better
done by sliding back to PHP for this part of my application.
Thank you.
First, of all, there’s no way slinding back to PHP is a good idea. The
flexibility of the Ruby language itself gives you plenty of options. So
first question, how do you get the file from the user? That’s easy
enough:
http://wiki.rubyonrails.com/rails/pages/HowtoUploadFiles
http://manuals.rubyonrails.com/read/chapter/56
Then, just use CSV::Reader to parse the data:
http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/
and lastly, load the data into the database. Not sure about the best
way to
do that. Is there an easy way to do that? The code that handles
fixtures
can do it, so I’m wondering if there’s a re-usable module for that
already.
You could obviously use ActiveRecord, but I’m not sure that’s the best
choice for a batch operation like this.
Also, how many records are you loading? If it is a lot and speed is a
concern, you may want to use a database vendor-specific way of loading
the
data, such as LOAD DATA INFILE if you are using MySQL, or SQL Loader for
Oracle.
Here’s another descirption of how to do it:
http://www.archivesat.com/Ruby_on_Rails_developers_help/thread203817.htm
On 07 May 2006, at 17:15, Bill C. wrote:
solution. Also, let me know if this is something that might be better
done by sliding back to PHP for this part of my application.
Why would you ever want to go back to PHP? 
You could just use the FasterCSV library and import it using that.
The following link has all the useful information.
http://rails.techno-weenie.net/tip/2006/3/7/
import_csv_file_into_database_using_migrations
Best regards
Peter De Berdt
Why would you ever want to go back to PHP? 
Ok. I’ll admit it was an idle threat. I figured the absolute horror of
the suggestion would inspire people to help me. I’m sorry I decided to
cheapen myself with such a pathetic ploy.
Paul and Peter - Thanks a ton for your responses. You definitely gave me
enough to get moving on this.
Bill C. wrote:
I have been using Ruby on Rails for around 6 weeks now (so far I love
it), but I’ve gotten to a point in the application where I need to allow
the user to upload comma deliminated text files that will get loaded
into one of the database tables.
Before I get started doing this, I was wondering anyone knows of any
examples or tutorials that deal with this. I’d rather do it the right
way (if there is one) then try and reinvent the wheel with some hacked
solution. Also, let me know if this is something that might be better
done by sliding back to PHP for this part of my application.
Thank you.
both mysql and postgresql offer ways to import csv data directly by
using the COPY command. if you host your app on a webhost you most
likely will not be able to do it in mysql since the call is a global
permission. if you use postgres you can use the COPY command through
psql which uses user permissions. A webhost is more likely to allow
that.
personally, i ended up using fastercsv to parse the file and generate
INSERT sql. Using ActiveRecord was to slow for the amount of data i am
importing.
Bill C. wrote:
I have been using Ruby on Rails for around 6 weeks now (so far I love
it), but I’ve gotten to a point in the application where I need to allow
the user to upload comma deliminated text files that will get loaded
into one of the database tables.
Before I get started doing this, I was wondering anyone knows of any
examples or tutorials that deal with this. I’d rather do it the right
way (if there is one) then try and reinvent the wheel with some hacked
solution. Also, let me know if this is something that might be better
done by sliding back to PHP for this part of my application.
Thank you.
I do this with a multi-step ‘wizard’ in my application. Imported
datasets are typically small, so I can store the data in the session
between steps. As the other posters have suggested, I use CSV::Reader.
It’s fast enough for this quantity of data.
First I pull the data into an array of arrays (rows and columns of the
data). I display this and let the user choose which columns contain the
data they want for particular columns in the database table. This
avoids the user having to follow a particular CSV output order.
Second, I create AR objects for each of the rows the user wants
selected, and display them on a second page. I highlight rows that fail
validation and tell them that these will not be imported.
Finally, all the data is imported.
Jake