Transferring Access database

Question

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

e=Equipment.find(6)
  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 
e.save
   (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 

c=Customer.find(2)
  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 ---

c.equipment
  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?

Answer 1 · 2017-05-30T15:09:24Z

May 30, 2017 3:09pm

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) 
     end 
  end
end

then from the command line you could call

rake old_data:tranfser

Welcome to the Treehouse Community

Looking to learn something new?

John Stegman

John Stegman

Transferring Access database

1 Answer

Michael Hall

Michael Hall