How to prepare a csv file for import to ead5

When a new weekly master schedule is released and the data has been parced in excel, that data needs to be imported to the server. Follow these steps to produce a csv file that can be imported to the server with the new milestone data.

1. Make a copy of the latest version of the file. Either look on the server. It's probably called something like "milestones_20_revision.csv" or similar.
2. Rename the file by incrementing the revision number.
3. Copy all the rows from the most recent master schedule, and append them to the end of the "milestones_20_revisions_file.csv" file.
4. Expand the id field so that it assigns new sequential ids to the new rows.
5. Reformat the 'milestone' field to SQL format.
1. In Excel, highlight the column -> Format Cells -> Number -> Custom.
2. Type in "yyyy-mm-dd" in the "Type:" field.
3. Click okay.
6. Reformat the created_at and updated_at fields to SQL format:
   
1. In Excel, highlight both columns -> Format Cells -> Number -> Custom.
2. Type in "yyyy-mm-dd hh:mm:ss" in the "Type:" field.
3. Click okay.
7. Save and close the file.

NB: If you reopen the csv file in Excel, Excel will reformat the dates. Before saving, re-reformat the dates back into SQL format.

How to import the milestones.csv file to MySQL rvb_production on ead5.lbl.gov

1. Put the CSV file on the ead5.lbl.gov 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 ead5.lbl.gov.
5. Put the following code in the SQL editor window:
   

   use rvb_production;
   LOAD DATA INFILE 'milestones_17_revisions.csv'
   INTO TABLE milestones
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   (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
[password]
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;