# File upload/database import **URL:** \ **Category:** Rails\ **Created:** [May 7, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848 "2006-05-07T17:15:26Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 7, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/1 "2006-05-07T17:15:26Z")
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. ---
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 7, 2006, 5:35pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/2 "2006-05-07T17:35:29Z")
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://wiki.rubyonrails.com/rails/pages/HowtoUploadFiles) [http://manuals.rubyonrails.com/read/chapter/56](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/](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](http://www.archivesat.com/Ruby_on_Rails_developers_help/thread203817.htm) ---
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 7, 2006, 5:35pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/3 "2006-05-07T17:35:39Z")
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/](http://rails.techno-weenie.net/tip/2006/3/7/) import\_csv\_file\_into\_database\_using\_migrations Best regards Peter De Berdt ---
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 7, 2006, 6:43pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/4 "2006-05-07T18:43:09Z")
> 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. ---
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 8, 2006, 8:11pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/5 "2006-05-08T20:11:17Z")
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. ---
**Author:** ![printmkr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/9f8e36/32.png) [@printmkr](https://www.ruby-forum.com/u/printmkr)\ **Post date:** [May 8, 2006, 5:30pm UTC](https://www.ruby-forum.com/t/file-upload-database-import/59848/6 "2006-05-08T17:30:40Z")
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