Importing Records from a File

Overview
You can import new or updated data from an Excel or CSV spreadsheet into an existing app. 
Importing Records to an App

  1. Prepare the Excel or CSV File for import. See Supported Formats for Importing.
  2. Within an app, click the Ellipses Options icon on the right.
  3. Select Import from File.
  4. Click Browse to select your file. 
  5. Select if your First row is header or First row is data.
  6. When you select an option the Preview section displays a sample of the first five rows of data so that you can check the result.
    • If the imported data is garbled, change the character encoding.
    • If the imported columns are misaligned, change the delimiter.
  7. Under Column in File, select the column from the imported file that matches the fields in the app. For each field on the left, use the drop-down to select a column, or select None to not import data into that field.
    • If a field in the app is not specified a column from the file, it will contain its set default value when a record is created. Please note that a radio button field initially has the first item set as its default value. 
  8. To update existing records in the app with the imported data, select Key to Bulk Update on the right of the desired field to use as the key identifier. Text, Number, Date, Date and Time, and Link fields can also be used as keys. To use a field other than Record number as the key field, the field in the app settings must be set to "Prohibit duplicate values."
    • When the key field is the Record number:
      • If the record number in the imported file matches the value in the Record number field in the app, the record is updated. 
      • If the record number field in the imported file is empty, the record is added to the app as a new record.
      • If a record number exists in the imported file and it does not match any record number in the app, an error occurs.
    • When the key field is one of the other fields:
      • If the values in the key field match, the record is updated.
      • If the values in the key field do not match, the record is added as a new record.
      • If you select None and you also select a key field, existing values are not overwritten when a record is updated.
  9. Click import.PNG.
  10. Click OK
  11. The top of the page shows a message stating either the file import was successful or unsuccessful. 
  12. See Checking Import Results.

If a large number of records was created accidentally, you can delete the records in bulk. See Bulk Delete.

What is Bulk Updating?

Records can be updated in bulk by exporting the data to a file, and importing the edited file with updated information. To check for data types capable of being both exported and imported, see Data Types that can be Imported/Exported.

How to Bulk Update:

  1. Export the records you'd like to update. See Exporting Records to a File.
  2. Edit the File to contain the updated content. See Editing a File for Bulk Updating
  3. Import the updated file. See Importing Records to an App.

Creating a File for Bulk Updating 

Excel workbooks can be created with Microsoft Excel, and CSV files can be created by a spreadsheet software, such as Excel or Open Office Calc. Before creating a file, note that each file format has its own limitations:

Excel

  • Up to 1,000 records can be created
  • The maximum file size for import is 1 MB, and the file extension must be ".xlsx"

CSV

  • Up to 100,000 records can be created
  • The maximum file size for import is 100 MB

How to Create a File with Excel

This example guide uses Excel 2013 to create a file for creating records in bulk.

  1. In an Excel worksheet, enter the field names in the first row of cells, and enter the values for each field in the subsequent rows. One row should correspond to one record. For details on the supported formats for each field type, see Supported Formats for Importing.
  2. Select the "Files" tab and click "Save As".
  3. Select "Excel Workbook" or "CSV (Comma delimited)" for "Save file as type".
  4. Specify the file name and the destination folder and click "Save". 

Editing a File for Bulk Updating

This section explains how to edit a file to be used to bulk update records. Once you edit the file, see Importing Records to an App for more information on Bulk Updating. 

Please note, when you use CSV files in updating records, there are the following limitations:

  • Up to 100,000 records can be created or updated at once.
  • The maximum file size for import is 100 MB.

How to Edit a File with Excel

This example guide uses Excel 2013 to edit a file for updating records in bulk.

  1. Export the records that are to be updated to a CSV file. See Exporting Records to a File.
  2. Open the exported CSV file in Excel.
  3. Edit the file to update the record data. New records may be added by adding rows in Excel and entering record data, as long as the file is still within CSV limitations. For details on the supported formats for each field type, see Supported Formats for Importing.
  4. Select the "Files" tab and click "Save".

