Import Specifications

« Cell Specification | Export Specification »

This manual is in pilot operation.

Import specifications are used to define how data should be imported from comma-separated value files (CSV files) into ledgers using forms. Here are some key points about the import process:

Data can be imported into multiple ledgers from a single form. For example, in fusion_place, transaction data like amounts and summaries (transaction comments) are kept in separate ledgers. In such cases, transaction data can be imported using a single form, and the data can be reflected in both the amount ledger and the summary ledger.
Data in input-eligible cells on the screen (created by the form) will be cleared before importing (however, only cells linked to the target ledgers will be cleared). Therefore, if there is an error in the import data, correcting the data and re-importing it will completely clear the previously imported data.
Not only amounts/numbers but also string values, Boolean values, and enumeration values can be imported.
Code conversion is possible during import. There can be any number of translation tables, and each table’s content can be made common to all participants or can be set to allow separate registration for each participant. When describing the before/after code pairs in the translation table, wildcards (? and *) can be used to reduce the number of entries.
Complex string manipulations, such as code concatenation, are also possible. For example, it’s possible to concatenate multiple key field codes from the input data to create a dimension member label. Combining this function with the above-mentioned code conversion function allows for very complex conversions. For instance, codes in the input data can be converted using a translation table, and then the result can be concatenated with another code in the input data to obtain a member label. Code conversion and code concatenation can be repeated any number of times.
It is also possible to import calculated values. For example, if the file to be imported contains items such as sales price and sales quantity, you can multiply them to calculate the sales amount and import it into the ledger. It is also possible to import the sales price and sales quantity at the same time (or not to import them).

For detailed information about the process, please refer to the document "Mechanism of Import".

Format of Importable Files

Please refer to the document "Format of Importable Files (Full-Featured Version)".

Setting Items for Import Specifications

The items that can be specified in import specifications are as follows:

[General Setting Items]

Item Mandatory Setting

Item	Mandatory	Setting
・Enable import	―	By default, it is OFF. If you want to perform imports using this form, please set it to ON.
・Target ledger	― ^[1]	Specify the target ledger for import. Please choose from the ledgers used as data sources in this form. It’s also possible not to specify the target ledger here. In that case, please provide a field with the label `#LEDGER`. Then, set up a calculation expression or similar so that the converted value becomes the ledger label. That is, if you use the `#LEDGER` field as the data source field, please specify the ledger label in the corresponding field in the CSV data to be imported. If it’s used as a calculation field, derive the ledger label using a calculation expression. In either case, it’s possible to apply a translation table.
・Sign type	✓ ^[1]	Specify the interpretation of the sign (positive/negative) when importing amounts/numbers. Follow account attribute Treat debits as Treat credits as The initial suggested value is "Treat debits as +". For more details on sign interpretation, please see the explanation in "Formatting the Value Field".
・Number of header lines to skip (Excluding blank lines)	✓ ^[1]	If the import file contains lines at the beginning that you do not want to import, specify the number of such lines. However, count excluding blank lines.
・Do not warn when importing to closed cells	―	If checked, data will be written to closed cells without issuing a warning and will be skipped (this will be logged with a severity of "INFO").

・Enable import

―

By default, it is OFF. If you want to perform imports using this form, please set it to ON.

・Target ledger

―
^[1]

Specify the target ledger for import. Please choose from the ledgers used as data sources in this form. It’s also possible not to specify the target ledger here. In that case, please provide a field with the label #LEDGER. Then, set up a calculation expression or similar so that the converted value becomes the ledger label.

That is, if you use the #LEDGER field as the data source field, please specify the ledger label in the corresponding field in the CSV data to be imported. If it’s used as a calculation field, derive the ledger label using a calculation expression. In either case, it’s possible to apply a translation table.

・Sign type

✓
^[1]

Specify the interpretation of the sign (positive/negative) when importing amounts/numbers.

Follow account attribute
Treat debits as
Treat credits as

The initial suggested value is "Treat debits as +". For more details on sign interpretation, please see the explanation in "Formatting the Value Field".

・Number of header lines to skip
(Excluding blank lines)

✓
^[1]

If the import file contains lines at the beginning that you do not want to import, specify the number of such lines. However, count excluding blank lines.

・Do not warn when importing to closed cells

―

If checked, data will be written to closed cells without issuing a warning and will be skipped (this will be logged with a severity of "INFO").

[Settings for Data Source Fields]

Specify the following items for each field (data source field) of the CSV file you wish to import. The order of the data source fields corresponds to the order of fields in each row of the CSV file.

Item Mandatory Setting

