Administering the Generic Import Wizard

Aptify includes a wizard that imports any data into the system. This wizard is ideal for recurring imports of small sets of data. Note that this tool is not intended to be used for data conversions.

By default, the standard Aptify installation supports importing from tab-delimited text files, Microsoft Excel files, and Microsoft Access databases. Currently, for Azure environment, only “Tab Delimited Text File” and “Excel File” import source types are supported.

The following steps provide an overview of how to use the wizard to import data into Aptify:

1. Identify the data that you want to import into Aptify. Also, identify the following:
   • The current format of the data.
   • The location where the data should be added into Aptify. For example, for the customer contact information to import, you will want to import that data into the Persons entity.
2. If the data's format is not one of the existing source type, create a new Import Source Types record. See Creating New Import Source Types.
   • Note that creating new source types requires that a developer write code.
3. Create a new Import Sources record for your data. This record ties a import source type to the entity to which you are importing the data. Also, you can specify an object that overwrites the default record matching and posting behavior with your own business logic. See Creating Import Sources.
   • This assumes that a suitable Import Source does not already exist. However, you can use an existing source if your system already has a record that specifies the data's source type, its fields, and the target entity.
   • Note that creating your business logic for the matching and posting processes requires that a developer write code. Alternatively, you can define and modify matching criteria while running the Generic Import wizard, reducing the amount of code a developer needs to write to specify different matching logic. See Running the Generic Import Wizard for more details.
4. Run the Generic Import wizard from the Import Runs service. See Running the Generic Import Wizard. 
5. Match import run details with existing records in the entity within the Import Runs record that was automatically generated by the wizard. Then, post the import run details to create new records for unmatched items. See Matching and Posting Import Results.
6. Review the newly created records to confirm that the data was successfully imported into Aptify.

This topic covers the following sub-topics:

• Creating New Import Source Types
• Creating Import Sources
• Running the Generic Import Wizard
• Matching and Posting Import Results

Creating New Import Source Types

Aptify provides three default Import Source Types:

• Tab-delimited Text File: Import from a text file with field data separated by a tab.
• Excel File: Import from an Excel file.
• Access Database: Import from an Access database.

If you want to import data that is not in one of these formats, you need to create a new Import Source Type. Follow these steps:

1. A developer writes and compiles code that implements Aptify's IImportSourceReader interface to create an import process for the desired file type.
2. The developer writes and compiles code that implements Aptify's IImportSourceTypePropPage to specify the properties for the new source type.
   
   • The Prop Page specifies an input map control that appears in the Generic Import wizard and on the Import Runs record's Input Maps tab.
   • Creating new code is beyond the scope of this document. Refer to the Aptify Software Development Kit (SDK) for information on these interfaces. 
3. The developer or an administrator adds the source reader and property page object files to the Object Repository.
   
   • See Creating Object Repository Objects for more information on adding new items to the repository. 
4. Open a new record from the Import Source Types service (found under the Aptify Framework Administration application).
5. Specify the new source type's Name and Description in the fields provided.
6. Enter the .NET assembly name for the SourceReader file in the Assembly Name field.
7. Enter the Object Repository location of the SourceReader's object in the Object field.
   
   • Alternatively, you can click the plus (Emoticon: plus) icon to the right of the field to open the Select Object Repository Object dialog. You can use this dialog to browse for an existing object or to add a new object to the repository (if you have not done so already). See Creating Object Repository Objects for details. 
8. Enter the class within the .NET assembly that corresponds to the SourceReader functionality in the Class field.
9. Enter the .NET assembly name for the source's PropPage file in the Prop Page Assembly Name field.
10. Enter the Object Repository location of the PropPage's object in the Prop Page Object field.
    
    • Alternatively, you can click the plus (Emoticon: plus) icon to the right of the field to open the Select Object Repository Object dialog. You can use this dialog to browse for an existing object or to add a new object to the repository (if you have not done so already). See Creating Object Repository Objects for details. 
11. Enter the class within the .NET assembly that corresponds to the PropPage for this source type in the Prop Page Class field.  
    
     
12. Click the Input Properties tab.
13. Define a sub-type record for each property required by the Input Source Type.
    
    • This includes properties such as Filename and HasHeader.
    • See the three default source types for sample Input Properties.
    • Specify the property in the Name field, select the data Type from the drop-down list (such as String), and select the Is Required option, if the property is required to successfully perform the import operation.
      • See About the Input Properties Form for information on the Input Properties form.  
        
        
         
14. Save and close the Import Source Types record.

Creating Import Sources

