Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial


John Stegman
John Stegman
12,182 Points

Transferring Access database

New to Rails and need help with the transfer of a MS Access database to Rails. I apologize in advance for any ignorance evidenced by the following.

To start the process, I am working with two of the main Access tables – Customers and Equipment In Access, Customers table has a primary key of cust_id In Access, Equipment has a primary key of equip_id and a field cust_id to relate that table to the customer record to which it belongs

In rails, I created the migrations and, when creating the equipment table, specified that it referenced the customers, adding customer:references at the end of the migration create command line.

Ran the migrations and set up the models Customer – has_many :equipment Equipment – belongs_to :customer

The schema.rb indicates that the equipment table has the index it needs to relate to the Customers

t.index ["customer_id"], name: "index_equipment_on_customer_id", using: :btree

I have exported the Access data to csv, imported the tables into the rails app The Equipment table column customer_id is nil for all the records – which I expected

Now trying to relate the tables together. Of course, rails has assigned new ID to each of the records in the tables and I need to get the “old” cust_id from the equipment table to look for its counterpart in the Customers table, and, using the new Rails generated Customer.id value, update the Equipment.customer_id to that value.

I can do this manually through the console

  Equipment Load (0.6ms)  SELECT  "equipment".* FROM "equipment" WHERE "equipment"."id" = $1 LIMIT $2  [["id", 6], ["LIMIT", 1]]
 => #<Equipment id: 6, cust_id: "9", equip_id: "17", customer_id: nil, created_at: "2017-05-19 00:00:00", updated_at: "2017-05-19 00:00:00"> 
e.customer_id = 2 – > set the customer id to the new id for cust_id = 9
 => 2 
   (12.7ms)  BEGIN
  Customer Load (0.5ms)  SELECT  "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  SQL (8.8ms)  UPDATE "equipment" SET "customer_id" = $1, "updated_at" = $2 WHERE "equipment"."id" = $3  [["customer_id", 2], ["updated_at", "2017-05-21 17:07:56.883762"], ["id", 6]]
   (6.7ms)  COMMIT
 => true 

  Customer Load (0.5ms)  SELECT  "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
 => #<Customer id: 2, cust_id: "9", first_name: "Joseph",  other data ---

  Equipment Load (0.6ms)  SELECT "equipment".* FROM "equipment" WHERE "equipment"."customer_id" = $1  [["customer_id", 2]]
 => #<ActiveRecord::Associations::CollectionProxy [#<Equipment id: 6, cust_id: "9", equip_id: "17", customer_id: 2, created_at: "2017-05-19 00:00:00", updated_at: "2017-05-21 17:07:56">]> 

I believe what I need to do is to read through the equipment table (15,000 + records) , use the “old” cust_id as a reference to the Customer table(6,000 + records) to find the new Customer.id and write that value into the equipment record as customer_id

Is there a way to accomplish this?

1 Answer

Michael Hall
Michael Hall
Courses Plus Student 30,909 Points

you could write a rake task to accomplish this. You would first need to load in the cvs file of the old records, this woud become an array of arrays that you could iterate over and find the id's once you have them create the new instance. This is just pseudo code, but hopefully it helps get you started.

::::The names of these files and variables could be anything, I've called them transfer, and namespace 'old data'::::: create a rake task in lib/tasks/transfer.rb

namespace :old_data do
  desc 'Transfer database'
  task :transfer do
    csv_text = File.read( 'access_data.csv' )
     csv_test.each do |record|
         (do your find a save here) 

then from the command line you could call

rake old_data:tranfser