Workbook Converter FAQ

Yes, please review Using the Workbook Converter before running a conversion in a production environment. Consider the following before running the Workbook Converter in a production environment:

• Schedule imports during off-peak times for more efficiency. Imports must be performed after business hours or during the weekend.
• In order to minimize the impact to production sites, limit contact and data imports to around 1,000 contacts at a time. Importing larger batches of contacts to a production system will adversely affect performance and reliability.
• Before running the Workbook Converter, make sure you note the date and time. If the import fails when running in Live mode, the database will need to be restored by hosting. Having the date and time will help hosting know how far back the database needs to be restored.
• When importing, be sure you know the website (URL) and enter https:// before the website name.

The following are recommended to improve performance:

• Enable the UseMultitasking option when running an import. If it is not defined in the Workbooks > Option sheet, then it is set to FALSE by default. For best performance it should be set to TRUE when importing.

• Before starting a live import, disable all available index areas in Indexing preferences (Settings > RiSE > Indexing). Keep in mind the Content option cannot be disabled.

• Refer to the following for approximate Workbook Conversion import processing times:

Contacts	Groups	GroupPanelData rows	GroupPanelData (multi-instance) rows	Time
1500	1000	1000	1000	~10 minutes
5500	5000	5000	5000	~ 30 minutes
10500	10000	10000	10000	~ 1 hour

🚧 Warning

In order to minimize the impact to production sites, limit contact and data imports to around 1,000 contacts at a time. Importing larger batches of contacts to a production system will adversely affect performance and reliability.

The initial conversion should occur with the Test only checkbox selected to ensure that the basic workbook structure and definitions are correct. Once any errors or warnings are addressed, run the Workbook Converter again with the Test only option deselected.

There might still be problems that can only be detected during the actual conversion. If problems arise, do the following:

1. Restore the database backup.
2. Analyze and correct errors reported by the Workbook Converter.
3. Run the application again.

If you are working on a conversion with tens or hundreds of thousands of contacts and related records, you can define a set of Excel workbooks. Meaning, you will have a main workbook and a number of child CSV (comma separated value) text files.

When verifying data, if you see some values that are surrounded by braces ({ }), you should modify the worksheet column to have a General number format and run the conversion again.

📘 Note

Numeric columns must use either a General or Number number format. If Number is selected, make sure the option Use 1000 Separator (,) is not selected, and the Negative numbers format should be -1234.10.

If there is a failure mid-conversion, do the following:

1. Analyze and correct errors reported by the Workbook Converter.
2. Restore the target database to a known good pre-conversion version.
3. Recycle the iMISService associated with the database.
4. Run the application again.

Contact ASI Hosting for assistance recycling the iMISService instance associated with the database.

The ID defined in the workbook will be saved to ContactMain.ID.

If you are updating a single-instance related-data item and do not see the results you are expecting, search the workbook on the ID that was used. It is possible there were multiple rows updating the same single-instance data item.

The Workbook Converter modifies existing single-instance user-defined table data. If you have more than one row of single-instance data for the same data item with the same ID, the last row of single-instance data processed will overwrite any previously entered data.

If you are updating contact information but there are no address updates, make sure that you do not define the worksheet in which the contact information is found as an Address worksheet type . Specify the worksheet as a Contact worksheet type only in the Contents worksheet.

For example, if you have a worksheet called Individual that is both an Address and Contact worksheet type but do not provide the Address information on the worksheet (specifically, the AddressPurpose value, which the Workbook Converter is expecting), that will cause a warning to be displayed.

If you are adding a worksheet that sets the various available options, this worksheet must be identified in the Contents worksheet as an Options worksheet type.

If this worksheet is not specifically added to the Contents worksheet, the Workbook Converter will proceed with all options being defaulted, for example, CreateAccounts will default to FALSE.

No. If you are only converting a RelatedData single or multi-instance table, you only need the Contents worksheet and the desired RelatedData-type worksheets in your workbook.

When deleting unneeded worksheets to also remove them from the Contents worksheet. The Contents worksheet should only list those worksheets containing data being added or updated.

A Contact worksheet type is only required if:

• The ID being used to reference the associated data is not in the ContactMain.ID column in the database.
• You want to create a contact with the given ID, the value of which is set into ContactMain.ID.
•  You want to update address information for a given contact. In this case, you must have the following:
  • A Contact worksheet type, referenced in the Contents worksheet.
  • The first row of the Contact worksheet type, specifying all the columns necessary for Contact processing.
  • Only the ID and PartyType columns need to be used.

If the Panel Editor data sources already exist, you can specify RelatedData worksheet types in the Contents worksheet, making sure to specify the existing data source name in the Data Type column.

If you have already defined the Panel Editor data source and you are converting the actual data (where the data source is multi-instance), then you must specify True in the MultiInstance column of that data source's row in the Contents worksheet:

Otherwise, you will see an error similar to the following:

Warning: For Related Data: Education Info Data, Row: 2, Error adding data: Error: An item with the requested identity can't be found.

On the Group Memberships worksheet, you can enter multiple terms for a contact/group/role by entering multiple lines in the worksheet. For example, you could have ID 2200 on the Planning Committee, who served from 01/01/2001 to 12/31/2004 as Chair, then from 01/01/2005 to 12/31/2005 as a Member.

For existing details that overlap a worksheet row for a given contact/group/role, all details will be merged into a single detail with a combined membership term. This term will encompass the earliest effective date and the latest expiration date of all the matching details. As a result, you might have fewer membership detail records in the system than you provided as input through the worksheet.

The following chart demonstrates when multiple group membership details are entered within the Workbook Converter:

To exemplify the membership term dates, the following chart displays each contact/group/role over four years:

• ID 1102 has three terms listed:
  • The two terms that are highlighted in green overlap one another and will be merged into a single detail that has an effective date of 1/1/2011 and an expiration date of 12/31/2012.
  • The term highlighted in blue does not overlap any other terms with matching details, so this detail will not be merged.
• ID 1200 has four terms listed:
  • The three terms that are highlighted in red overlap one another and will be merged into a single detail that has an effective date of 8/1/2011 and an expiration date of 3/31/2013.
  • The term highlighted in orange does not overlap any other terms with matching details, so this detail will not be merged.
• ID 324 has three terms listed:
  • As all three these terms overlap one another, they will be merged into a single detail that has an effective date of 4/1/2011 and an expiration date of 4/30/2013.

The following worksheet is the equivalent to the previous chart in order to demonstrate how the group membership details are merged:

Clients need to be a Full User with the Sysadmin role and Staff Access Module Authorization Levels set to 8 -System Setup for each module.

The contact Name is used to populate the card holder name for AutoPay enrollments. For clients using iMIS Pay, the name is truncated if the total characters exceed 26.