Importing CSV into Postgresql with duplicate values that are not duplicate rows

374 views Asked by At

I am using Rails 4 and postgresql database and I have a question about entering in a CSV dataset into the database.

Date    Advertiser Name Impressions Clicks  CPM     CPA     CPC     CTR
10/21/13    Advertiser 1    77         0    4.05    0.00    0.00    0.00
10/21/13    Advertiser 2    10732      23   5.18    0.00    2.42    0.21
10/21/13    Advertiser 3    16941      14   4.64    11.23   5.62    0.08
10/22/13    Advertiser 1    59         0    3.67    0.00    0.00    0.00
10/22/13    Advertiser 2    10130      15   5.24    53.05   3.54    0.15
10/22/13    Advertiser 3    18400      22   4.59    10.55   3.84    0.12
10/23/13    Advertiser 1    77         0    4.06    0.00    0.00    0.00
10/23/13    Advertiser 2    9520      22    5.58    26.58   2.42    0.23

Using the data above I need to create a show page for each Advertiser.

Ultimately I need to have a list of Advertiser's that I can click on any one of them and go to their show page and display the informations relevant to each advertiser (impressions, clicks, cpm, etc)

Where I am confused is how to import the CSV data when there are rows with duplicate Advertiser's, but the other columns contain relevant and non duplicate information. How can I set up my database tables so that I will not have duplicate Advertiser's and still import and then display the correct information?

2

There are 2 answers

0
dhan On BEST ANSWER

Instead of creating two different models I just created 1 advertiser model and inputted the complete dataset into that model.

require 'csv'

desc "Import advertisers from csv file"
task :import => [:environment] do
  CSV.foreach('db/MediaMathPerformanceReport.csv', :headers => true) do |row|
    Advertiser.create!(row.to_hash)
  end
end

After the data was imported by the above rake task, I simply set up the show route as follows:

def show
  @advertiser = Advertiser.where(advertiser_name: advertiser_name)
end
3
benathon On

You will want to create two models: Advertiser and Site. (or maybe date).

Advertiser "has many" Sites, and Site "has one" advertiser. This association will allow you to import your data correctly.

See: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html