Using the Workbook Converter

The Workbook conversion allows you to specify details for each contact, defining which contacts are created as persons or organizations, and redirecting activity/history/user-defined data to the appropriate ID.

Phone numbers, email addresses, fax numbers, etc are stored in their associated address records.

Contacts whose status is either Inactive or Marked for Deletion should be reviewed. The Workbook conversion only converts information for Active contacts.

The Workbook conversion allows you to define multiple objects with all their properties, and they convert to iMIS 300 as virtual objects that are accessible to IQA and the Panel Editor.

Note: Remove any special formatting, for example, color, bolding, and so forth, from your Workbook data.

Before you begin preparing your Workbook data in anticipation of your conversion task, review the Before you begin and Performing pre-conversion tasks sections.

1. See recommended pre-conversion checklist.
2. (If iMIS 20-300 is self-hosted)

   1. Navigate to the Net\bin folder (under the location where iMIS is installed) to obtain the iMIS20-300WorkbookConverter.zip file. The default location is C:\Program Files (x86)\ASI\iMIS\Net\bin.

   2. Copy the file to a local folder, and extract its contents.

3. (If iMIS 20-300 is ASI-hosted) Contact ASI hosting or your AiSP to obtain the Workbook Converter ZIP file.
4. Copy the Excel workbook file from that folder to a backed-up location for editing, and rename the file. Note that the sample workbook is designated as read-only, so you should save changes into a new file.

Note: This must be in the .xlsx format– older versions are not supported.

5. Complete the spreadsheet with the organization's data, following the instructions provided in the workbook.

Note: Hover over column headers in each spreadsheet for tips/help on that column.

6. Enter/prepare your data:
• Contents worksheet: Be sure that each worksheet tab that contains data is listed here, and that there is an entry for all appropriate Worksheet Types. For example, if you have a worksheet called Individuals, which contains both contact and address information, be sure that there are two entries for that worksheet, one with type Contact, and another with type Address.
  
  List the worksheets in the Contents page in the order listed on the Instructions tab, that is, all Contact type sheets first, then Address type, and so forth.
  • Remove any trailing spaces from all entries in the Contents tab.
• Contact worksheet type: Create one or more worksheets with type Contact.. The sample workbook instructions show which fields apply to each type of contact. Fields at the top of the worksheet that are in bold apply to that worksheet's PartyType. You must have at least one worksheet of type Contact. Follow the instructions provided in the worksheet comments.

Warning!

To create a new worksheet, copy the entire first row containing the column headers from one of the provided examples with a matching WorksheetType into the first row of your new worksheet. The column headers must be on the first row, and all column headers must appear. You do not have to fill out every column, but do not delete or rename any column headers.

• Options WorksheetType – Refer to comments on the Options worksheet in the sample workbook for more information.

  Note: If you are adding a worksheet that sets the various available options, this worksheet must be identified in the Contents worksheet with a Worksheet Type of Options.

  • Create User Accounts allows you to create accounts for new contacts. In order for this option to even be considered, Test Only must be disabled, and the CreateAccounts setting must be set to TRUE in the Options worksheet. New accounts are not created if the Test Only option is enabled. Disabling Test Only and setting CreateAccounts to TRUE will allow the conversion process to generate usernames based on the preferred mailing address email addresses.
    • Duplicates: If the email address already exists in the iMIS database, the duplicate is flagged in your error log.
    • No email: If the contact has no email address, no username is created for them.
    • Passwords: The password is a random value, so the user will go through the Forgot my password route to reset it to a password of their choosing

    Note: iMIS will only create a new user account if a contact is being added. Modifying a contact will not result in a new user account.

• Address worksheet type: Enter all of the addresses for each contact - individual or organization - into the appropriate columns of the contact worksheets. If a contact has more than one address, you may enter additional addresses into any worksheet with worksheet type of Address (as defined on the Contents page). Be sure to list all of the worksheets that contain address information in the Contents page, with a worksheet type of Address.

Note: The very first address for each ID must be marked as the default address. All other addresses can be flagged to override that default with a specific value for various communication reasons (preferred mail, bill, and ship).

• RelatedDataDefinition worksheet type: You can use this worksheet type to define the structure of an associated worksheet of the RelatedData type. The example workbook contains the following worksheets as examples of this type:
  • Education Info Definition: Defines the fields used in the associated Education Info Data worksheet. Follow the instructions provided in the worksheet comments.
  • Personal Info Definition: Defines the fields used in the associated Personal Info Data worksheet. Follow the instructions provided in the worksheet comments.
