Documentation
Documentation

Workbook Converter tips

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

Iterative data tips

General workbook tips

Consider the following when working with the workbook:

  • Excel file must be saved as an .xlsx; older versions of Excel are not supported.

  • Use the correct version of the sample workbook. The workbook version (for example, 20-100) 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 spreadsheet 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:

Excel FunctionExample
VLOOKUP — Finds a supplied value in the first column of a table, and returns the corresponding value from another column.=VLOOKUP(H3,K1:N9,3,0) — Compare the value of cell H3 with values in the left column of the range K1:N9. When an exact match is found, return a corresponding value found in the third column of K1:N9. If no match is found, return #N/A.
COUNTIF — Returns the number of cells of a range that satisfy a given criteria.=COUNTIF(E2:E90,"=Colorado") — Count how many cells in the range E2:E90 equal the value Colorado.
FIND — Returns the position of a character from within a text string. This function is case-sensitive.=FIND("X",A3) — When A3 contains the value 57X3A, the function returns 3: this is the character position of X in cell A3.
MID — Returns a specified number of characters from the middle of a text string.=MID(A7,3,5) — Extracts five characters from cell A7, starting at character position 3.
LEFT — Returns a specified number of characters from the start of a text string.=LEFT(A3,6) — Extracts the first six characters from the left side of cell A3.
RIGHT — Returns a specified number of characters from the end of a text string.=RIGHT(A3,3) — Extracts the last three characters from the right side of cell A3.
PROPER — Converts all characters in a text string to proper (Sentence) case.=PROPER(HELLO world) — All letters that do not immediately follow another letter are set to Upper case. All other characters are Lower case: Hello World
UPPER — Converts all characters in a text string to Upper case.=UPPER(Hello World) — Returns text with all letters of all words in Upper case: HELLO WORLD
LOWER — Converts all characters in a text string to Lower case.=LOWER(HeLLo WoRLd) — Returns text with all letters of all words in Lower case: hello world
TRIM — Removes leading and trailing spaces, as well as duplicate spaces.=TRIM(A3) — When cell A3 contains " Full Time ", the function returns the string "Full Time", where leading and trailing spaces are removed.
LEN — Returns the length of a text string.=LEN (B2) — When cell B2 contains the phrase Hello World, the function returns a value of 11 (ten characters and one space).
& (concatenation operator) — Joins together text from other cells and text strings to form new text strings.=C3&", "&B3 — Creates an entry consisting of the text string from cell C3, a comma and space, and then the text from cell B3.
CONCATENATE — Joins together two or more text strings.=CONCATENATE(A3, " ", C4) — Creates an entry consisting of the text string from cell A3, a space, and then the text from cell C4.

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.
  • When creating a new worksheet, copy the column headers from the sample worksheet into the first row of your new worksheet.
    📘

    Note

    The column headers must be on the first row, and all column headers must be displayed.

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 data from the COLUMN_NAME column.
    📘

    Note

    Request assistance from Cloud Services for SQL-related items.

  • For user-defined tables in the workbook:
    1. Select all field names (column A, cell 2 to last field name) in the RelatedDataDefinition sheet.
    2. On the RelatedData sheet, right-click in cell B1, and select Paste Special > Transpose.
      📘

      Note

      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 is faster than creating objects manually using Panel Designer.

Tips for populating the workbook with sales history data

Consider the following when populating the workbook with sales history data:

  • Product codes must be created first in the database. Donations for products not marked as Is Charitable will not be displayed in Gifts Received.
    📘

    Note

    Products can be flagged as Is Charitable post-migration, and the gifts are then displayed.

  • The description is migrated into SalesHistory.Description and GiftReceived.DonationItem.
    📘

    Note

    Gifts added using iMIS post-migration use the product name.

  • If a contact does not exist in the database or workbook with the ID of a specified TributeId, the donation is not imported.
  • Source codes must be unique: the same source code cannot be used for multiple campaign codes.

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 on 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, check the ID column to make sure there are no blank cells that could indicate a blank row.
  • Check for valid values, for example, Address Purpose.
  • Use the LEN function to check for data that might exceed maximum property length.
  • Use the TRIM function to remove any leading or trailing blank spaces from the data.
  • MS 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.
    • Use Grouping feature to check for misspelled group names on the Group Memberships worksheet.
    • Export the worksheets after completing reviews and updating as necessary.

