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!

Comments

  1. Hi. It doesnt work for me. It just loads and shows nothing. No error, or success message. And when I check the database to see if entry is successful, the database is empty

    ReplyDelete
    Replies
    1. Hi Muyiwa..

      Can you paste or attache your csv file with dummy data but same structure?

      Delete
    2. Be careful that you need to pass path of directory in which you have your csv files (not full path to the csv file).

      Eg. if file path is `/home/raj/test.csv` then you will need to run rake task as `rake csv_table_import['/home/raj']`

      Delete

Post a Comment