Forum: Ruby on Rails File upload/database import

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
76ade6a3bc9aadb33df39c735caf46f7?d=identicon&s=25 Bill Clinton (bclinton)
on 2006-05-07 17:15
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.
6661ef9d747db3af8896cd94959d717d?d=identicon&s=25 Paul Barry (Guest)
on 2006-05-07 17:35
(Received via mailing list)
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...
00973881979aa0a660ffbbb2f7a907fb?d=identicon&s=25 Peter De Berdt (Guest)
on 2006-05-07 17:35
(Received via mailing list)
On 07 May 2006, at 17:15, Bill Clinton 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
76ade6a3bc9aadb33df39c735caf46f7?d=identicon&s=25 Bill Clinton (bclinton)
on 2006-05-07 18:43
> 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.
Ff43001ac5fe9805aa6ca2e89d3b7b5d?d=identicon&s=25 Jake Janovetz (janovetz)
on 2006-05-08 17:30
Bill Clinton 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
Ba0b6f35c33d3275de38f31bdd848396?d=identicon&s=25 Jeremy Burks (jrun)
on 2006-05-08 20:11
Bill Clinton 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.
This topic is locked and can not be replied to.