An Import Sources record ties an Import Source Type with a target entity. You can also specify your own business logic for handling the matching and posting processes in an Import Run.

Before you can import data into an entity, you must have an appropriate Import Source configured. Follow these steps to create a new Import Source:

1. If desired, a developer writes and compiles code that implements the appropriate Aptify interface to define matching or posting behavior.
   
   • To develop new matching logic, a developer writes and compiles code that implements Aptify's IImportRecordMatch interface.
   • To develop new posting logic, a developer writes and compiles code that implements Aptify's IImportRecordPost interface.
   • Creating new code is beyond the scope of this document. Refer to the Aptify Software Development Kit (SDK) for information on these interfaces. 
2. If a developer has defined new matching or posting behavior, the developer or an administrator adds the matching and/or posting object file to the Object Repository.
   
   • See Creating Object Repository Objects for more information on adding new items to the repository. 
3. Open a new record from the Import Sources service (found under the Aptify Framework Administration application).
4. Enter a Name for the import source.
5. Enter a source type in the Import Source Type link box.
6. In the Mapping Entity link box, enter the entity that you want to import data to.
7. In the Record Template link box, enter an existing Record Template for the entity you specified in the Mapping Entity field, if desired.
   
   • This field is optional. If you specify a record template, records created by the Import Runs posting function will use the default specified in the template if the import file does not specify a value for a particular field. This is particularly useful if your entity has a required field that does not have a mapped field in the import file.
     • For example, if your entity contains a required Category field that is not in your source data, the import fails since new records cannot be saved without specifying a category. However, if you use a Record Template that specifies a default category, the import succeeds since the posting process uses the information in the Record Template if not provided by the import file. 
   • See the Working with Record Templates and Administering Pending Changes for more information on Record Templates.  
     
     
8. Configure the Record Match and/or Record Post fields if a developer has written code that modifies the standard matching and/or posting behavior.
   
   • If you leave these fields blank, the system uses the default matching and posting logic.
   • Follow these steps to specify your own matching logic:
     1. Enter the .NET assembly name for the Record Match file in the Record Match Assembly Name field. 
     2. Enter the Object Repository location of the Record Match object in the Record Match Object field. (Alternatively, you can click the plus (+) icon to the right of the field to open the Select Object Repository Object dialog. You can use this dialog to browse for an existing object or to add a new object to the repository.)
     3. Enter the class within the .NET assembly that defines the matching logic in the Record Match Class field.  
   • Follow these steps to specify your own posting logic: 
     1. Enter the .NET assembly name for the Record Post file in the Record Post Assembly Name field.
     2. Enter the Object Repository location of the Record Post object in the Record Post Object field. (Alternatively, you can click the plus (+) icon to the right of the field to open the Select Object Repository Object dialog. You can use this dialog to browse for an existing object or to add a new object to the repository.)
     3. Enter the class within the .NET assembly that defines the posting logic in the Record Post Class field.
     4. Enter any applicable Comments in the field provided. 
9. Click the Import Source Columns tab.
10. Create an Import Source Columns sub-type record for each column that is contained in the import file.
    
    • Create one entry for each column in the import file, even if you do not intend to import a particular column.
    • The Import Source Columns sub-type records must appear in the same order that the columns appear within the import file.
    • Follow these steps to create an Import Source Columns sub-type record: 
      1. Open a new sub-type record.
      2. Enter the name of the column in the Column Name field.
      3. In the Default Mapping Field, enter the name of the field in the Mapping Entity to which data from this column will be imported. Leave this field blank if you do not want to import data from this column into Aptify.
      4. Select the Show In Display check box if you want this column's data to appear in the Import Run Details record's Display Data field. If you select the check box for multiple columns, the columns' data is separated by dashes in the Display Data field.
      5. Enter a Description, if desired.
      6. If the column is a field that should not be imported, uncheck the Import Default. For example, if a column is an ID field.
      7. If you want to allow a user to define the default matching criteria for a particular column in the import source file when running the Generic Import wizard, check the Match Column option.  
         
          
      8. Click OK to save and close the sub-type record.  
         
         
          
11. Save and close the Import Sources record.

Matching and Posting Import Results

This topic describes how to match and post import results.

Follow these steps:

1. Open an Import Runs record whose details have not yet been matched or posted.
   
   • After running the Generic Import wizard, the Import Runs record that was created by the wizard opens automatically.
2. Click the Import Run Details tab.
   
   • This tab displays all of the rows imported from the import file. 
     
     
