How to prepare the master schedule for the macro run

Before you run the main parsing macro on a new master schedule, you need to set up a lookup table with Excel sheet ids in one column and the corresponding rulemaking id in another.

1. Create a new tab at the front of the workbook called "Rulemakings."
2. Run the macro called PrintWorkSheetNames(). This will populate the Rulemakings tab with a list of all the rulemakings in that workbook and their corresponding Excel sheet indexes.
3. Use MySQL Workbench (or some tool) to copy the production version of the rulemakings table into the Rulemakings tab. Leave an empty column between the first set of columns (created by the macro) and the second set of columns (which you just pasted in) so that each group can be sorted independently.
4. Sort each group by the rulemaking abbreviation. These SHOULD be unique, and they should be the same.
5. When you are sure that each row lines up properly as a match all the way across, delete the empty separating row (thus locking them together for the sort).
6. Make the "sheet index" the first column and the "rulemaking_id" the second column.
7. Sort it by sheet index. This is so that vlookup() works properly.
   
8. Highlight the whole range of those two columns and name the range "rulemakings." This is used by the product_tabs_to_dates() macro.
   

With those steps complete, the workbook is now ready to run the product_tabs_to_dates() macro.

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 createa new revision to complete an import of new milestones

When you have successfully imported a csv file of new milestones, you have to tell Ruby on Rails about it.

1. Log in to the production server (ead5.lbl.gov) through a web browser. Note that you must log in as an administrator.
2. Check to make sure the date showing on the home page is the date of the last revision imported, and not the most recent one.
3. Navigate to http://ead5.lbl.gov/revisions and click on "new revision."
4. Enter the date of the revision (usually indicated by the filename, so named by DOE)
5. Click submit. A new revision is created, with an automatically incremented revision_number. Note that this is not the same as the revision_id. That is just a database identifier. We only care about the revision_number. These may or may not be the same.
6. Navigate back to the home page and check that date has been updated, and that data still properly loads.
7. You're done.
   

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;