wiki:Developer/KHIS2/KHIS15Imports

Importing data from KHIS 1.5 into KHIS 2.0

The following scripts are located in the SVN:

  • 5.01 identify new contacts.sql
  • 5.02 identify user staff+contact records.sql
  • 5.2 org imports.sql
  • 5.31 create new contacts.sql
  • 5.3 contact imports.sql
  • 5.4 project imports.sql
  • 5.7 resource imports.sql
  • 5.8 doc imports.sql
  • 5.9 comm imports.sql
  • 5.10 finance imports.sql
  • 5.11 - Reassign project owners.sql
  • 5.12 - permissionsSunderlandProject .sql
  • 5.13 - permissionsKHProject .sql
  • 5.14 - permissionsNorthumbriaProject .sql
  • 5.15 - permissionsTeessideProject .sql
  • 5.16 - permissionsKHProjectForAllsql.sql

Step 1: Identify user and staff contact records

  1. Open 5.02
  2. This script must be run in 2 parts, as identified by comments in the script
  3. In KHIS 2.0, user and contact records match 1:1 (and have the same ID). However, in KHIS 1.5, user and contact/staff records are completely unrelated. This script returns a list of user ids for users who have modified/inserted data and then, for each user, attempts to match them to staff/contact records.
  4. Part 1
    1. Modify the WHERE clauses in all of the SELECTs as appropriate; if importing all projects, simply remove the "P_RecvDate > '…'
    2. Highlight Part 1 and run
    3. This will return a list of user ids of people who own/have modified records in KHIS 1.5
  5. Part 2
    1. Paste the output from Part 1 (above) into the WHERE clause of the SELECT statement in Part 2 (removing the initial comma)
    2. For each user id, the script attempts to match them to existing staff/contact records
    3. The output should be pasted into an Excel sheet
  6. Excel sheet (see attachment)
    1. Now the fun part :-)
    2. Paste the output from Part 2 into an Excel sheet (delimited with ";")
    3. For users with more than one staff/contact record, you need to manually match them to the most appropriate record; the easiest way is to log into KHIS 1.5 and use the search box
    4. Column O contains SQL INSERT statements to populate a temporary table in KHIS 1.5 Once all users have been matched, delete the contents of [dbo].[Link_User_Staff_Contacts] in the KHIS 1.5 live d/b, then paste and run the INSERTs in SQL Management Studio
    5. Users with no matching staff or contact record must be created in a separate script (5.31 create new contacts.sql) - see Step 4 below

Step 2: Import organisations

  1. Open 5.2 org imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  4. Run the script!

Step 3: Import contacts

  1. Open 5.3 contact imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  4. Run the script!

Step 4: Import new contacts

  1. Open 5.31 create new contacts.sql
  2. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  3. For each new user identified in Step 1, 6.e., this script temporarily inserts a new contact record into the KHIS 1.5 contacts table, imports it into KHIS 2.0, then deletes the temp record
  4. Several parts need modifying before running this script:
    1. Find/replace the database name if required
    2. Modify the "delete from khislive..Link_User_Staff_Contacts where user_ref in (…)" statement to include a list of user id's without a contact record
    3. For each new contact, there must be an "insert into khislive..contacts (…)" statement
      1. NOTE You must manually look up the parent org id of each contact - 1935 = Knowledge House; 7782 = Sunderland; 10165 = Durham; 425 = Newscastle; 17339 = Northumbria
    4. For each new contact, there must be an "insert into khislive..link_user_staff_contacts values (…,null,@contact)" statement
    5. Amend the WHERE clause of the main SELECT statement to include one entry for each new contact - i.e. (con_forenames='xxx' and con_surname='xxx' and con_email='xxx')
    6. For each new contact, there must be a "delete from khislive..contacts where con_forenames='xxx' and con_surname='xxx' and con_email='xxx' statement at the bottom of the script
  5. Run the script!

Step 5: Create Collections

  1. Open 3.19 followup data bootstrap.sql
  2. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  3. Run the script!
  4. NOTE Do not close the script when complete, as you will need to paste the output into Step 6 !!