3. Review the results of the import process and resolve any import failures that may have occurred (that is rows whose Status is set to Import Failure).
   
   • If you place the cursor in the row's Matching Record column, an ellipsis (...) button appears (the button is circled in the figure below). Click this button (or simply double-click within the row) to open the corresponding Import Run Details record to view more information about a particular row. See About the Import Run Details Form for details on the form.
     
      
   • If a match failure occurred or if you want to modify an unintended partial or absolute match, open the row's Import Run Details record and modify the record's contents as necessary.
   • Modify the Import Run Details record as necessary to resolve the issue. Errors related to a failure appear on the Import Run Details record's Comments tab.
   • When finished, change the record's Status from Import Failure to Imported (the Status field is on the General tab). 
4. Click the Match button to display a dialog that contains information about the matching behavior.
5. Click OK to begin the matching process.
6. Click OK when prompted that the matching process is complete.
7. Review the results of the matching process and resolve any matching failures that may have occurred. You can also modify any rows that unintentionally matched with an existing record.
   
   • You can filter the rows that appear in the list using the Show Records radio buttons. For example, to display all records whose Status is Partial, select the Partial Matches option, as illustrated in the figure above.
     • With Aptify, absolute matches occur if the Mapped Entity's name field has the Unique option selected in its Entity Fields record and the imported row's name matches an existing record's name.
     • With Aptify, partial matches occur if the imported row's name field matches the name of an existing record in the Mapped Entity and the Mapped Entity's name field does not have the Unique option selected in its Entity Fields record. 
   • Review any partial matches. If the partial match is an actual match, change the row's Status to Absolute to prevent a new duplicate record from being created.
   • If a match failure occurred or if you want to modify an unintended partial or absolute match, open the row's Import Run Details record and modify the record's contents as necessary. Errors related to a match failure appear on the Import Run Details record's Comments tab.   
8. Click Match to rerun the matching process if you modified any rows.
9. After you have completed your final review of the matching results, click the Post button to display a dialog that contains information about the posting behavior.
10. Click OK to begin the posting process.
    
    • The posting process completes the import.
    • For records that are absolute matches, the posting process updates the existing, matching record with information from the import row. Therefore, note that the posting process may overwrite existing data if a field's value in the import row does not exactly match the field's current value in the record.
    • The system creates new records for records that are Partial or Unmatched. The system uses the Record Template you may have specified in the Import Sources record to populate any fields that do not have a corresponding column in the import file. 
11. Click OK when prompted that the posting process is complete.
12. Review the results of the posting process and resolve any matching failures that may have occurred.
    
    • You can display the records that failed to post by selecting the Failure Only option in the Show Records area.
    • If a row failed to post, update the Import Run Details record using the following steps:
      <oltype="a">
    • Open its Import Run Details record.
    • Click the Comments tab in the Import Run Details record to review the error. 
      
       
    • Based on the error message, modify the record as necessary.
    • Click the General tab and change the Status from Post Failure to the appropriate option (such as Unmatched). Note that if you can also leave the Status set to Post Failure. The posting process also attempts to repost any row that previously failed.  
      
       
    • Save and close the Import Run Details record to return to the Import Runs record.
       

• Click Post to rerun the posting process if you modified any rows.
• Resolve any additional failures as needed.
  
  • When all rows have been successfully posted, the system grays out the Match and Post buttons on the Import Runs record (since no additional action is required for this import run).
  • The system automatically adds the date and time to the End Date field on the Import Runs record's General tab when all records have been posted. 
• Save and close the Import Runs record.
  
  • If you do not save and close your record, a prompt will appear indicating that the End Date has been changed. Click Yes to save the End Date to the record.

Running the Generic Import Wizard

Note About Access and Excel Imports

When using the Generic Import wizard with the Aptify Desktop interface to import a Microsoft Access/Excel 2013 or 2016 file, the import may fail if running the 64-bit version of Microsoft Office 2013 or 2016. The error that is generated is: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The import fails because the user's computer has the 64-bit version of Access/Excel 2013 or 2016 installed, while the Aptify shell requires the 32-bit version. 

The current workaround for this issue is to install the 32-bit version of the Access Database Engine Redistributable on the user's computer. You can download the redistributable here:

https://communitybrands.thruinc.net/Publishing/Link.aspx?LinkID=0TIRUUHFR7ACL . Once the Access Database Engine Redistributable is installed, the user's computer must be restarted. 

After you have created or identified a suitable Import Source for your import file (see Creating Import Sources), you can run the Generic Import wizard.

Follow these steps:

