Fields settings

This section explains the fields of the Excel information.

(1) Excel - Detailed Information (Common fields)

ID

The ID used to identify the registered Excel file.

Business group

A business group for which general users have access rights.

Alternatively, a business group that is used as a filter condition only.

Comment

Comment about the Excel file.

I/O category

Select whether to specify settings for input or output.

Input

: Select this option when you want to set the information used when data is input from the Excel file.

Output

: Select this option when you want to set the information used when data is output to the Excel file.

(2) Excel - Detailed Information

Target sheet

Select the method for specifying the target sheet to be input or output.

  • When I/O category is Input

    All sheets

    : All sheets will be input.

    Specify sheets to read

    : The specified sheets will be read.

    Specify sheets to exclude

    : The specified sheets will not be read.

  • When I/O category is Output

    Use a fixed value

    : Select this option when you want to use a fixed value to specify the output destination sheet name.

    Use a field value

    : Select this option when you want to use a particular field as the output destination sheet name.

Management Excel file

The path of the Excel file that displays the Excel image of the Excel information.

This field is used for registering and correcting single cells or tables in Excel information.

In DataMagic Server, specify the path on the connection destination server. In DataMagic Desktop, specify the path on your local machine.

Sheet name

= Remarks =

To specify multiple sheets, use a forward slash (/) to delimit the sheet names.

Example: Sheet1/Sheet2/Sheet3

  • When I/O category is Input

    Specify the names of the sheets that you want to input or exclude.

    You need to specify this item if you selected a method other than All sheets for Target sheets.

    You cannot specify this item if you selected All sheets for Target sheets.

  • When I/O category is Output

    Specify the names of the sheets that you want to output or exclude.

    You need to specify this item if you selected a method other than All sheets for Target sheets.

    You cannot specify this item if you selected All sheets for Target sheets.

Handling of cells with errors (when I/O category is Input)

Select whether to make the conversion processing end abnormally or to ignore the error and handle the cell as having empty data to perform the processing if an error occurs in the result of the calculation formula set for a cell to be read.

Use a template (when I/O category is Output)

Specify whether to use a template file.

Use a template file if you want to write data to an Excel file while outputting data, by using a file that has preset styles and formulas. This option is enabled if a file name is specified in Template file name of the Excel - Detailed Information screen.

Template file name (when I/O category is Output)

Select the name of the file to be used as a template.

If Use a template is enabled, specify the name of the sheet in the template file that is to be used. This field must be specified if a template is used. You cannot specify more than one sheet name.

Template sheet name (when I/O category is Output)

Specify the name of the output destination sheet.

If Output is specified for I/O category and you selected Use a fixed value for Target sheets, an output sheet name must be specified. You cannot specify multiple sheet names.

Note

If a sheet with the same name exists in the target book file, the sheet is overwritten. For non-target cells, previous data is output as is. If you selected Use a field value for Target sheets, an asterisk (*) is automatically entered.

Single cell tab

Select this tab to use a single cell in the Excel file to be read or written.

Table tab

Select this tab to use data in the table format of the Excel file to be read or written.

No.

Serial number for the field name (starting from 1).

When rows are added mid-way through, serial numbers are reallocated starting from 1.

Maximum number of fields for the field settings is 10000 including individual cells and tables.

Cell (for the Single cell tab) or Column (for the Table tab)

Excel row/column address.

Only uppercase alphabetic characters and numeric characters can be input. If lowercase alphabetic characters are entered, they are converted to uppercase alphabetic characters.

You can specify a value in the range from A to XFD for a table, or from A1 to XFD1048576 for a single cell.

You cannot select the same cell among single cells, or the same cells within a table.

Field name

Excel field name. Commas (,) cannot be used.

An error occurs if there are duplicate field names when the file is saved.

Field type

Field type

M

: Variable-length characters

D

: Date

Z

: Numeric character string

Comment

Field comment.

Initial row number

Specify the first row from which the read or write operation starts.

Specify this item if you selected Table as the target range. You cannot specify this when the target range is a single cell.

End position

Specify a position condition on which a read operation ends.

End of data

: Reading proceeds up to the last position the data appears.

Empty record

: Reading proceeds until an empty record appears.

Empty row in a specified column

: Reading proceeds until a record with an empty value for the column appears.

Specified row

: Reading proceeds up to the specified row.

Row containing an empty cell

: Reading proceeds until a record with an empty value for a cell appears.

Final row number

If Specified row is selected for the end position, specify a row number where the read or write operation ends.

Specified column

If Empty row in a specified column is selected for the end position, specify the target column.

Settings regarding the sheet names when data is output to multiple sheets

If I/O category is Output and you specify Use a field value for Target sheets, you can output data by the value of a field of the input data, to multiple sheets whose value is the value of each field.

Figure 3.1 Output to multiple sheets by field name

(3) Settings for importing Excel data

Select Excel sheet

Sheet of the Excel image to be imported.

Excel image

Excel image to be imported.

Single cell tab

If you select the cell for the displayed Excel image, and then select Add as a single cell(Only Cell) or Add as a single cell(Cell and Field Name), the following settings are specified:

  • A row is inserted after the selected row, and the fields are renumbered.

  • Cell and item name:

    If you select one of the cells and you click Add as a single cell (Only Cell), the address of the selected cell in the Excel sheet (for example, A1 or B3) is stored. The field name is left empty.

    The following shows the behavior when you select two cells and click Add as a single cell(Cell and Field Name):

    • If two cells next to each other are specified, the value of the left cell is stored in the field name, and the address of the right cell in the Excel sheet (for example, A1 or B3) is stored in the cell.

    • If two cells vertically next to each other are specified, the value of the upper cell is stored in the field name, and the address of the lower cell in the Excel sheet (for example, A1 or B3) is stored in the cell.

    • D: Date type

      Displays a date and time when the display format of the cell is a date, time or user-defined date data.

    • Z: Numeric string

      Displays a numeric character string when the display format of the cell is a number, a currency, accounting or user-defined date data.

    • M: Variable-length Characters

      Other than the above.

  • Field: Fixed to NULL.

Table tab

If you select the range of the displayed Excel image, and then select Add as a column of table, the selected range of columns is imported. If you do not select the range, an error occurs. In addition, if you do not select two cells or more, an error occurs.

No.

Serial number for the field name (starting from 1).

When rows are added mid-way through, serial numbers are reallocated starting from 1.

Maximum number of fields for the field settings is 10000 including individual cells and tables.

Cell or Column

Excel row/column address.

Only uppercase alphabetic characters and numeric characters can be input. If lowercase alphabetic characters are entered, they are converted to uppercase alphabetic characters.

You can specify a value in the range from A1 to XFD1048576 for a cell, or from A to XFD for a column.

You cannot select the same cell among single cells, or the same cells within a table.

Field name

Name used for identifying the field. You cannot specify a duplicated value within the same ID. In addition, Sheet cannot be specified.

Field type

Field type.

M

: Variable-length characters

D

: Date type

Z

: Numeric character string type