Item	Mandatory	Setting
・Label	✓	The label assigned to the field. This label can be used to refer to the value of this field in the calculation expression of a derived field.
・Value	―	Check this if the field is a value field (a field containing data values to be imported). [Implicit Value Field] If no value fields are specified through either data source fields or derived fields, it is treated as if there is an additional value field at the end of the data source fields' order. This is referred to as an "Implicit Value Field." Since the Implicit Value Field has no label, it cannot be referenced in the calculation expression of a derived field. Also, a translation table cannot be specified, so the post-conversion value will be the same as the pre-conversion value.
・Name	―	Specify the name of the field. It is a multilingual string. If the label corresponds to a dimension label used in the target ledger, the dimension name will be displayed automatically.
・Value Field Key	―	This is only specifiable if the field is a value field (not mandatory). Specify the key item, such as the member label assigned to that value field. If there are multiple value fields, you usually need to specify this key to associate each value field with different members. The specified key can be referenced in the calculation expression of a derived field using the `@field_key` function. You can also specify multiple keys separated by commas. In that case, specify the key number starting from 1 as an argument to the `@field_key` function. For how to use the Value Field Key, please also see the "Consideration for Multiple Value Fields" in "Mechanism of Import" at link:https://docs.fusionplace.net/manual/en/concepts/forms/full_featured_forms/components/a1_mechanizm_of_import.html#ConsiderationForMulipleValueFields.
・Translation Table	―	Specify the Translation Table to be applied for converting the field’s value. No specification is needed if conversion is not required. The "pre-conversion" value of this field (i.e., the actual value of the field in the imported data) will be converted by this Translation Table, and the "post-conversion" value will be obtained. If other fields' expressions refer to this field, the "post-conversion" value is passed. If no translation table is specified, the post-conversion value equals the pre-conversion value.

・Label

✓

The label assigned to the field. This label can be used to refer to the value of this field in the calculation expression of a derived field.

・Value

―

Check this if the field is a value field (a field containing data values to be imported).

[Implicit Value Field]

If no value fields are specified through either data source fields or derived fields, it is treated as if there is an additional value field at the end of the data source fields' order. This is referred to as an "Implicit Value Field." Since the Implicit Value Field has no label, it cannot be referenced in the calculation expression of a derived field. Also, a translation table cannot be specified, so the post-conversion value will be the same as the pre-conversion value.

・Name

―

Specify the name of the field. It is a multilingual string. If the label corresponds to a dimension label used in the target ledger, the dimension name will be displayed automatically.

・Value Field Key

―

This is only specifiable if the field is a value field (not mandatory).

Specify the key item, such as the member label assigned to that value field. If there are multiple value fields, you usually need to specify this key to associate each value field with different members.

The specified key can be referenced in the calculation expression of a derived field using the @field_key function. You can also specify multiple keys separated by commas. In that case, specify the key number starting from 1 as an argument to the @field_key function.

For how to use the Value Field Key, please also see the "Consideration for Multiple Value Fields" in "Mechanism of Import" at link:https://docs.fusionplace.net/manual/en/concepts/forms/full_featured_forms/components/a1_mechanizm_of_import.html#ConsiderationForMulipleValueFields.

・Translation Table

―

Specify the Translation Table to be applied for converting the field’s value. No specification is needed if conversion is not required.

The "pre-conversion" value of this field (i.e., the actual value of the field in the imported data) will be converted by this Translation Table, and the "post-conversion" value will be obtained. If other fields' expressions refer to this field, the "post-conversion" value is passed.

If no translation table is specified, the post-conversion value equals the pre-conversion value.

[Settings for Derived Fields]

Specify derived fields and their calculation expressions. You can have any number of derived fields (or none at all). Unlike data source fields, the order of derived fields has no particular meaning.

Item Mandatory Setting

Item	Mandatory	Setting
・Label	✓	(Same as Data Source Field)
・Value	―
・Name	―
・Value Field Key	―
・Calculation Expression	✓	Describe the field expression for assembling the field’s value (code value). The way to write expressions will be described later. This value is considered as the "pre-conversion" value of the field. Field values are strings. When the result of a calculation expression is set to a field, if the result is a number, it will be converted to a simple string by removing unnecessary zeros at the end of the decimal part. For example, the number 1234.5600 will be set as the string "1234.56". The number 1234.0000 will become "1234". If the result of the calculation expression becomes the value `#IGNORE` (a string), the data will be skipped. Skipping is done for each import data (row data) and for each value field. Skipping can also be performed using a Translation Table.
・Translation Table	―	(Same as Data Source Field)

・Label

✓

(Same as Data Source Field)

・Value

―

・Name

―

・Value Field Key

―

・Calculation Expression

✓

Describe the field expression for assembling the field’s value (code value). The way to write expressions will be described later.

This value is considered as the "pre-conversion" value of the field.

Field values are strings. When the result of a calculation expression is set to a field, if the result is a number, it will be converted to a simple string by removing unnecessary zeros at the end of the decimal part. For example, the number 1234.5600 will be set as the string "1234.56". The number 1234.0000 will become "1234".

If the result of the calculation expression becomes the value #IGNORE (a string), the data will be skipped. Skipping is done for each import data (row data) and for each value field.
Skipping can also be performed using a Translation Table.

・Translation Table

―

(Same as Data Source Field)

Mapping to Target Ledger Cells

For each dimension used in the target ledger, it is necessary to set up fields with the same labels. These can be either data source fields or derived fields. During the import process, the "converted" values of these fields are considered as member labels, and the values of the value fields (also the "converted" values) are written into the ledger cells specified by their combinations.

For more details, please refer to the document "Mechanism of Import Processing".

Writing Field Expressions

Field expressions are a dialect of text expressions. A key characteristic is that label constants signify "field" objects. For detailed syntax, please refer to "Explanation of Text Expressions".
Here, we present some commonly used examples.

1. Referencing Values from Other Fields

By simply specifying the field label, you can refer to the "converted" value of that field.

  IN_PRODUCT_CODE (1)

1	The converted value of the field labeled IN_PRODUCT_CODE.

If the field label consists only of numbers or could be confused with a number, enclose the label in single quotes:

  '1234' (1)

1	Not the number 1234.0000, but the converted value of the field labeled '1234'.

Note that values obtained by referencing field values are fundamentally strings but are automatically converted to numbers when used in arithmetic expressions.

  IN_PRODUCT_CODE & "A" (1)

1	If the converted value (string) of the field labeled IN_PRODUCT_CODE is "1000", the result of this expression will be the string "1000A".

  IN_PRODUCT_CODE + "1" (1)

1	Under the same conditions, the result of this expression will be the number "1001". Note that when this value is set in a field, the field value becomes the string "1001.0000".

2. Setting Fixed Values

Fixed values can be specified by enclosing them in double quotes.

  "YTD" (1)

1	Represents the fixed value YTD.

3. Concatenating Values from Other Fields or Fixed Values

Field values and fixed values can be concatenated using the & concatenation operator.

  "P" & IN_PRODUCT_CODE (1)

1	Adds the fixed value P to the beginning of the converted value of the field labeled `IN_PRODUCT_CODE`.

  IN_PRODUCT_CODE & IN_DEPT_CODE (1)

1	Concatenates the converted values of the fields labeled `IN_PRODUCT_CODE` and `IN_DEPT_CODE`.

4. Setting Default Values or Parameter Values at Import

These values can be retrieved with the @POV function for the dimension (parameter values take precedence over default values).

  Dimensions!#FY!@POV (1)

1	The label of the Fiscal Year member selected by the parameter will be set in the field.

5. Calculating Values for Value Fields

To calculate and import sales amount by multiplying unit price (held in the PRICE field) by sales quantity (held in the QUANTITY field), you would write the following in the calculation formula for the sales amount field:

  PRICE * QUANTITY

Note that to include the sales amount as an import target, you must check the "value" column for the sales amount field.

6. When Making Conditional Judgments

You can use the IF conditional expression of text expressions. For example, to derive the "FYnnnn" fiscal year format from the calendar year (held in the YEAR field) and the calendar month (held in the MONTH field) for a company with a March year-end, you might write the following in the calculation formula for the fiscal year field. Note that when concatenating numbers with strings in the middle of the expression, you should use the @Text() function to format it properly (in the example below, YEAR - 1 becomes a number).

  IF MONTH <= "03" THEN
    "FY" & @Text(YEAR-1,"0000")
  ELSE
    "FY" & YEAR
  ENDIF

The IF conditional expression itself returns a value, meaning the above can also be written as:

  "FY" & @Text( YEAR - (IF MONTH <= "03" THEN 1 ELSE 0 ENDIF ), "0000")

Similarly, the relative period becomes M1 for April, M12 for March, so it can be derived using the following expression:

  IF MONTH <= "03" THEN
    "M" & @Text(MONTH + 9,"#0")
  ELSE
    "M" & @Text(MONTH -3,"#0")
  ENDIF

Note that the above first line compares the value of the "MONTH" field with "03" as strings, but the following writes it to compare as numbers. In this case, either approach yields the expected result:

  IF MONTH <= 3 THEN

1. When "Enable import" is set to ON