How to populate database using data from csv

Some days ago, when I was working on one of the RoR application, I was very tensed regarding populating the database from huge data that I had in csv files. I will be a big headache if I would try to add all the data manually. So, I decided to write a script to import data to my database tables from csv files. I am going to share that script with you all, may it helps you.

Lets suppose you have 3 tables named as categories, levels and subjects in your database. Now just as a normal spreadsheet, create 3 csv files for them including column headers. I mean, the first row of csv file will be attribute names of corresponding table (as shown in below levels, subjects, categories csv files respectively), after that you are having your data. Save files with same name as corresponding tables name. For example: if file has data for categories table, file name will be categories.csv (plural, underscore, lowercase).

Now create a rake task in your applications lib/tasks directory with following code:

require 'csv'

desc "Import csv data into database record"

task :csv_table_import, [:dir] => :environment do |task, args|

  files = Dir.glob("#{args[:dir]}/*.csv")

  files.each_with_index do |file, index|

    model_name = file.split('/').last.split('.').first.camelize.singularize

    firstline = 0

    params_arr = []

    keys = {}



    begin

      CSV.foreach(file) do |row|      

        keys = row if firstline == 0 

        params = {}




        keys.each_with_index do |key, i|

          if firstline > 0

            params[key] = row[i]

          else

            break

          end

        end



        firstline = 1 if firstline < 0

        params_arr << params if firstline > 0

      end

    eval(model_name).create(params_arr)

    rescue Exception => e

      puts "#{e.message} : #{model_name}"

    end

  end

end

Just copy and paste above code to your rake task  file and run the task as follow:

rake csv_table_import['/path/to/csv_files_directory']

'/path/to/csv_files_directory' is the path to the directory where you have kept the csv files.

Let me know, if it was helpful for you or any of your valuable feedback.
Thanks!