Import Records to Table

Import Data

The Import feature enables you to import records from an external Excel/CSV file to database table.

How to Use

To enable the feature for a table, select the table and click the Table tab, then enable the Import checkbox.

After generation, an Import button will be displayed in the table list page.

To start importing the external records, click the Import button and an Import dialog box will be displayed. Click the Choose button to select the file for import, and the import process will begin. Click the Close button once the import process is completed to refresh the list page.

Important Notes

Supported import file formats are native Excel spreadsheets (xls/xlsx) and CSV (csv) only.
The first row of the file should contain the field names to be imported. The field names MUST match the corresponding field names in the table. You can manipulate the field names in the Page_Importing server event (see Server Events for Import) if the file contains pure data only.
All data in the file MUST match the data types of the corresponding field in the table. For example, if you are importing into an Integer field, the data is expected to contain an integer value or the import may fail. You can however modify the field values in the Row_Import server event (see Server Events for Import below). Importing lookup fields from display values is NOT supported, but you can also use Row_Import event to support it in your own ways.
If you are importing a lot of records, the import may take a long time to complete. In that case, you can increase the maximum execution time in Advanced Settings (see Advanced Settings for Import below).
For CSV file, the default quote character and delimiter used are " (double quote) and , (comma). You can change in Advanced Settings or by Page_Importing server event.
To ensure an ALL OR NOTHING import, you can enable the Import records by transaction in Advanced Settings. The transaction will be rollbacked if there is any error during the import process.
The default import operation is "Insert only". If you want also to update records if record with the same record key is found, you can disable the Import records by insert only in Advanced Settings. Please make sure that you understand the implication of this change before modifying the setting.
If User Level Security is enabled, by default only the Administrators has the rights to import records. To allow a non-admin user to import records, use the TablePermission_Loaded server event (see Server Events and Client Scripts) to set an user with Import permission, e.g. $this->setCanImport(TRUE).

Server Events for Import

Table Specific -> List Page
Page_Importing	This server event allows you to modify the Import options before the import process begins. Arguments are: $reader - File reader (\PhpOffice\PhpSpreadsheet\Reader\IReader) $options - Array containing the import options (see below) You can return FALSE to skip importing the file. The options (accessed by $options["<value>"]) you can change during this event are: maxExecutionTime - Maximum execution time for import activeSheet - Active spreadsheet for import (default is 0, zero-based) headerRowNumber - Header row number (default is 0, zero-based) headers - Header array (default is empty array) offset - Offset to start import (default is 0, zero-based) limit - Number of records to import (default is 0, which means all records) inputEncoding - Input encoding for data (CSV only) delimiter - Delimiter for data (CSV only) enclosure - Quote character for data (CSV only) Example 1 Import selected records only $options["offset"] = 10; // Skip the first 10th records $options["limit"] = 5; // Import the next 5 records Example 2 The spreadsheet contains pure data only. Supply the header manually. $options["headers"] = ["CategoryName", "Description"]; Example 3 Change input encoding, delimiter and enclosure for CSV file $options["inputEncoding"] = "CP1252"; // Use CP1252 for input encoding $options["delimiter"] = ";"; // Semi-colon for delimiter $options["enclosure"] = "'"; // Single quote for enclosure
Row_Import	This server event is executed before a record is imported. Arguments are: $row - Array of data to be imported $cnt - Current import record count You can return FALSE to skip importing the row. Example 1 Modify data before import $row["Trademark"] = ExecuteScalar("SELECT ID FROM trademarks WHERE Trademark ='" . AdjustSql($row["Trademark"]) . "'"); // Get Trademark ID from trademarks table Example 2 Validate input data if (intval($row["Quantity"]) > 100) // Quantity must be <= 100 return FALSE; // Skip import
Page_Imported	This server event is executed after import is completed. Arguments are: $reader - File reader (\PhpOffice\PhpSpreadsheet\Reader\IReader) $results - Array containing the import results The data available in the results array are (accessed by $results["<value>"]): file - File name of the imported file totalCount - Total records imported successCount - Total records imported successfully failCount - Total records imported unsuccessfully failList - Array containing failed imports and reasons Example Write audit trail for import $msg = "imported " . $results["totalCount"] . " records (successful: " . $results["successCount"] . " / failed: " . $results["failCount"] . ") from " . $results["file"]; // Set up import message WriteAuditTrail("log", DbCurrentDateTime(), ScriptName(), CurrentUserID(), $msg, CurrentUserIP(), "", "", "", ""); // Write audit trail

