Ruby on Rails - Import Data from a CSV file

Question

I would like to import data from a CSV file into an existing database table   I do not want to save the CSV file  just take the data from it and put it into the existing table   I am using Ruby 1 9 2 and Rails 3   This is my table   create table  mouldings    force   gt  true do  t    t string    suppliers code    t datetime  created at    t datetime  updated at    t string    name    t integer   supplier id    t decimal   length            precision   gt  3   scale   gt  2   t decimal   cost              precision   gt  4   scale   gt  2   t integer   width    t integer   depth  end   Can you give me some code to show me the best way to do this  thanks

User · Answer

I know it s old question but it still in first 10 links in google   It is not very efficient to save rows one-by-one because it cause database call in the loop and you better avoid that  especially when you need to insert huge portions of data   It s better  and significantly faster  to use batch insert   INSERT INTO  mouldings   suppliers code  name  cost  VALUES       s1    supplier1   1 111          s2    supplier2    2 222     You can build such a query manually and than do Model connection execute RAW SQL STRING   not recomended  or use gem activerecord-import  it was first released on 11 Aug 2010  in this case just put data in array rows and call Model import rows  refer to gem docs for details

User · Answer

The better way is to  include it in a rake task  Create import rake file inside  lib tasks  and put this code to that file   desc  Imports a CSV file into an ActiveRecord table  task  csv model import    filename   model    gt    environment  do  task args    lines   File new args  filename    r ISO-8859-1   readlines   header   lines shift strip   keys   header split        lines each do  line      values   line strip split          attributes   Hash keys zip values      Module const get args  model   create attributes    end end   After that run this command in your  terminal rake csv model import file csv Name of the Model

User · Answer

The smarter csv gem was specifically created for this use-case  to read data from CSV file and quickly create database entries     require  smarter csv    options        SmarterCSV process  input file csv   options  do  chunk      chunk each do  data hash        Moulding create   data hash       end   end   You can use the option chunk size to read N csv-rows at a time  and then use Resque in the inner loop to generate jobs which will create the new records  rather than creating them right away - this way you can spread the load of generating entries to multiple workers   See also        https   github com tilo smarter csv

User · Answer

If you want to Use SmartCSV  all data   SmarterCSV process               params  file  tempfile                                   col sep   gt    t                    row sep   gt    n                                 This represents tab delimited data in each row   t  with rows separated by new lines   n

User · Answer

Simpler version of yfeldblum s answer  that is simpler and works well also with large files  require  csv       CSV foreach filename  headers  true  do  row    Moulding create  row to hash  end  No need for with indifferent access or symbolize keys  and no need to read in the file to a string first  It doesnt t keep the whole file in memory at once  but reads in line by line and creates a Moulding per line

User · Answer

This can help  It has code examples too    http   csv-mapper rubyforge org   Or for a rake task for doing the same    http   erikonrails snowedin net  p 212

User · Answer

You might try Upsert   require  upsert    add this to your Gemfile require  csv       u   Upsert new Moulding connection  Moulding table name CSV foreach file  headers  true  do  row    selector     name  row  name       this treats  name  as the primary key and prevents the creation of duplicates by name   setter   row to hash   u row selector  setter end   If this is what you want  you might also consider getting rid of the auto-increment primary key from the table and setting the primary key to name  Alternatively  if there is some combination of attributes that form a primary key  use that as the selector  No index is necessary  it will just make it faster

User · Answer

Use this gem  https   rubygems org gems active record importer  class Moulding  lt  ActiveRecord  Base   acts as importable end   Then you may now use   Moulding import  file  File open PATH TO FILE     Just be sure to that your headers match the column names of your table

User · Answer

It s better to use CSV  Table and use String encode universal newline  true   It converting CRLF and CR to LF

User · Answer

It is better to wrap the database related process inside a transaction block  Code snippet blow is a full process of seeding a set of languages to Language model   require  csv   namespace  lan do   desc  Seed initial languages data with language  amp  code    task init data   environment do     puts   gt  gt  gt  Initializing Languages Data Table      ActiveRecord  Base transaction do       csv path   File expand path  languages csv   File dirname   FILE           csv str   File read csv path        csv   CSV new csv str  to a       csv each do  lan set          lan code   lan set 0          lan str   lan set 1          Language create  language  lan str  code  lan code          print           end     end     puts        puts   gt  gt  gt  Languages Database Table Initialization Completed    end end   Snippet below is a partial of languages csv file   aa Afar ab Abkhazian af Afrikaans ak Akan am Amharic ar Arabic as Assamese ay Aymara az Azerbaijani ba Bashkir

User · Answer

require  csv       csv text   File read        csv   CSV parse csv text   headers   gt  true  csv each do  row    Moulding create  row to hash  end

[ruby-on-rails] Ruby on Rails - Import Data from a CSV file

Examples related to ruby-on-rails

Examples related to csv

Examples related to import