Overview
As an alternative to Excel Integration, data contributors can use Syniti Drive and Replicate Preview to bulk-import records into a grid container's dataset in Cloud Construct. This approach leverages Excel file-based export and import replications to download an exported spreadsheet, enter data, and then import that data from the spreadsheet into the corresponding container’s dataset.
The data import process consists of three stages:
Export the container's existing dataset as a spreadsheet (.xlsx) using a file export replication. This exported file serves as a data template.
Import the updated file back into the dataset using a file import replication configured in Append mode. The primary use case for this approach is adding new records and not updating existing ones.
Note
Unlike Excel Integration, importing data via Replicate Preview has no record count limit. This method can be used for containers with any number of records.
Cloud Construct does not support the Lock/Unlock Records functionality available in Excel Integration.
Important Considerations
Review the following considerations before importing data.
Consideration | Explanation |
|---|---|
Supported Column Input Types | All column input types, except List Box and Buttons that are not bound to the grid’s dataset, are supported. Refer to the Column Input Types section for more information on available column input types in Cloud Construct. |
No Validations During Import | Data is imported via Replicate Preview. During the import process, Cloud Construct does not perform field-level checks, input validations, or UI-driven business logic. Only OnSave Events execute after the import completes. Any additional validations or events must be run separately after the import. The import may fail if the records do not comply with the table's DDL or database constraints, for example, exceeding a column's character limit, violating a NOT NULL constraint, or a foreign key violation. |
Blank Cells | Blank cells in the import file are loaded into the dataset as-is (as NULL or empty values). |
Binding and Drill Criteria Are Not Honored | When importing data into container datasets via Replicate Preview, Cloud Construct does not honor binding and drill criteria. It is recommended to exclude columns with binding and drill criteria from the export/import process. |
Columns Controlled by Content Control Components | It is strongly recommended to exclude columns from the import file that are controlled by a Conditional Configuration Dataset (CCD), Page Control View (PCV), or User Control View (UCV). The restrictions defined by these controls are not enforced during import, and including such columns may produce inconsistent or unintended data. |
Translations | Translations must be defined in Cloud Construct prior to importing data for column header names to display correctly. The translated column name heading row in the export file reflects the technical column name from the database. If no translation exists for a column, the heading row displays the technical column name. |
Understanding Field Metadata | Before adding records to the export file, review the grid container's field metadata, such as data type, field length, required fields, and foreign key relationships, to avoid constraint errors during import. For example:
|
Set Up a Dedicated Drive and Workspace | It is recommended to create a dedicated drive and a workspace in Replicate Preview specifically for file export and import replications related to spreadsheets used in Cloud Construct. This keeps export templates and import files organized and separates them from other replication workflows. Exported files can be reused as templates for subsequent imports. |
Append Mode is Required | File import replication in Replicate Preview executes in either Truncate or Append mode. Always configure the file import replication in Append mode. Truncate mode removes all existing records in the dataset before inserting the records from the file. |
Working with Spreadsheets
Prerequisites
Create the required table in the database (the datastore used in the WebApp) such that you can export it to an Excel spreadsheet (.xlsx) to use as a template for importing data.
Create a dedicated drive or folder in Syniti Drive and a workspace in Replicate Preview. Refer to Syniti Drive and Creating a Workspace for more information.
Export the Container Dataset as a Template
Use a file export replication in Replicate Preview to export the container's existing dataset. The exported file reflects the structure of the dataset and serves as a template for adding new records.
In Replicate Preview, access or create the dedicated workspace and create a target datastore of Syniti Drive type and select the required drive.
Create a source datastore of SKP Catalog type using the datastore details of the construct page associated with the container’s dataset.
Create a new file export replication using the dataset (table) that corresponds to the container.
Run the replication to export the file to the configured file path (export folder) in Syniti Drive.
Download the exported file from Syniti Drive to your local machine.
Note
If the container’s dataset has no existing records, the exported file still contains the column header row, which defines the structure for adding new records.
Translations must exist in Cloud Construct prior to running the export for column headers to appear in the translated language. If no translation exists, the technical column name is used in the header row.
Add Records to the Exported File
Open the exported file and add new records in the rows below the last record or the header row.
Follow these guidelines when entering data:
Do not modify the header row: The column names in the header row are used to map data during import.
Observe data type and length constraints: Refer to the container's field metadata to understand the expected format and maximum length for each column. For example:
varchar(50)columns must not exceed 50 characters.decimal(10,4)columns must use the correct number of whole digits and decimal places.bitcolumns accept values of1(true/checked) or0(false/unchecked).
Leave blank cells only where null values are acceptable: Blank cells are imported as-is and are not replaced by default values.
Do not enter values in columns controlled by CCDs, PCVs, or UCVs: Remove or leave blank these columns in the file before importing.
Save the file with a new name when data entry is complete.
Import the Updated File
Upload the updated file to the configured file path (import folder) in the same drive.
In Replicate Preview, access the dedicated workspace and create a target datastore of SKP Catalog type and select the same datastore used as a source during the file export process.
Create a source datastore of Syniti Drive type using the same drive used as a target during the file export process.
Create a new file import replication using the updated file and the corresponding table (dataset) associated with the grid’s container.
Ensure that you use the target table name as defined in the database.
In the replication settings, turn on the Append Mode toggle.
Do not use Truncate mode. Truncate mode deletes all existing records in the dataset table before inserting the records from the file. Use Append mode to add new records while preserving existing data.
Use the field mapping dialog box to verify that the columns in the file map correctly to the dataset table's fields.
Run the import replication.
Note
If the import fails for any records, review the replication log for details. Common failure reasons include:
A column value exceeds the field's character or numeric limit.
A required (NOT NULL) column contains a blank/null value.
A value violates a foreign key constraint.
A duplicate primary key exists in the dataset for a record being appended.
Correct the data in the file and run the import replication again.