Saturday, October 2, 2010

How to import the milestones.csv file to MySQL rvb_production on

  1. Put the CSV file on the server under your home directery: /home/arsturges. In Windows you can do this using F-secure, or in Linux via the command line.
  2. Then copy the file to /var/lib/mysql/rvb_production using the following commands:
    sudo mv milestones_19_revisions.csv /var/lib/mysql/rvb_production [then enter password]. Why can't you just upload it directly to the mysql directory? Because of permissions. I don't know how to do it.
  3. Check to see if the move was successful: sudo ls /var/lib/mysql/rvb_production
  4. Now move over to MySQL Workbench. Open a connection to
  5. Put the following code in the SQL editor window:
    use rvb_production;
    LOAD DATA INFILE 'milestones_17_revisions.csv'
    INTO TABLE milestones
    (id, rulemaking_id, phase_id,
    task_id, date_category_id, milestone, revision_number,
    created_at, updated_at);
    The above code is much the same as what one would find with a google search, but here we don't use the LOCAL keyword in line 2.
  6. Try running the code. It should throw a "duplicate entry" error, which is good because the database is already populated.
  7. When you're sure this code will actually work, move over to a command line to truncate the milestones table. You have to have an empty table to import to. From the command line on the ead5 server:
    mysql -u root -p
    use rvb_production;
    truncate table milestones;
    select * from milestones;

  8. Now go back to MySQL Workbench and run the import script. With 100k rows, it should take about 15 seconds, so give it time to complete.
  9. Now check in MySQL that it worked: select count(*) from milestones;

No comments:

Post a Comment