Please note:

  • Values in a lookup field can only be entered or updated in bulk if every value of the Key Field is unique. In the Key Field setting, select Prohibit duplicate values.
  • When entering characters that are not Japanese or alphanumeric in a CSV file:
    • Use software other than Excel, such as Open Office Calc.
    • Make sure the character encoding is specified as "UTF-8" when saving.
      • The Japanese version of Excel uses Shift JIS as the character encoding for CSV files, which may cause characters that are not Japanese or alphanumeric to become garbled.
  • Values in fields that are included in tables can only be specified when a new record is created; they cannot be specified when updating an existing record.

Common Bulk Process Errors and Solutions

If a bulk register or bulk update fails, the Record List screen will display an error message. Click details to check the details of the error.

Error Message / Issue Solution
Message: "GAIA_IL16: The field value "(field name)" at line # is invalid."
  • If the field is required, make sure that the indicated cell contains a proper value.
  • If the field prohibits duplicate values, make sure that the indicated cell contains a unique value.
  • If the field is a radio button or drop-down, make sure that the indicated cell contains one of the field's options.
  • If the field is not to be updated, make sure that the indicated cell contains a value that satisfies the field's settings. (e.g. a required field left empty)
Message: "GAIA_IL17: The field format "(field name)" at line # is invalid." Modify the format of entries in the indicated field according to Supported Formats for Importing.
Message: "GAIA_IL48: The value "#" in line # of the field "(field name)" does not exist in the options for Category." The value must be one of the options that exist in the Category field.

Message: GAIA_II03: I am trying to load data into an application table. 

When reading data into the table of an application, the first column of data needs a column indicating the start row of the record. If you do not import data into the table of the application, do not specify the column of the file to be read in the field in the application table.

  • When reading the table:
    • Add the first column to the file and make it "*" or empty. The rows from "*" to the next "*" are registered as a table of one record. The value of the line in which "*" is entered is reflected in the first line of the table.
    • Examples of files to be read: The second to third lines, the fourth to fifth lines, and the sixth to eighth lines are registered as one record.
  • If you do not read the table:
    • Please delete the corresponding column in the table in the file to be read or remove the fields in the table from the field to be read.
Message: "GAIA_II02: The XX line of the file contains values ​​from more than one table." Correct file so it does no read the values of multiple subtables.
Message: "GAIA_RE 10: Key to bulk update of record with record number XX is duplicated with other record." Correct file so the value of the field set to Key to Bulk Update does not overlap.
Message: GAIA_IL 19: Failed to lookup (get) with value of field '(field name)' on XX line of the file. In the referenced application, the corresponding record can not be found. The value read into the lookup field does not exist in the application to which it refers. Register the value of the cell specified in the error message to the reference application.
Issue: An empty record is created. Delete any blank lines in the file.
A blank line is converted into an empty record.
Issue: Cannot perform the line break action for values in the Rich text field. In the import file, insert <br> in places that require a line break. 
Message: "Tables cannot be updated, and can only be added as a new record." When existing records are updated, values in fields that are included in tables cannot be updated. Clear the selection for "Key to Bulk Update" and create a new record, or do not import fields that already exist in a table.

Please note:

  • An error message is also displayed when a value violates a field's limit. For further details on each field type's limit, see Field Type Descriptions.

Canceling a Bulk Import / Update

The process of creating or updating records in bulk can only be canceled by the user who initiated the process before the file finishes importing. When a process is cancelled, the app data is restored to the state before processing.

How to Cancel a Bulk Import / Update:

  1. In the app, open the View screen and click Imported Files. The Imported Files screen can also be reached by clicking gear_wheel.PNG on the upper right side of the screen, and then clicking Imported Files.
  2. Click Cancel for the file you wish to cancel importing.

Please note:

  • Once an import is complete, Cancel will not be displayed and the file will no longer be cancelable.
  • The process to cancel the import can take anywhere from a few seconds to a few minutes.
  • Once the cancellation process is complete, the following notification will be sent:
    • "The import has been canceled. The app data has been restored to the state prior to the import."

 

Related Topics
Supported Formats for Importing

Was this article helpful?
0 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.