ajDBImportFromWorkbook function

Available since AlchemyJ v4.1.3

Description

The ajDBImportFromWorkbook function reads a range of cell values in the source workbook and Inserts new record or update the existing record in database table. Please take note that to run this function from Excel, you need to set up the Data Source Connection in ##ExternalResources and config the table in DB Schema.

Syntax

ajDBImportFromWorkbook ( source_workbook_path, data_range, table_name, column_headers, data, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )

Argument Name	Argument Type	Description
source_workbook_path (required)	String	The path of the file to be read. Remark: The function only supports source workbook in xlsx and xlsm format and it cannot not be the same as current workbook.
data_range (required)	String	The range of data to read in the source workbook in [sheet]!range_address format. For example, "sheet1!A2:F10".
table_name (required)	String	The name of the table to be updated.
table_schema (required)	Range / Array	The range that defines the DB Schema.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Number of affected records.

2) Return Type: Single Value

Example

ajDBImportFromWorkbook is a combined function of ajReadWorkbook and ajDBMergeRecord.

We will use the following table as a example. The table name is tb_customer and it has 5 columns and 6 records.

We are going to import these 2 records in wb2.xlsx. "VIVI NG" is a new record to be inserted to the table and the "MERRY SO" is an existing record to be updated.

The data header is required so that the function can identify which column to be updated to the table. Actual column name or data item name defined in the DB Schema can be used.

We use the formula below to import the data from wb2.xlsx and total 2 records were affected.

=ajDBImportFromWorkbook(B21,B22,B23,DBSchema!A4:R9,,,B27)

Let's check the data in database, it was updated successfully.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid source workbook path, it can be a xlsx file or a xlsm file.
Invalid data range should be in [sheet name]![range address:] format. For example, sheet1!B2:C3.
Data range does not exist.
Target workbook path is same as the path of the current workbook.
DB connection error.
No Key order value defined in DB Schema.
Table name does not exist in provided DB Schema.
Table name is empty.
The primary key value is empty.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.