The Import Mapped Excel action allows users to transfer an Excel file directly to an existing database table. During this process, Excel columns are matched with the columns in your application's table to ensure data consistency. This allows for easy data transfer without prior knowledge of the table structure or manual intervention.
Technical Features
Data Mapping: Excel columns can be manually mapped to database columns.
Dynamic Routing: The database table to which data will be imported can be selected through the system.
Update and Add: Data can be updated or added based on primary keys.
Blank and Default Controls: Fields that cannot be left blank and default values can be defined.
Foreign Key Support: Linked data can be controlled through foreign key definitions.
Import Excel Excel Action Application Steps
Add the Import Mapped Excel Action
Log in to the Kuika platform.
Select the project you want to work on from the Apps screen.
Go to the UI Design module.
Add a Button from the Elements section on the left panel.
From the + ADD ACTION menu, add the Export > Import Mapped Excel action according to the desired trigger event (Initial Actions, OnClick, OnBlur, etc.).
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
Click the Upload Excel File option.
Upload the Excel file.
If the file contains multiple pages, select the appropriate page.
Map the Excel columns to the system columns.
Define default values for empty cells.
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.
Use Case: Customer List Transfer
A sales representative wants to transfer the customer list stored in an Excel file to the Customer table in the application.
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.