• RelatedData worksheet type: Worksheets of this type are user-defined. Their structure can be defined by an associated worksheet of the RelatedDataDefinition type or by a table or panel editor data source that already exists in iMIS.
  
  The example workbook contains the following worksheets as examples of this type:
  • Education Info Data: This worksheet’s structure is defined by the associated Education Info Definition worksheet of the RelatedDataDefinition type. This worksheet contains multi-instance data; it can contain multiple rows of data for a single contact. Enter the educational information for each contact into the appropriate columns.
  • Personal Info Data: This worksheet’s structure is defined by the associated Personal Info Definition worksheet of the RelatedDataDefinition type. This worksheet contains single-instance data; it can only contain one row of data for a single contact. Enter the personal information for each contact into the appropriate column.
  • Activities: This worksheet is an example of a RelatedData worksheet type that does not have an associated RelatedDataDefinition worksheet type. The table referenced by the worksheet already exists in iMIS. Enter information about the activities, such as sales, for each contact in the appropriate column. In order to import the data, the activity type must be valid in iMIS. If the activity type is valid, but not fully set up in the Advanced Accounting Console, the data imports, but you cannot view it. Follow the instructions provided in the worksheet comments.
  
  
  

  Your workbook may contain worksheets of the RelatedData type that are defined by existing tables in iMIS. If a worksheet of this type contains multiple rows of data for a single contact (multi-instance table data), enter True for that worksheet in the MultiInstance column on the Contents tab. This specification ensures that the Workbook Converter does not produce errors for these rows when run in Test Only mode. TheActivities worksheet in the sample workbook is an example of a predefined multi-instance table.

  If the worksheet can only contain one data row per contact (single-instance table data), leave the MultiInstance column blank. When in Test Only mode, the Workbook Converter checks for multiple occurrences of an ID in single-instance table data and produces errors for those rows.

When all entries are complete, save and close the workbook. The workbook must be closed when running the conversion.

Note: The workbook must be saved with an extension of .xlsx. You will receive an error if you attempt to convert using an .xls extension.

Note: If you have a column of data in a spreadsheet that should be empty, but instead contains a blank or space character (whitespace), then you will get an error during the TestOnly run. To clear a space from a cell in Excel, right-click the cell, and select Delete. To clear an entire column, it may be easier to copy the column header to the clipboard, then highlight the entire column and delete it, and then copy the column header into the first available cell at the end of line 1.

1. Locate the WorkbookConverter.exe file. Double-click the file to launch the converter.

Note: The conversion utility now requires .NET 4.5. If not present, you will be prompted to update.

2. Fill out the Workbook Conversion utility fields as follows:
   • Source Workbook: Configure the converter to use the correct spreadsheet file. You can use the folder icon to browse to your workbook file.
   • Max Contacts: Leave at zero.
   • Test Only: Keep this checked until you have run a successful test conversion that reports no errors.
   • Destination WebSite and Protocol fields: There are two protocols that can be used; you can use an https protocol to access the conversion utility from outside the ASI firewall (which applies to all except ASI staff, who have the additional option of operating within the firewall using NetTcp protocol).
   • If using https protocol, select https://[DomainName]/[VirtualRoot] as the Destination WebSite.

   Note: ASI Hosting or your AiSP will set up the target iMIS 20-300 website with a valid security certificate.

   • If using the NetTcp protocol, select net.tcp://[DomainName]:808/[VirtualRoot]/NetTcp as the Destination WebSite.
     
     Replace [Domain Name] with localhost, the IP address, or the proper name of the destination website.
     Replace [VirtualRoot] with the name given to the virtual root during the installation of iMIS.

   Note: Workbook Converter saves the modified website name, so when you next run the conversion, the modified name automatically displays as the Destination WebSite.
   

   • Username and Password: Hosting will provide credentials for running the conversion.
3. Run the conversion in Test Only mode.
   1. Verify that the spreadsheet file is closed.
   2. Click Convert to run the import test. It can take several minutes for the initial connection to succeed, and no information appears on the conversion update window until after that is complete.

   Note: If you get a timeout error after 60 seconds, try resetting IIS and re-starting.

   3. After conversion completes, click Export Log to save the import information and any errors.

   Note: Exported logs include additional details on any Service Errors encountered.

   4. Click Update Workbook to allow the converter to update the spreadsheet and flag any errors in red.
4. Open the spreadsheet and fix any problems identified, such as duplicate records.