Running a test conversion

📘

Note

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, 1000 contact records with related data.
  3. Ensure the Contents worksheet includes all worksheets you intend to import.
  4. Make sure 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 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 or time for the migration and request a transfer of database backup to sandbox.
  • After migration, submit a Hosting Support ticket requesting transfer of data back to the production server.
  • Submit a Tech Support ticket for any issues encountered during migration process.

Final data cleanup tips

  • Clear extraneous column data:
    • Select all blank cells in the worksheet, and go to Home > Clear > Clear All.
    • You can also select blank rows and go to Delete > Delete Sheet Rows.
  • Before saving the worksheet, move your cursor to the origin cell (A1) on each worksheet.
  • Restore the database prior to running the final conversion:
    • Do not try to truncate tables instead. You might get a clean test run, but the final conversion will produce Primary Key violations.
  • More than 500,000 rows: Instead of referencing tabs on the workbook, the Contents worksheet should reference separate comma-delimited (.csv) files.

Common error messages and resolutions

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

Error MessageResolution
FormatExceptionThis error can indicate that a bit field has 0/1 or No/Yes rather than True/False. True or False is the acceptable format for bit fields.
XXXX Table not defined in workbookThis error can indicate that there are trailing spaces in the table name. Clear the blank spaces behind the name in Worksheet Name column in the Contents worksheet or GroupName column in the Group Names worksheet.
Error adding Contact with ID XXXXX: Service Error: Cannot insert duplicate key row in object 'dbo.ContactMain' with unique index 'IX_ContactMain_ID'. The duplicate key value is (XXXXX).Check Settings > Contacts > Formula. For v100/200, the formula must be M####ID at the time of migration. It can be changed after the migration.
Service Error: Violation of PRIMARY KEY constraint 'PK_PrefixRef'. Cannot insert duplicate key in object 'dbo.PrefixRef'. The duplicate key value is (Mrs.).The migration file has a row with a leading space in front of the prefix Mrs.
Object reference not set to an instance of an object. INNER EXCEPTION: CALL STACK: at WorkbookConverter.WorksheetMapExcelS tream.WorkbookInfo.InitializeWorkbook() ….The customer added formatting such as color coding, bolding, and so forth to the worksheet.
Error: Message: Invalid string: Name must be alphanumeric starting with a letterThis error indicates that there are spaces, even trailing spaces, in the Data Type names on the Contents worksheet, or in the PropertyName names on any of the data definition worksheets. These Data Types on the Contents worksheet represent new virtual business objects for the user-defined data, and the Property Names in the data definition worksheet become properties of those virtual objects. Neither can contain spaces in the names, or trailing spaces, which might not be apparent.
Warning: OrganizationName for Person record at 'Individuals'![column/row#] will be ignored.Data has been populated in one of the ignored columns. Some of the columns in the Contact-type worksheets are not applicable to both individuals and organizations. The column headers always exist in the worksheets and you might want to populate the headers for a party type where the headers are not used, just to assist with data population and reference. If data exists in these columns, these warning messages appear in the log to inform you that the data in those columns is not being imported. These warnings can be ignored, assuming you realize this data is not being imported. If you do not want these warning messages to appear in your log, then you must remove the data from those columns prior to running the migration.
Error: Workbook ID XXXX has not been defined in this workbook and is not an existing contact's alternate ID and/or Error: PrimaryOrganizationId XXXX at Individuals [X,X] refers to an Organization that has not been definedData is enclosed in curly brackets upon import, causing data to not be found. In this example, the ID is not found or recognized. This can occur if the cell formatting is set to Text for this data. The resolution is to set the cell format to General and import the data again.
Warning: For Source ID: XXXXX, could not create user account: Service Error: Failed to create user [email protected]: ProviderError.Set the Default Department Group for this organization to the default Administrative group. If not set to the default Administrative group, user accounts cannot be created and the error occurs.
(Post-migration data validation) Contact not found with standard directory search ("By all common criteria")This directory query uses the PartyWithDefaultAddressData business object. If you imported contacts without any addresses, they will not have a row in the address tables and will not be returned by this search. If your database might have contacts without addresses, you might want to create an alternate directory search query which uses the PartyData business object rather than PartyWithDefaultAddressData.

Updated 3 months ago

Did this page help you?