Import data

    Import data to reports

    ManageEngine AppCreator allows you to import data from into the reports in your AppCreator application. AppCreator supports importing data from .xls, .xlsx, .xlsm, .csv, .tsv, .ods, .accdb, .mdb, .json and .numbers file formats. The required file can either be stored in your computer or stored in a cloud service (Dropbox, Google Drive, ManageEngine Docs, etc.).

    Check out what's new in the new Import Interface here

    To import data:

    1. Access your AppCreator application. Navigate to the report to which the data has to be imported. Click on the More options button on the right corner and select the Import Data option.

    2. Now choose the file type and required data location.
      • Local storage - Choose a file that is stored in your computer.
      • Paste - Allows you to paste data. Import data by pasting delimiter-separated values. The four supported delimiter are comma(,), tab, semicolon(;), space, and pipe(|). Pasting of data up to 5MB is supported.

    3.Import interface

    In the next step the import interface will be shown with data from the selected file. The interface gives a preview of the records that will be created according to the specified mappings of the columns. You can edit the data that is displayed.
    The import interface also offers a variety of smart capabilities that enable you to filter, refine, and cleanse the to-be imported data. Based on the data of the imported file, the format for a certain field will be added. If required, this format can be edited as per your requirement.

    AppCreator offers a variety of smart capabilities that enable you to filter, refine, and cleanse the to-be imported data.

    4.First row contains column headers

    AppCreator will automatically try to detect if the first row of your imported file is comprised of headers or not. When the First row contains column headers option is checked, the columns in the field mappings section will be named after those column headers. If the option is unchecked then the first row values will be considered as actual data values.

    • Click on the Table Settings button adjacent to the required table.

    • Check the First row contains column headers option.

    5. Map columns

    In the Map columns section you can map fields from the imported file to those present in AppCreator. In the column mapping pane, the left side shows the columns of the imported file and the right side shows the fields in your AppCreator. AppCreator automatically tries to map the fields. You can remap the columns of the imported file to any field based on your requirement. To map a column, choose the field from the drop down list.

    1. Click on the Map columns button.

    2. To map a column choose the field from the drop down list.

    3. Mandatory fields: Make sure are mapped and you do not leave any mandatory fields blank in the import file. Do note that not mapping mandatory fields will lead to error while importing the file.
    4. Lookup field handling: The lookup fields can be imported based on both ID as well as value. Whether the import is based on Value or ID will be denoted in adjacent to the field. 

      ​​

    5. Conflict resolution: AppCreator offers conflict resolution by highlighting warnings that may arise during field mapping. When conflict warning is displayed you can click on remap button to rectify the mistakes.
      • Decimal warning: When appropriate number of decimals are not present.
      • Field warning/data type mismatch- When incompatible data type is set for the column that is not appropriate for the data.

        ​​

      • Lookup-not in parent warning: Some rows may fail during import when their values are not present in the lookup parent field. To resolve it is recommended to set empty values on import error.
      • Lookup-duplicate values: When parent lookup field either contains duplicate values or some values are not present in parent lookup fields.

    6. Import settings

    The import mode option found under the Settings allows you to choose how the data from the file should be imported to AppCreator. There are two types of import that AppCreator supports: Append rows and Update rows.

    1. Append rows - When you want to create new records with your uploaded data. For example, consider an organization named Zylker which stores details of its employees in ManageEngine AppCreator. When this organization needs to add details of the newly joined employees to the existing ManageEngine AppCreator report containing a list of all its employees they can use the Append rows option. This will add new records to the Employee details report.

    2. Update rows - When you want to update your existing records with the imported data. For example, consider an organization named Zylker which stores details of its employees in ManageEngine AppCreator. The employees may have changed their addresses or phone numbers. When Zylker wants to update the Employee details report with new data they can use the Update rows option. Now based on the Employee ID field, which acts as unique field, the new addresses or phone numbers will be updated. 

      To update your existing file, you'll need to pick an unique field of the report based on which the new data has to be updated. AppCreator will then compare the records in your existing file with the rows in your imported file, and update the values in the existing AppCreator report according to the contents of the imported file, even if the field for those matching records in the file is blank.

      • Only field with unique values can be used to compare values and update records. Make sure the 'no duplicate values' option is checked for that field in the form properties
      • Under the field name dropdown the fields with unique value will be listed. Select the required field and map the required column from the file.
      • The 'Append rows when values not in field' option will append new rows when the values are not in the unique field.
      Note: 
      • Updating existing records overwrites data in the target file and cannot be undone. It is recommended to make a backup copy of the target file to which you’re importing data.
      • It is important to remember that values in the existing AppCreator report will be updated according to the contents of the imported file, even if the field for those matching records in the file is blank.

    7. Define what's to happen on import error

    There is a possibility that errors may occur while importing data. So ManageEngine AppCreator allows you to choose how to proceed in such instances. Under the On import error section you choose the action to be done when error occurs while the data is being imported. You can choose to either Skip the corresponding rows or Set empty value for the column.

    1. Click the Settings button displayed near the top-left corner of the interface. The Settings pane will slide in from the right.
    2. Under the On import error section two options will be displayed.
      • Skip Corresponding Rows - If there is any conflict found in any of the row values, ManageEngine AppCreator will skip the corresponding rows.
      • Set Empty Values for the column - If there is any conflict found in any of the column values, ManageEngine AppCreator will set empty(NULL) values for the corresponding columns.
      • Do not import records - If there is any conflict found in any of the values then ManageEngine AppCreator will not import the data. This option is supported only when the number of records to be imported is less than 3000.

    8. Set the date format

    Setting the data format's paramount as this will tell ManageEngine AppCreator how to interpret the date values in your file. 

    1. Click the Settings button displayed near the top-right corner of the interface. The Settings pane will slide in from the right.
    2. Select the required value from the dropdown below Date Format.

    The application has capabilities to recognize date based on locale. A date such as dd.mm.yyyy may not be recognized as date if locale is US and so on import also it will be treated as text. Henceforth, all possible date and time formats will be recognized. If date format is different across the columns then they can be refined into a common format by Standardize Date and Time option under Refine data section

    9. Cleansing data

    AppCreator offers you to cleanse the to-be imported data by offering:

    Sort data

    Based on a column, you can sort the data in a table in the ascending or descending order. The sorting order you apply will be carried in along with the imported data. This is to let you view data in the required order. Sorting data will be help before you perform the data refining operations.

    1. Click the down arrow of the required column.
    2. In the drop down list select the required sorting order.

    Filter data

    The filter option allows you to narrow down data. Once filter is applied only the filtered data will be imported in to the to-be created imported data.

    1. Click on Filter option.

    2. Set the required criteria. The criteria can contain up to 20 sub-criteria. Now click Filter button.

    Find and replace

    The find and replace allows you to find and replace data.

    1. Click on the Find and replace button
    2. Enter the criteria for the find and replace operation.

    10. Refine data

    The refine data option allows to you the following smart data refining operations. The available options are:

    • Fix Inconsistent Spellings - This enables you in identifying spelling inconsistencies and typographical errors. It'll display Clusters that are nearly similar in terms of number of characters, phonetics, and character repetition. You can modify the values in a cluster with the suggested value, or with a value of your choice.
    • Fix Missing Values - This enables you in finding if a column contains empty cells, and fix them in any of the following ways:
      • Fill empty cells with the value present in their preceding row
      • Fill empty cells with a value of your choice
      • Fill empty cells with the most-occurring value in that column
      • Remove the rows that do not contain a value in that column
    • Split Column - This enables you to split the data in a column in to two. You'll need to define what delimiter (such as whitespace, comma, or hyphen) is to be used to split the data.
    • Merge Columns - This enables you to merge the data in two or more columns, in the order they are selected. Optionally, you can define if a separator (such as whitespace, comma, or hyphen) is to be added between the values or the to-be merged columns. You'll get to preview the merged value.
    • Explore and Edit - This shows the frequency distribution of values in a column, which can help in deciding if the data is to be modified by find and replace or use Fix Inconsistent Spellings operation.
    • Standardize Date and Time - This enables you to standardize the Date/Date-Time columns to a common format. With this multiple date formats can be uploaded and standardized. An option to choose the 'Pivot year' will also be available. This pivot year is helpful in the case where the year value has only two digits. In such cases the two-digit year value xx smaller than the pivot year will be expanded into 20xx, and year values larger and equal than the pivot year will be expanded into 19xx.

      Note: 
      • If a column is mapped to Date/Date-time field, Standardise Date/Date-Time warning will begiven if the date format of the importing values doesn't match with the date format set for the application.
      • If Standardise Date warning is ignored by the user, then the date values will be automatically standardised based on the date order set in Settings. Ignoring Standardise Date will cause import errors only when the importing data has multiple date formats across the columns. Each column has to be standardised individually with an appropriate date order.
    • Standardize Phone Numbers - This enables you to standardize the phone numbers as per the international E. 164 notation. You'll be able to do one of the following:
      • Prepend a particular country's international calling code to all the phone numbers in the selected column. For example, if you select United States as the country, +1 will be prepended to all the phone numbers in the selected column. Similarly, +61 will be prepended to phone numbers associated with Australia.
      • Prepend the international calling code of the country present in another column of the same sheet. The country data must either be country names, or their standard two-letter or three-letter ISO 3166 codes. For example, if a row were to contain "222-555-019" in the phone number column and "United States" in the country column, it will be changed to "+12225550191".

      Note:  If a column is mapped to a Phone Number field, then a warning to Standardise Phone Number will be provided to add appropriate country code to the values. The values without country code will result in record import failure.
    • Fix duplicate rows - This operation enables you to find and remove duplicate records across one or more columns in your sheet or table. In the remaining duplicate records to be deleted, you can include/exclude rows. When you exclude a row, that row will be greyed out and retained, whereas the remaining duplicate rows will be deleted. The following GIF shows how to include/exclude record looks like:
      • Exact match looks for rows that contain the same (case-sensitive) values in the selected column
      • Similar match looks for rows that contain values in the selected column that are similar barring case-sensitivity, punctuation, ordering of words, and white spaces
      • Fuzzy match looks for rows that contain values in the selected column that are similar, barring typos, phonetic similarities, and transposition errors (where characters are swapped such as "ei" vs "ie" in the word field)
      • For text-based and phone number columns, you can choose to look for duplicates using any of the three matches: exact, similar and fuzzy.
      • For columns mapped to number, date, time and date-time field types, all three matches will display the same set of duplicate clusters.
      • Upon selecting the required cluster of duplicates, the corresponding rows will be displayed on the left. You can set one of the rows as the master record, which will be highlighted in the UI.
      • Upon clicking Remove Duplicates button, all rows except the master record of each cluster and those rows that you exclude will be deleted.​

    To refine data:

    1. Click on the Refine data button.
    2. In the Refine data pane choose the required operation from the drop down list.

    3. Now choose the required column and perform the data refining operation.

    11. Smart suggestions with Zia

    Under the Smart suggestions section Zia(ManageEngine's Intelligent Assistant) will provide suggestions to you based on problems that may be present in the data that has been uploaded from the file. Zia provides suggestions to a handful of problems which are as follows:

    • Duplicate rows at table level
    • Inconsistencies in data
    • Multiple data types
    • Missing values
    • Phone number without standardization
    • Name and Address without standardization
    • Date/Date-time/Time without standardization
    • Data with un-escaped HTML entities
    • Values enclosed in quotes

    To work with Zia's suggestions:

    1. Click on the Smart suggestions button. In the Smart suggestions pane on the list of suggestions by Zia will be displayed.

    10. Importing data

    Once you've configured your settings and field mappings, you're ready to import the data from the file.

    1. Click the Import button to start importing data into your report. This may take a few seconds to a few minutes depending on the amount of information you're importing.

    2. Once the import process is complete a popup will be displayed with the import summary. Errors if any will be shown.

    3. Click on the Import summary button to view a detailed summary of the success/failure of the records that were imported.

    4. Click on Access the report to view the report.

    What's new in the New Import Interface:

    1. The new interface allows Addition and Updation of the data provided necessary permissions have been granted.
      • Adding and Updating during the import process will be permitted only if the Access, Edit and Import permissions has been granted for the form. The Updation of data also depends on the grant of Modify All permission and the Role hierarchy.
      • Updating data during the import process will not be allowed if the Edit permission for the particular form has not been granted.
      • The import data function will work only if Access permission has been granted for the particular form.
    2. Import of files with any kind of script execution is currently supported only when the number of records to be imported are less than 3000.
    3. By default, the workflows will not be triggered in the new import interface. Instead, they can be enabled from the Import Settings section.
    4. Do not import records option under On Import Error section will be available only when the number of records to be imported are less than 3000.

    Points to remember:

    1. You can import data from the .xls, .xlsx, .xlsm, .csv, .tsv, .ods, .accdb, .mdb, .json and .numbers file formats.
    2. The Name field can either be present as a single column or as a composite field made up of Prefix, First Name, Last Name, and Suffix subfields. AppCreator supports importing data in both scenarios.
    3. Similarly Address can either be a single column or a composite field made up of the Address Line 1, Address Line 2, City/District, State/Province, Postal Code, and Country subfields. AppCreator supports importing data in both scenarios.
    4. If the file has columns containing values such as true/false then AppCreator will automatically associate it with the Decision box field. In case other boolean values are present then they will be associated with Radio field.

    Limitations:

    1. There is no limit on the number of rows that can be imported provided the file size is less than 100 MB.
    2. Importing Subform data is currently not supported.
    3. The new import interface will not available for customer portal users.
    4. Import of files with any kind of script execution is currently supported only when the number of records to be imported are less than 3000.
    5. Do not import records option under On Import Error section will be available only when the number of records to be imported are less than 3000.

    Share this post : FacebookTwitter

    Still can't find what you're looking for?

    Write to us: appcreator-support@manageengine.com