1. Select the Import Runs service.
2. Click the Generic Import Wizard icon in the toolbar to launch the wizard.
   
   • This icon is also available in the view toolbar of Import Runs list views.  
     
     
3. Enter the applicable source record in the Import Source field.
4. Select the Customize Field Mapping for This Import Run option to review or modify the Import Source Columns for this run.  
   
    
5. Click Next to continue.
6. Modify the field mappings as necessary, and then click Next to continue.
   
   • This step is not applicable if you did not check the Customize Field Mapping for This Import Run box.
   • You cannot modify the Source Column fields in this dialog. 
     
      
   • To specify a different entity field for the Mapping Field, place your cursor in the appropriate row to enable a drop-down list that contains all of the fields in the entity. Select a different field as necessary.  
     
      
   • If the data in a source column corresponds to a linked field in the Mapping Entity, you can select the Lookup check box. When selected, Aptify performs a lookup in the specified entity to find the record ID that matches the text in the source column.

     • For example, if the data in the Culture source column is text (such as Spanish) and this column maps to a CultureID linked field, then Aptify can lookup the ID for the Cultures record whose name is Spanish and return the appropriate ID. This ID can then populate the CultureID field in the new record created from the import data.

       The Lookup feature applies only to entities whose Name field is Unique (in other words, the Unique option is selected on that Entity Fields record that also has the Is Name option selected). This feature does not support entities whose records can share the same name.

   • You can also modify each row's details by opening the Import Mappings sub-type record. Click within a Description field to display an ellipsis (...) button and then click that button to open the form.  
     
     
7. Specify the import file details in the fields provided and click Next.
   
   • The fields vary depending on the import source's import source type. These fields correspond to the items on the Import Source Type's Input Properties tab.
   • For tab-delimited text files, identify the name and location of the Import File.
   • For Excel files, identify the name and location of the Import File and the name of the Excel file's Worksheet that contains the import data.
   • For Access files, identify the name and location of the Import File and the name of the Table that contains the import data.
   • Select the Has Header option if the first row of the import file contains column headings. When selected, the Generic Import wizard ignores the first row in the import file.  
8. Click Finish to begin the import process.
9. Click OK when prompted that the file was imported successful.
   
   • This opens the Import Runs record that the wizard created. Proceed to Matching and Posting Import Results.

Related Solution Articles

• Aptify Generic Import Wizard fails in Aptify Web
• Generic Import Wizard Run Completes with no data
• Aptify Generic import wizard and Aptify Web v2

Generic Import Wizard Fails in Aptify Web

Issue - The Aptify Generic Import wizard does not work in Aptify Web. Attachments may also not properly upload to records.

You may receive one or more of the following errors:

Error: 1) Exception Information

 *********************************************
Exception Type: System.IndexOutOfRangeException
 Message: Cannot find table 0.
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Data.DataTable get_Item(Int32)
HelpLink: NULL
Source: System.Data

StackTrace Information
*********************************************
 at System.Data.DataTableCollection.get_Item(Int32 index)
 at Aptify.Framework.GenericImport.Components.ImportExcelFileReader.a()
 at Aptify.Framework.GenericImport.Components.ImportExcelFileReader.OpenSource()
 at Aptify.Framework.GenericImport.ImportEngine.Import(Int64 ImportRunID)
 at Aptify.Framework.GenericImport.GenericImportWizard.d()
 at Aptify.Framework.GenericImport.GenericImportWizard.c(Boolean& )

Alternatively, you may receive the following:

Error: 1) Exception Information
The description for Event ID 0 from source Aptify.ExceptionManagerPublishedException cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Access to the path 'C:\inetpub\AptifyWeb\AptifyServicesAPI\Uploads\adc80221-3d0c-4917-bee0-f21a3b05b024___Filetoupload.xls' is denied.

Additionally, in Web, you may receive a popup error message "Blank value is not ok for Source."

You may also receive an error in the Windows Event Viewer:

Access to the path 'C:\inetpub\wwwroot\AptifyServicesAPI\Uploads\' is denied

 

A separate error is related to the server's version of the Microsoft Access Database engine:

 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Aptify Web requires the 64-bit version to be installed on the Web server, while the Desktop client requires the 32-bit version. 

Solution:

1. Ensure the app pool user has read and write access to the server's uploads folder.
2. Verify the Web folder's Web.config file points to the correct uploads folder. Note that our best practice is to place the upload folder outside the primary IIS site directory structure.
   
   <add key="Aptify.Framework.UploadsPath" value="C:\Aptify\Uploads\" />
   
   