Table Specific -> List Page

Page_Importing

This server event allows you to modify the Import options before the import process begins. Arguments are:

$reader - File reader (\PhpOffice\PhpSpreadsheet\Reader\IReader)
$options - Array containing the import options (see below)

You can return FALSE to skip importing the file.

The options (accessed by $options["<value>"]) you can change during this event are:
maxExecutionTime - Maximum execution time for import
activeSheet - Active spreadsheet for import (default is 0, zero-based)
headerRowNumber - Header row number (default is 0, zero-based)
headers - Header array (default is empty array)
offset - Offset to start import (default is 0, zero-based)
limit - Number of records to import (default is 0, which means all records)
inputEncoding - Input encoding for data (CSV only)
delimiter - Delimiter for data (CSV only)
enclosure - Quote character for data (CSV only)

Example 1

Import selected records only

$options["offset"] = 10; // Skip the first 10th records
$options["limit"] = 5; // Import the next 5 records

Example 2

The spreadsheet contains pure data only. Supply the header manually.

$options["headers"] = ["CategoryName", "Description"];

Example 3

Change input encoding, delimiter and enclosure for CSV file

$options["inputEncoding"] = "CP1252"; // Use CP1252 for input encoding

$options["delimiter"] = ";"; // Semi-colon for delimiter

$options["enclosure"] = "'"; // Single quote for enclosure

Row_Import

This server event is executed before a record is imported. Arguments are:

$row - Array of data to be imported
$cnt - Current import record count

You can return FALSE to skip importing the row.

Example 1

Modify data before import

$row["Trademark"] = ExecuteScalar("SELECT ID FROM trademarks WHERE Trademark ='" . AdjustSql($row["Trademark"]) . "'"); // Get Trademark ID from trademarks table

Example 2

Validate input data

if (intval($row["Quantity"]) > 100) // Quantity must be <= 100

return FALSE; // Skip import

Page_Imported

This server event is executed after import is completed. Arguments are:

$reader - File reader (\PhpOffice\PhpSpreadsheet\Reader\IReader)
$results - Array containing the import results

The data available in the results array are (accessed by $results["<value>"]):
file - File name of the imported file
totalCount - Total records imported
successCount - Total records imported successfully
failCount - Total records imported unsuccessfully
failList - Array containing failed imports and reasons

Example

Write audit trail for import

$msg = "imported " . $results["totalCount"] . " records (successful: " . $results["successCount"] . " / failed: " . $results["failCount"] . ") from " . $results["file"]; // Set up import message

WriteAuditTrail("log", DbCurrentDateTime(), ScriptName(), CurrentUserID(), $msg, CurrentUserIP(), "", "", "", ""); // Write audit trail

Advanced Settings for Import

Import maximum execution time (seconds)	Maximum executing time for import. Default value is 300 (5 minutes). Change to a larger value if you are importing a lot of records.
Import records by insert only	Only insert is performed. Default value is true. Uncheck if you also want to update records with the same primary key. Important Make sure that you understand the implication before modifying this setting. Beware of that if some field values in the import data are empty, they will overwrite the data in the database.
Import records by transaction	Use transaction for import. Default value is false. Change to true if you want an ALL OR NOTHING import.
Import supported file extensions	Supported file extensions for import (comma separated)
Import to CSV delimiter	Delimiter for CSV file for import. Default value is , (comma).
Import to CSV quote character	Quote for CSV file for import. Default value is " (double quote).