Step 6: Create template users

  1. Open 3.20 create template users.sql
  2. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  3. Paste the output from Step 5 into the script
    set @allUsersCollection =1
    set @KHManagers = 2
    set @allKHCollection = 3
    set @KHCentralCollection = 4
    set @allNorthumbriaCollection = 5
    set @NorthumbriaCentralCollection = 6
    set @allDurhamCollection = 7
    set @DurhamCentralCollection = 8
    set @allSunderlandCollection = 9
    set @SunderlandCentralCollection = 10
    set @allTeessideCollection = 11
    set @TeessideCentralCollection = 12
    set @allNewcastleCollection = 13
    set @NewcastleCentralCollection = 14
    
  4. Paste the output from 3.15 create all accounts.sql as follows:
    set @khaccount = 1
    set @durhamAccount =2
    set @teessideAccount = 3
    set @northumbriaAccount = 4
    set @sunderlandAccount = 5
    set @newcastleAccount = 6
    
  5. Run the script!
  6. NOTE Do not close the script when complete, as you will need to paste the output into Step 7 !!

Step 7: Create users

  1. Open 3.21 UserCreation?.sql
  2. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  3. Paste the output from Step 6 into the script
    set  @KHCentralTemplateContact = 2
    set  @northumbriaTemplateContact = 4
    set  @northumbriaCentralTemplateContact = 3
    set  @sunderlandTemplateContact = 6
    set  @sunderlandCentralTemplateContact = 5
    set  @durhamTemplateContact = 10
    set  @durhamCentralTemplateContact = 9
    set  @teessideTemplateContact = 8
    set  @teessideCentralTemplateContact = 7
    set  @newcastleTemplateContact = 12
    set  @newcastleCentralTemplateContact = 11
    
  4. Paste the output from 3.15 create all accounts.sql as follows:
    set @khaccount = 1
    set @durhamAccount =2
    set @teessideAccount = 3
    set @northumbriaAccount = 4
    set @sunderlandAccount = 5
    set @newcastleAccount = 6
    
  5. Run the script!

Step 8: Import projects

  1. Open 5.4 project imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  4. Modify the WHERE clause if required as it currently only returns projects received after 1/1/2007
  5. Check that the second part of the script - project team members import - is not commented out
  6. Modify the where clause of the project team members import so that only projects imported during the first part of the script are processed - i.e. line 5 of the snipped below
    DECLARE TeamCursor CURSOR FOR
    select convert(nvarchar(max), pa_proj) as id, convert(nvarchar(max), pa_academic) as staffid, 
    	convert(nvarchar(max), pa_contact) as contactid, PA_role
    from khislive_copy..projectteam
    where pa_proj in (select p.p_id from khislive_copy..project p where  p_Account not in ('KT','UT','SG','HSNE','UNR','UNP'))
    open TeamCursor 
    
  7. Run the script!

Step 9: Import resources

  1. Open 5.7 resource imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  4. Run the script!

Step 10: Import documents

  1. Open 5.8 doc imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION is commented-out
  4. Run the script!

