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 Stegman12,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
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?
Michael HallCourses 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) end end end
then from the command line you could call