3. Verify the AptifyServicesAPI Web.config file points to the correct Uploads folder:
   
   add key="Aptify.Framework.UploadsPath" value="C:\Aptify\Uploads\" />
   
   
4. Ensure that you have installed the AccessDatabaseEngine.exe (V:12.0.4518.1014 )
   • Depending on the server you need the 2010 or 2016 32-bit version or 64-bit version.
   • 2010: https://communitybrands.thruinc.net/Publishing/Link.aspx?LinkID=0TIRUUHFR7ACL
   • 2016: https://www.microsoft.com/en-us/download/details.aspx?id=54920
5. Check the system environment variable to ensure that it is pointing to correct folder.  To do this, log on as an Administrator to the web server hosting the services-based application. From the Start Menu, type Edit the System Environment Variables and launch the program. Next, click the Environment Variables button. Under the System variables section, verify the UploadFolderPath is not set. If it is not set, click New, and set its value to the same location you specified for Aptify.Framework.UploadsPath in the services Web.config.  Otherwise, edit the existing entry to have the correct value.
   
   The value must end in a trailing slash.
   
   
6. Restart IIS and retest the wizard.

Generic Import Wizard Run Completes with no data

Issue

If you run the generic import wizard and it completes but no actual data was imported (the import run created but the import run detail tab is empty) . If you get the error as posted in the Error section below and you have confirmed that you have the Microsoft Access Database Engine installed, then you may have a x64 bit machine but the x32 bit version of the Microsoft Access Database Engine still needs to be installed. Running the AccessDatabaseEngine.exe results message below

Solution 

Confirm that the below as been done

https://aptify.freshdesk.com/a/solutions/articles/6000165275 

https://aptify.freshdesk.com/a/solutions/articles/6000148463 

if still an issue then 

Get the AccessDatabaseEngine 

Save on a computer

Using CMD run the following command 

Location of exe file>AccessDatabaseEngine /passive

Location of exe file>AccessDatabaseEngine_X64.exe /passive

Example - if the file is stored in person123 then run the below

C:\Users\person123>AccessDatabaseEngine_X64.exe /passive

C:\Users\person123>AccessDatabaseEngine /passive

Error

The description for Event ID 0 from source Aptify.ExceptionManagerPublishedException cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 


General Information 

*********************************************

Additional Info:

ExceptionManager.MachineName: APARDP-TEST
ExceptionManager.TimeStamp: 7/22/2020 1:13:58 PM
ExceptionManager.FullName: AptifyExceptionManagement, Version=4.0.0.0, Culture=neutral, PublicKeyToken=f3fa0ecabf9514d9
ExceptionManager.AppDomainName: Aptify Shell.exe
ExceptionManager.ThreadIdentity: 
ExceptionManager.WindowsIdentity: APANYC\greg.mack

1) Exception Information
*********************************************
Exception Type: System.IndexOutOfRangeException
Message: Cannot find table 0.
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Data.DataTable get_Item(Int32)
HelpLink: NULL
Source: System.Data
HResult: -2146233080

StackTrace Information
*********************************************
   at System.Data.DataTableCollection.get_Item(Int32 index)
   at Aptify.Framework.GenericImport.Components.ImportExcelFileReader.a()
   at Aptify.Framework.GenericImport.Components.ImportExcelFileReader.OpenSource()
   at Aptify.Framework.GenericImport.ImportEngine.Import(Int64 ImportRunID)

the message resource is present but the message is not found in the string/message table

 Aptify Generic import wizard and Aptify Web v2

Issue - Aptify generic import wizard does not work on Aptify web and you are running into the error below as well as this error for all users.

Error: 1) Exception Information

The description for Event ID 0 from source Aptify.ExceptionManagerPublishedException cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Access to the path 'C:\inetpub\AptifyWeb\AptifyServicesAPI\Uploads\adc80221-3d0c-4917-bee0-f21a3b05b024___Filetoupload.xls' is denied.

StackTrace Information

*********************************************

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode)

at System.Web.HttpPostedFile.SaveAs(String filename)

at Aptify.Services.Framework.Controllers.AttachmentUploadController.uploadFile()

Properties

*********************************************

Type: System.UnauthorizedAccessException

Data: System.Collections.ListDictionaryInternal

TargetSite: Void WinIOError(Int32, System.String)

HelpLink: NULL

Source: mscorlib

HResult: -2147024891

the message resource is present but the message is not found in the string/message table

Solution :-  On the web server ensure that the users have access to the read and write uploads folder in the location where you have Aptify web installed on