Workbook Converter tips

The following tips can help you prepare your data and workbooks to ensure a successful conversion process.

Iterative data tips

Iterative data imports allow you to do the following:

• Update existing contact and address details.
• Add additional addresses.
• Update existing single-instance user-defined data.
• Add rows to existing multi-instance user-defined data.
• Add new user-defined data or objects, groups, and memberships.

• Do not include an Address worksheet type on the Contents worksheet if you are not adding or updating addresses.
• A Contact worksheet type is required, even if only adding or updating addresses or new groups:
  • Only the ID and PartyType columns are required.
  • The Contents worksheet must include a row for both the Address and Contact worksheet types when those types are included in the workbook.
• If adding new single-instance user-defined records, you must include a Contact worksheet type:
  • The worksheet requires a header row. The worksheet does not require any data to be included.
  • If a header is not included, any included data is not added for contacts who do not already have data in the user-defined table.
• If updating some (not all) columns in a single-instance user-defined table, only the ID column and the updating columns are required on the RelatedData worksheet type.
• Blank values in user-defined columns for existing contacts are not cleared of existing data. If the existing contact already has data in the user-defined column in the table, a blank value will not overwrite the existing data.
• You can only add rows to multi-instance user-defined data or activities; you cannot update these items.

General workbook tips

Consider the following when working with the workbook:

• Excel files must be saved as .xlsx. Older versions of Excel are not supported.

• Use the correct version of the sample workbook. The workbook version (for example, 20-300) must match the iMIS version.

• Sample workbook files can be found in the following directory:

  iMIS install directory\iMIS20-WorkbookConverter.zip

• Hover over column headers in each worksheet for tips or help for that column (if available).

• Do not delete any columns or headers, even if you do not have data for those columns. Doing so can generate errors.

Data preparation tips

Use the VLOOKUP, INDEX, and MATCH functions in Excel when merging data from multiple tables or sources. This is useful if you opt to export data from iMIS LAN for a Workbook Converter migration.

The following table identifies additional Excel functions that can be helpful in preparing your data:

Some ID numbers should be avoided because they are reserved system account IDs. The Workbook Converter displays an error if individual records use any of the following IDs:

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 194

Tips for populating the workbook

Consider the following when populating the workbook:

• For an initial conversion, you must have at least one Contact worksheet type defined on the Contents worksheet.
• The column headers must be on the first row, and all column headers must be displayed.
• When creating a new worksheet, copy the column headers from the sample worksheet into the first row of your new worksheet.
  You are not required to enter data for every column, but you must not delete or rename any column headers.

Tips for populating the workbook with user-defined data

Consider the following when populating the workbook with user-defined data:

• Do not include RelatedDataDefinition worksheet types for user-defined tables already created with Customizer or Panel Designer.
• The value in the Data Type column on the Contents worksheet must match the table name.
• If you need to import data that is not currently supported (for example, Company ID for Company records), import the data using a RelatedData worksheet type and run a post-conversion script to copy the data to the intended location.

Tips for populating column headers for RelatedData sheets

Do the following to quickly and accurately populate column headers for RelatedData sheets:

• For existing user-defined tables, use sp_columns [TableName] in SQL and copy the values from the COLUMN_NAME column.
• For user-defined tables in the workbook:
  1. Select all field names (column A, from cell A2 to the last field name) in the RelatedDataDefinition sheet.
  2. On the RelatedData sheet, right-click cell B1, then select Paste Special > Transpose.

Request assistance from Cloud Services for SQL-related items.

It is okay to create RelatedDataDefinition sheets without corresponding RelatedData sheets. You can create user-defined tables or fields to track new data in iMIS even if there is no existing data to import.

This approach is faster than creating objects manually using Panel Designer.

Searching for potential issues

Consider the following when searching for potential issues with your workbook:

• Use the Data > Filter feature in Excel to filter and sort columns.
• Confirm there is an O (organization) contact record in the workbook for any ID specified as a PrimaryOrganizationId for a P (individual) contact record.
• Verify all required fields are populated with data:
  • If Blank appears in a filter drop-down for a required column, select the checkbox to view and populate blank fields.
  • Check for blank rows. For example, review the ID column to ensure there are no blank cells that could indicate a blank row.
• Check for valid values, such as Address Purpose.
• Use the LEN function to identify data that might exceed the maximum property length.
• Use the TRIM function to remove leading or trailing spaces from the data.
• Microsoft Access can be helpful for checking large workbooks:
  • Import worksheets into an Access database.
  • Sort fields to check for leading spaces or empty rows.
  • Link sheets to detect orphaned records.
  • Export the worksheets after completing your review and making any necessary updates.

Running a test conversion

Request assistance from Cloud Services to run a test conversion.
Do the following to perform a test conversion:

1. Review Performing pre-conversion tasks.
2. Perform a test conversion with a smaller subset of data, for example, 1,000 contact records with related data.
3. Ensure the Contents worksheet includes all worksheets you intend to import.
4. Make sure the workbook is closed and saved before running the conversion.
5. Contact Cloud Services to arrange access to a sandbox migration server.
6. Launch WorkbookConverter.exe with the Test Only option enabled.

Migrating data in the Cloud

To minimize the impact on other clients in the shared server environment, consider the following when running Workbook Converter migrations:

• ASI Cloud Services provides access to sandbox servers for use by ASI and AiSP/AiC implementation teams.
• Submit a Hosting Support ticket to schedule a date and time for the migration and request that a database backup be transferred to the sandbox environment.
• After the migration, submit a Hosting Support ticket requesting that the data be transferred back to the production server.
• Submit a Tech Support ticket for any issues encountered during the migration process.

Final data cleanup tips

Consider the following before running the final conversion:

• Clear extraneous column data:
  • Select all blank cells in the worksheet, then go to Home > Clear > Clear All.
  • You can also select blank rows and go to Delete > Delete Sheet Rows.
• Before saving the workbook, move your cursor to cell A1 on each worksheet.
• Restore the database before running the final conversion:
  • Do not truncate the tables instead. You might get a clean test run, but the final conversion will produce Primary Key violations.
• If creating data in a single-instance user-defined table, clear Always Create in the table definition before beginning the conversion.
• If importing more than 50,000 rows, enable the UseMultitasking option.
• If importing more than 500,000 rows, have the Contents worksheet reference separate comma-delimited (.csv) files instead of worksheet tabs.

Common error messages and resolutions

The following are some common error messages you might see, and their common resolutions: