Import Mechanism (Full-featured Version)

« Arbitration | Formats of Importable Files (Full-featured Version) »

Import Mechanism (Full-featured Version)

This manual is in pilot operation.

Overview

In the import process, the data in the target area is cleared before importing the import data. Thus, the overall import process is as follows:

Clear the values of cells in the import target area on the import destination screen.
Import data from the source file and reflect it in the screen data.

The data displayed on the screen in step 2 above is changed, but these changes are not saved in the database. To save, like any other data change, you need to press the Save button.

Below, we will explain the details of each step.

Clearing the Import Target Area

In the first step, the values of the cells in the import target area are cleared on the screen created by the form. The targets are the cells that meet the following conditions:

On the import destination screen, the cell must be inputtable (not limited to cells currently displayed on the screen. This includes cells that become inputtable when a item is selected from the selection box on the vertical axis).
The cell must be for the "target ledger" specified in the target ledger field of the import specification. However, if no target is specified in the target ledger field, all ledger cells that can be inputted in the form are targeted.

Data Import from the Source File

In the second step, based on the data of one line imported from the source file, code conversion and code concatenation are performed, and the process of writing (or skipping) values to the ledger cell keyed by the field value (converted value) associated with the dimension used in the import target ledger is repeated for all lines of the source file. However, if there are lines at the beginning of the import target file that you do not want to include in the import, you can skip them by specifying the number of those lines in the "Header Skip Lines" of the import specification.

If multiple value fields are specified in the import specification, the above process is repeated for each line and each value field (for settings on writing values of each value field to separate ledger cells, please refer to "Considerations for Multiple Value Fields" described later).

Note that the data import process is executed in the order of the lines and the order of the value fields within a line. If a ledger cell has a numeric attribute, the written values for the same ledger cell are accumulated, and for non-numeric cells, the last written value becomes effective. Below, using an example, we will explain the content of the process for one line and one value field:

Example

(1) Layout and Content of Import Data

Assume importing monthly expense data by division, department, and account. The key fields are the fiscal year, month, division, department, and account, followed by the value field "Amount". The data used in the explanation is shown below:

(Fiscal Year) (Month) (Division) (Department) (Account) (Amount) 2016, 05, 10, 111-22, 12345, 1000

⚠ In the following, "Amount" is not defined as a data source field and is considered as an "Implicit Value Field". Even if the amount is explicitly defined as a value field, the essence of the following explanation does not change (the amount field becomes one of the data source fields).

(2) Target Ledger for Import

The label of the target ledger for import is "EXPENSE". The member label settings required at the time of import for each dimension that composes this ledger are shown below:

Account (#ACCOUNT): Use the "Account" ("12345") in the import data directly as the member label.
Change Account (#CHANGE): Regardless of the import data code, set the fixed value "#NONE".
Fiscal Year (#FY): Use the value of "Fiscal Year" ("2008", etc.) in the import data directly as the member label.
Relative Periods: Corresponds to the "Month" in the import data, but conversion is necessary, such as "04" to "M1", "05" to "M2", and so on.
Scenario: Regardless of the import data code, set the fixed value "ACTUAL".
View Format (#VIEW): Regardless of the import data code, set the fixed value "PER".
Division-Department (DIV_DEPT): Connect the "Division" to the first 3 digits of "Department" and prefix with "D" (e.g., "D10111").

Explanation of Processing Based on the Example

Step 1: Setting Values to Data Source Fields

Upon reading one entry of import data, values from each key field of the import data are first set to the 'data source fields' defined in the import specification. Each field has areas for both 'pre-conversion values' and 'post-conversion values', and at this stage, values are set to the 'pre-conversion values'.

To explain how field labels are assigned:
For fields where the 'post-conversion value' directly becomes the member label used in the target ledger, use the label of that dimension as the field label. In this example, this applies to Fiscal Year (#FY), Relative Period (#PERIOD), and Account (#ACCOUNT).

For other fields, assign field labels different from the dimension labels. In this example, Division (DIV) and Department (DEPT) fall into this category.

Step 2: Code Conversion (For Data Source Fields)

In the next step, 'pre-conversion values' of each data source field undergo conversion using translation tables, resulting in 'post-conversion values'. For fields without specified translation tables, 'pre-conversion values' directly become 'post-conversion values'.

In this example, translation tables TBL_PER and TBL_DEPT are applied to fields #PERIOD and DEPT, respectively. Their contents are as follows:

Period Conversion Table (TBL_PER): Registers 12 pairs of month and period labels such as 04→M1, 05→M2, …, 01→M10 (where "04→M1" means a row with "04" as the pre-conversion code and "M1" as the post-conversion code).
Department Conversion Table (TBL_DEPT): Only one conversion rule is registered: the pre-conversion code is "???*", and the post-conversion code is "???". This extracts the top 3 digits of the pre-conversion code. See the section on translation tables for 'Wildcards' and 'Examples of Conversion Rules'.

Step 3: Calculating 'Pre-conversion Values' for Derived Fields

Up to Step 2, member labels for dimensions #SCENARIO, #VIEW, #CHANGE, and DIV_DEPT are not determined. Therefore, the import specification includes 'derived fields' with these dimension labels to determine their member labels.

By setting appropriate expressions in the 'Calculation Expression' column of each field, 'pre-conversion values' for these fields are determined.

The 'pre-conversion values' of derived fields can be set as fixed values, as seen with #SCENARIO, #VIEW, #CHANGE, or calculated from other field values, as with the DIV_DEPT field.

The expression DIV & DEPT specified for the DIV_DEPT field means to concatenate the post-conversion values of fields DIV and DEPT. Note that expressions always refer to post-conversion values.

Step 4. Code Conversion (For Derived Fields)

Furthermore, translation tables are applied to the 'pre-conversion values' of derived fields to determine their 'post-conversion values'. For fields without specified translation tables, 'pre-conversion values' directly become 'post-conversion values'. This process is exactly the same as Step 2, except that it targets derived fields.

In this example, the field DIV_DEPT is subjected to the translation table TBL_DIVDEP. The contents of this translation table are as follows:

Division-Department Translation Table (TBL_DIVDEP): Only one conversion rule is registered. The pre-conversion code is * and the post-conversion code is D*. This results in the pre-conversion code being prefixed with D to form the post-conversion code.

In this example, since there are no fields that reference other derived fields, all fields' 'post-conversion values' are filled in. If there were fields that reference other derived fields, Steps 3 and 4 would be repeated until the 'post-conversion values' for all fields are filled. Note that formulas that cause circular references between derived fields are not allowed (and are checked for errors at the time of form registration).

Step 5. Writing to Cells

By the end of Step 4, the 'post-conversion values' for all fields have been determined. The final step involves updating the cell values in the target ledger (specified in the import specification) using the 'post-conversion values' of fields that have the same labels as the dimensions of the target ledger, as member labels.

If there are multiple import data for the same member label cell, amounts/numbers are accumulated, and for non-numeric attribute cells, they are overwritten with the last value.

Import Processing Log

The process of the aforementioned import procedure is output as a processing log to a file. The file name is automatically assigned. The processing log includes 'pre-conversion values' and 'post-conversion values' for all fields, amounts, and messages indicating the content of exception handling described later.

For details, please refer to the document "Import Processing Log".

Exception Handling

There are two types of exception handling: intended exceptions and unintended exceptions (errors). In either case, data is not written to the cell.

(1) Intended Exception Handling

If the calculation result of a calculation formula or the conversion result from a translation table becomes the value (string) #IGNORE, the data of that value field for that row is skipped and excluded from writing to the ledger. Please also refer to the explanation of translation tables.

(2) Unintended Exception Handling (Error Handling)

Cases include conversion errors, values in fields with labels matching those of dimensions in the target ledger being inappropriate as member labels, or data outside the import range being included (without being skipped).

Among the two types of exception handling mentioned above, 'intended exception handling' takes precedence. That is, rows that are skipped due to intended exception handling are not considered errors, even if they meet the conditions of unintended exception handling.

In either case, the import processing log displays a message and a code indicating the severity of the exception (#SEVERITY). In case (1), the value of #SEVERITY is INFO, and in case (2), it is WARNING or ERROR depending on the severity.

Considerations for Multiple Value Fields

When importing data with multiple value fields, you would typically want to write the values of all those value fields into separate ledger cells, rather than accumulating them into a single ledger cell.

You might want to have monthly value fields and write the value of each field into ledger cells keyed by separate months (relative periods).
You might want to have two value fields, 'Sales Quantity' and 'Sales Amount', and write the values of each field into ledger cells keyed by the corresponding account.

To accommodate such requirements, a mechanism to specify a 'Value Field Key' for value fields is provided. For example, if you have monthly value fields, you can set the labels of relative periods, such as M1, M2, …, in the 'Value Field Key' column for each value field. Additionally, you should set up a derived field with the same label as the Relative Period dimension (#PERIOD) and write @field_key in its calculation expression column.

The @field_key function is used to retrieve the value field key of the value field being processed at each moment. The data import process is executed in the order of the rows and within a row, the order of the value fields, with the evaluation of the calculation expressions being done accordingly. Thus, during the processing of the first value field (with the value field key M1 specified), @field_key returns "M1", and during the processing of the second value field, it returns "M2".

This approach can also be applied to the case where you want to import 'Sales Quantity' and 'Sales Amount'. You would specify the account labels that distinguish between 'Sales Quantity' and 'Sales Amount' in the 'Value Field Key' column for each of those value fields. At the same time, for the derived field with the same label as the Account dimension (#ACCOUNT), you would write @field_key in its calculation expression column.

It’s also possible to specify multiple value field keys for a value field. For example, if you want to import data that holds monthly budgets and forecasts side by side, you would write in the value field key column of each amount field something like "BUDGET, M1" and "FORECAST, M2", separating the scenario label and the relative period label with a comma. Meanwhile, for the derived field for the scenario, you would write @field_key(1) in the calculation expression, which would set the first item separated by a comma, namely BUDGET or FORECAST. Similarly, for the derived field for the relative period, you would write @field_key(2) in the calculation expression to set the second value field key, namely "M1" or "M2". Note that @field_key without an argument, or @field_key(), is shorthand for @field_key(1).

Specifying Translation Tables for Value Fields

It’s also possible to specify translation tables for value fields. While there’s no point in converting codes for values that are amounts or quantities (if processing is needed, calculation expressions can be used), in cases where you’re importing code values into an enumerated account, using a translation table allows you to import the converted values as values for the enumerated account.