Note: To display the dates differently, format the specific cell by selecting Format Cells > Number > Dates. Choose from the list of date formats and then select OK.

5. Re-run the converter in Test Only mode until it returns no errors.
6. Back up the iMIS database. Run the converter with Test Only mode disabled. Some additional errors may be reported at this point. You must examine the conversion log to identify all Errors and Warnings, and either correct them in the workbook and initiate a new conversion with a fresh 20-300 database, or in a few cases, you may correct them directly in 20-300.

   Warning!  

   After you have restored the fresh database, you must recycle the IIS ASI scheduler instance associated with the database.

7. Verify the conversion by logging into iMIS and locating records that should have been imported.

Once you have completed the conversion process, proceed to the post-conversion tasks to ensure that your conversion process concludes seamlessly.

• If you re-run the Workbook Converter tool immediately after a data import, you may receive errors. To avoid these errors, follow these instruction before re-running the tool:
  1. Close the WorkbookConverter.exe file.
  2. Stop Internet Information Services (IIS).
  3. Restore the database.
  4. Start IIS.
  5. Open the WorkbookConverter.exe file and begin the import.
• If you use the Workbook Converter tool to create data in a single-instance, user-defined table, you must deselect Always create in the table definition (located in the User Defined Tables window) before beginning the conversion. You can re-select Always create after the conversion has successfully completed. (Settings > Membership > Configuring membership options. Click Additional Windows, and then click Define Tables.) See To create a user-defined table for more information on user-defined tables.

Confirmation emails will be sent. It is important for staff accounts to be used for all post-conversion testing of orders/event registrations/and so forth. If this is not feasible, another option is to reset the preferred email address for selected contacts.

1. Ensure expected numbers of Individual and Organization records were created.
2. Review your committees, chapters, and so forth and compare rosters. Review roles that were assigned to members.
3. Ensure your database is accurately prepared.
4. Stop the IIS ASI Scheduler. Use the following command:

   iisreset -stop

5. Open the iMIS DB Maintenance Utility, and click Prepare iMIS Database. This recreates the iMISPublishQueue and iMISPublishService.
6. Synchronize the NetContacts in your Name table:
   1. In the DB Maintenance utility, select the NetContacts tab.
   2. Click Disable Name Table Trigger to disable triggers temporarily.
   3. Import your records in the Advanced Accounting Console. Select Utilities > Data Transfer Utilities > Import Utility.
   4. When the import is done, click Enable Name Table Trigger to restore the trigger.
7. Do one of the following to re-enable the ContactMain trigger:
   • In SQL Server Management Studio, go to [Database] > Tables > dbo.ContactMain > Triggers. Right-click on asi_ContactMain_Insert_Update_Delete and click Enable.
   • You can also run the following query in SQL Server Management Studio:

   ALTER TABLE ContactMain ENABLE TRIGGER asi_ContactMain_Insert_Update_Delete

8. Edit the ASI Scheduler web.config file to restore the PublishServerCode value to match what is set in the PublishServerRef table.
9. Delete the contents of the C:\Program Files (x86)\ASI\[iMISInstanceName]\indexServiceProtected\Search\Lucene folder.
10. Restart the IIS ASI Scheduler. Use the following command:

    iisreset -start

11. Secure the database.

Queries can be used to check for issues:

• Committees: Run this query on source database to get a list of all the committees and their members, and check resulting data in 20-300 (search for the committee and check against the roster there). Compare names, roles, and in cases where the membership is date-limited, the effective and expiration dates of membership:

SELECT Activity.Product_Code, Committee_Position.TITLE, Name.FULL_NAME, Name.COMPANY, Activity.ACTION_CODES, Activity.EFFECTIVE_DATE, Activity.THRU_DATE, Committee_Position.RANK, Activity.TRANSACTION_DATE

from Activity INNER JOIN Name on Name.ID = Activity.ID LEFT OUTER JOIN Committee_Position on Committee_Position.POSITION_CODE = Activity.ACTION_CODES

Where Activity.ACTIVITY_TYPE = 'COMMITTEE' AND Activity.PRODUCT_CODE LIKE 'COMMITTEE/%'

order by Product_Code

• Duplicate organizations: Run this query to see any duplicate Organizations that got created in 20-300 (multiple companies were created having the same name) – and investigate the cause.

select COUNT(i.ContactKey) as CompanyRecords, InstituteName as CompanyName

from Institute i, ContactMain cm where i.ContactKey = cm.ContactKey

group by InstituteName having COUNT(i.contactkey)>1