The following are frequently asked questions and recommendations regarding the Workbook Converter:
How much time is needed to run a data conversion?
The amount of time needed to run a data conversion varies for each database and depends on many factors. You can generally expect to convert about 16 contacts per second when not creating usernames and passwords for the contacts. If you are creating usernames and passwords, expect at least one second per contact.
Does the Workbook Converter support Dynamic Content Item-generated user-defined tables? What about Desktop-created user-defined tables?
Yes, data in user-defined tables can be populated in the workbook. In addition, certain other existing tables and tables you define at runtime can be added to the workbook.
What should I do if I have a problem with the conversion data?
The initial run should occur with the Test Only checkbox selected to ensure that the basic workbook structure and definitions are correct. Then, after any errors or warnings are addressed, run the Workbook Converter again with the Test Only option not selected.
There might still be problems that can only be detected during the actual conversion. If problems arise, then the process is to restore the database backup, analyze and correct the errors reported by the Workbook Converter, and then 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 ({ }), then you should modify the worksheet column to have a number format of General and run the conversion again.
Note: Numeric columns must use a numeric format of either General or Number. If Number is selected, make sure the option Use 1000 Separator (,) is not selected, and the Negative numbers format should be -1234.10.
What is the procedure if there is a failure mid-conversion?
You should analyze the failure, correct the workbook data, restore the target database to a known good pre-conversion version, recycle the IIS ASI scheduler instance associated with the database, then run the conversion application again.
Does the importer use the ID column as the iMIS ID?
The ID defined in the workbook will be saved to Name.MAJOR_KEY.
What should I do if I do not see the expected results when updating a single-instance related-data item?
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.
What if I want to update contact information without any address updates?
If you are updating contact information but there are no address updates, make sure that you do not specify Address type for the worksheet in which the contact information is found. Specify the worksheet as a Contact type only in the Contents worksheet.
For example, if you have a worksheet called Individual that is both a type Address and Contact, 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.
Why are the options I’ve specified not being honored?
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.
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.
If I only want to convert a RelatedData single or multi-instance table, do I need any other worksheets?
No. If you are only converting a RelatedData single or multi-instance table, you only need the Contents worksheet and the desired worksheets of WorksheetType RelatedData in your workbook. However, be sure 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.
Do I have to have a Contact worksheet if I’ve already imported records previously?
A worksheet of WorksheetType Contact is only required if:
- The ID being used to reference the associated data is not in the Name.MAJOR_KEY column in the database.
- There might be some ID values in the data that are not being treated as workbook IDs, that is, not treated as values from the Name.MAJOR_KEY column. Activity rows can be converted for existing contacts if the CO_ID and SOLICITOR_ID columns are not set.
- You want to create a contact with the given ID, the value of which is set into Name.MAJOR_KEY.
- You want to update address information for a given contact. In this case, you must have the following:
- A worksheet of WorksheetType Contact, referenced in the Contents worksheet.
- The first row of the Contact worksheet specifying all the columns necessary for Contact processing.
- Only the ID and PartyType columns need to be used.
For new Panel Editor dynamic tables, do I need to create the workbook for the RelatedTable definition if I’ve already created the source and fields in the database?
If the Panel Editor data sources already exist, you can specify worksheets of WorksheetType RelatedData 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.
Recommendations
- It is recommended that you have at least 16 GB of memory.
- Multiple smaller sized CSV files are preferable to one large file.