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

Question

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?

if there is a duplicate advisor, which row do you want to show? The most recent? or? — benathon
– benathon, Commented Dec 3, 2013 at 1:47
I should have mentioned that the data needs to be displayed as a graph. All of the data related to an advertisor needs to be displayed over time. — dhan
– dhan, Commented Dec 3, 2013 at 1:51

benathon · Accepted Answer · 2013-12-03 02:10:05Z

1

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

answered Dec 3, 2013 at 2:10

benathon

7,6712 gold badges44 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dhan Over a year ago

So after I create these two models of Advertiser and Date, how would I import the data? would it all just be within one import rake task or would it have to be separate? Do I just do the import in one task shown here?

benathon Over a year ago

I would probably create an import rake. You will need to loop through each line. I usually like to make find_or_create(id) methods. So for you this method would return an existing Advertiser, or a new one with that id. Then insert a Site and set the advertiser. I couldn't find a better video, but here is many-to-many (which is not what you have) railscasts.com/episodes/47-two-many-to-many

benathon Over a year ago

You can use the link you gave as a starting place

dhan · Accepted Answer · 2013-12-04 03:36:03Z

1

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

answered Dec 4, 2013 at 3:36

dhan

435 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related