Add the Import Mapped Excel Action

1. Log in to the Kuika platform.
2. Select the project you want to work on from the Apps screen.
3. Go to the UI Design module.

4. Add a Button from the Elements section on the left panel.

5. From the + ADD ACTION menu, add the Export > Import Mapped Excel action according to the desired trigger event (Initial Actions, OnClick, OnBlur, etc.).

6. Click on the three dots button on the right side of the action → select the Edit Import Action option.

Action Configuration Steps

1. Database Selection

• Select the target table from the Managed DB field.

2. Primary Key Definition

• Primary Key: If updating, select a unique column (e.g., user_id).

3. Column Mapping

• Alias: The corresponding column name in the Excel file (nickname for easy reading).
• Required: Check this box for columns that cannot be left blank.
• Hide: Disables columns that users should not see or update.
• Foreign Key: If the column is related to another table, logical matching can be performed.

4. Preview

• Click the Preview button at the top right → click the button to launch the Excel upload modal screen.

Uploading and Mapping the Excel File

1. Click the Upload Excel File option.
2. Upload the Excel file.

3. If the file contains multiple pages, select the appropriate page.
4. Map the Excel columns to the system columns.
5. Define default values for empty cells.

6. NEXT button to start importing the data.

Data Import Failure

• If the data has been partially added/updated and some of it has failed, the relevant error messages will be displayed on the screen.

• Ensure that the rows are unique with the primary key you selected.
  
  • If they are not unique, multiple rows will be updated.

Important Notes:

• The Excel file should not contain merged cells or formulas.
• The Excel file format you upload must be Excel 2007 or a newer version.

By following these steps, you can successfully configure the Import Mapped Excel action on the Kuika platform.

A sales representative wants to transfer the customer list stored in an Excel file to the Customer table in the application.

Configuration

• Element: ‘Transfer Customer List’ button
• Trigger: OnClick
• Action: Export → Import Mapped Excel
• Database Table: Customer
• Primary Key: email
• Aliases and mappings:
  
  • Excel column ‘Name’ → first_name
  • Excel column ‘Surname’ → last_name
  • Excel column “Email” → email (Primary Key)
  • Excel column ‘Phone’ → phone_number

• Conditional Column Mapping: You can define different mapping tables based on roles to create custom transfer scenarios for each user.
• Dynamic Defaults: You can define the system date or the username of the logged-in user as the default value for columns left blank in Excel.
• Logging and Error Tracking: Failed transfers can be logged and the user can be notified of the reason for the transfer failure.
• Restricted Permissions: Access to the Excel upload screen can be restricted to users with Admin permissions.

Technical Risks and Controls

• Primary Key Conflict: If there are multiple rows with the same primary key, the system may duplicate them instead of updating them.
• Required Fields Left Blank: Leaving fields marked as Required blank will prevent the transfer.
• Inappropriate File Format: The .xlsx file extension should be used instead of older formats such as .xls.
• Use of Merged Cells or Formulas: Cells containing merged cells or formulas in Excel may cause issues during transfer.
• Data Mismatch: Entering numbers in fields that require text input will generate errors.

Import Mapped Excel simplifies the transfer of Excel-based data to the database on the Kuika platform with a user-friendly interface. With this action, you can quickly import data from external systems, configure mapping settings, and easily identify data inconsistencies. It offers a powerful data management solution with advanced control and error management features.