Step 11: Import comms

  1. Open 5.9 comm imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION(S) are commented-out
  4. Part 1: responses import
    1. Check the document path matches the physical path on the server - if not, modify it (line 4 in snippet below)
      SELECT R_ID, 'RESPONSE' AS se_type, R_Project, R_University, CASE WHEN R_Response = 0 THEN 'Negative' ELSE 'Positive' END AS response, 
      	LTrim(RTrim(CONVERT(nvarchar(max), R_Desc) + ' (' + CASE R_University WHEN 'UD' THEN 'Durham University' WHEN 'UNN' THEN 'Northumbria University' 
      	WHEN 'UN' THEN 'Newcastle University' WHEN 'US' THEN 'University of Sunderland' WHEN 'UT' THEN 'University of Teesside' 
      	ELSE 'Knowledge House' END + ')')) AS details, CASE WHEN len(R_File) > 0 THEN 'C:\KHDocStore\Archive\KHOUSE\' + Replace(R_File,'/','\') ELSE '' END 
      	AS filename, CONVERT(nvarchar(MAX), R_Size) + 'KB' AS size, R_Created, R_CWho, R_Modified, R_MWho, SUBSTRING(R_File,9,len(R_File)) AS clientfile
      FROM khislive_copy..response
      
  5. Part 2: PSE import
    1. Modify the SELECT statement in the WHERE clause if required; if importing all projects, simply remove the "P_RecvDate > '…'
      WHERE (PSE.PSE_Project IN (SELECT P_ID FROM khislive_copy..Project WHERE (P_RecvDate >= '01/01/2007') AND (P_Account NOT IN ('KT', 'UT', 'SG', 'HSNE', 'UNR', 'UNP'))))
      
  6. Part 3: CSE import
    1. This should be ok as-is and does not need to be modified
  7. Part 4: Project Documents import
    1. Now the fun begins :-) The Doc_Synopsis field in the KHIS 1.5 ProjDocs? table contains data for "to", "from", "cc" and "date" - this needs to be separated out!
    2. Run the following SQL statement first (this lists all docs that have a "CC" specified)
      -- Docs with "CC" - run this statement first
      SELECT pd.Doc_id, REPLACE(REPLACE(pd.Doc_Synopsis,CHAR(10),''),CHAR(13),'')
      FROM khislive_copy..ProjDocs AS pd LEFT OUTER JOIN khislive_copy..Project AS p ON pd.Doc_Project = p.P_ID
      WHERE pd.Doc_Type = 'email' AND pd.Doc_Project IN (SELECT P_ID FROM khislive_copy..Project WHERE --(P_RecvDate >= '01/01/2007') AND 
      (P_Account NOT IN ('KT', 'UT', 'SG', 'HSNE', 'UNR', 'UNP'))) and pd.Doc_Synopsis like '%cc: %'
      
      1. Right-click over the results and Save Results As…
      2. Open the CSV file in Notepad++
      3. Replace all ,To: with ¬To: [NB be sure to select the "Use Extended Mode" radio button in the Find/Replace? dialogue box]
      4. Replace all \tFrom: with ¬From:
      5. Replace all \tcc: with ¬cc:
      6. Replace all \tDate: with ¬Date:
      7. Replace all \tAttachments with ¬Attachments
      8. Save the file
    3. Now run the following SQL statement (this lists all docs without a "CC")
      -- Docs without "CC" - run this statement second
      SELECT pd.Doc_id, REPLACE(REPLACE(pd.Doc_Synopsis,CHAR(10),''),CHAR(13),'')
      FROM khislive_copy..ProjDocs AS pd LEFT OUTER JOIN khislive_copy..Project AS p ON pd.Doc_Project = p.P_ID
      WHERE pd.Doc_Type = 'email' AND pd.Doc_Project IN (SELECT P_ID FROM khislive_copy..Project WHERE --(P_RecvDate >= '01/01/2007') AND 
      (P_Account NOT IN ('KT', 'UT', 'SG', 'HSNE', 'UNR', 'UNP'))) and pd.Doc_Synopsis not like '%cc: %'
      
      1. i. Right-click over the results and Save Results As…
      2. Open the CSV file in Notepad++
      3. Replace all ,To: with ¬To: [NB be sure to select the "Use Extended Mode" radio button in the Find/Replace? dialogue box]
      4. Replace all \tFrom: with ¬¬From:
      5. Replace all \tcc: with ¬cc:
      6. Replace all \tDate: with ¬Date:
      7. Replace all \tAttachments with ¬Attachments
      8. Save the file
    4. We now need to import the 2 modified CSV files into a new Excel file so…
      1. …open a new Excel Worksheet
      2. Select Data → Import External Data → Import Data… and select the first of the 2 CSV files
      3. In the Import Wizard, choose "Delimited" then specify "¬" as the delimiter (and uncheck the default "tab" delimiter)
      4. Choose Finish then click OK
      5. Navigate to the first blank row after all of the imported data, and place the cursor in column A
      6. Select Data → Import External Data → Import Data… and select the second CSV file
      7. In the Import Wizard, choose "Delimited" then specify "¬" as the delimiter (and uncheck the default "tab" delimiter)
      8. Choose Finish then click OK
      9. Save the Excel file
    5. We now need to import the data back into khislive_copy
      1. If the database table [dbo].ProjDocsImport? already exists, delete it
      2. In SQL Management Studio right-click over the khislive_copy database name, then choose Tasks → Import Data
      3. Select the Excel file

Step 12: Import finances

  1. Open 5.10 finance imports.sql
  2. Find/replace the database name if required
  3. Check BEGIN/ROLLBACK TRANSACTION(S) are commented-out
  4. Run the script!
Last modified 2 years ago Last modified on 17 Sep 2015 10:06:52