Re-import of TmpV2 data from Julie's Export 2014-09-08

From CCMDB Wiki
Jump to navigation Jump to search

Original import of TmpV2 went wrong. Julie provided a new export 2014-09-04.

These are instructions for one of the imports. See Re-import of TmpV2 data from Julie's Export 2014-09-18 fro more up-to-date next import.

Legacy below here

These are the instructions for the one-time import of her data into the live Centralized_data.mdb.

File location

The files for this process are stored in x:\CCMDB\z_archive\2014-09-08_Tmp_reimport

<a folder> in the instructions can be any folder, ideally for speed one on the local disc

steps

process

prep steps

The following steps can be done any time in preparation:

  1. copy tmp_to_centralized_importer.mdb from #File location to <a folder>
  2. open using usual tasty pw
  3. confirm table L_TmpV2_1_cleaned_imported is empty and import TmpV2_1.mdb type data
    • statistician provided the data as .xlsx because TmpV2_1.mdb data needs to be limited to valid instance of records
    • use Access 2010 for this next time, problems with 2003 because of size limitation on xls files and commas in comments breaking .csv format... had to put quotes around the comments etc, better to just use 2010 next time...
  4. confirm table L_TmpV2_2_IDmaker is empty
  5. run query step1_populate_idmaker to populate the D_Tmp_ID field with an autonumber. L_TmpV2 does not use an autonumber for the field but we need one.

import steps

The following steps then have to be done on live data in a "pull" state:

  1. copy backup of most current centralized_data.mdb to #File location as centralized_data_pre_tmpv2_import.mdb
  2. copy centralized_data.mdb to same <a folder> as in #prep steps
  3. link tables L_Log and L_TmpV2 into tmp_to_centralized_importer.mdb
  4. run query step2_delete
    • SELECT L_TmpV2.D_ID FROM L_Log INNER JOIN L_TmpV2 ON L_Log.D_ID = L_TmpV2.D_ID WHERE (((L_Log.RecordStatus)<>"incomplete"));
    • and delete all records listed; this will delete tmp entries for all except incomplete L_Log entries
  5. delete table L_TmpV2_3_orphans_wo_L_Log_parent if present
  6. run query step31_put_orphans_into_separate_table
    • there are a number of orphans in the .mdb that have become disconnected from their parent record in L_Log, likely due to location chagnes; this was flagged during analysis and decided to just segregate the records for now and have Pagasa correct later if needed. The records would already be in the correct format to cut-and-paste them into L_TmpV2 once the D_ID gets corrected.
  7. run query step32_append_matches_to_L_TmpV2 to actually append the tmp records to L_TmpV2
  8. move centralized_data.mdb back to the output directory on the Regional Server
  9. move tmp_to_centralized_importer.mdb to #File location; L_TmpV2_3_orphans_wo_L_Log_parent may be needed in the future to reinstate some records after fixing D_IDs (see #possible future steps)

possible future steps

If we need to reinstate any of the records that were orphaned:

  1. open #File location\tmp_to_centralized_importer.mdb
  2. open table L_TmpV2_3_orphans_wo_L_Log_parent
  3. correct the D_ID for the relevant records
  4. cut-and-paste the records into an up-to-date copy of centralized_data.mdb