Filter:
Submit Search

Read from Sheet

Operation name

Read from Sheet

Function overview

Reads Excel data from a sheet.

Data model

The data model of this component is table model type.

Properties

= Remarks =

For details on use of variables, refer to Variables.

Basic settings

Item name

Required/Optional

Use of variables

Description

Remarks

Name

Required

Not available

Enter a name that is used on the script canvas.

 

Required settings

Item name

Required/Optional

Use of variables

Description

Remarks

File

Required

Available

Enter the Excel file path.

The Browse button launches a file chooser that allows you to select a file.

  • A file with ".xlsx" or ".xlsm" extension is processed as an Excel workbook, and a file with other extensions is processed as an Excel 2003 workbook.

Note

Sheet name

Optional

Available

Select or enter a sheet of the Excel file specified in File.

  • When sheet name is omitted, or the specified sheet doesn't exist, the first sheet will be read.

  • Case-sensitive.

Column list

Optional

-

Specify the columns.

Each column in the column list can be operated with the following buttons:

Up

Moves the selected column upward by one row.

Down

Moves the selected column downward by one row.

Add

Adds a column.

Delete

Deletes the column.

 

Column list/Column name

Required

Available

Enter the column name.

 

Column list/Type

Required

Not available

Select the type of Column name.

General

(Default)

Text
Number
Date

 

Property actions

Item name

Description

Remarks

Open Excel Wizard

Open the Excel Wizard with the file specified in File.

= Remarks =

For how to configure settings, refer to Excel Read Wizard.

  • Disabled when the specified file doesn't exist, when it's not an Excel file included in the supported versions, or when a variable is set in File.

  • When Sheet name is set, the sheet opens in Excel Wizard.
    When sheet name isn't set, when the specified sheet doesn't exist, or when a variable is set in Sheet name, the sheet that was displayed when saved in Excel application opens.

  • 50MB is the maximum file size that can be displayed on the Excel Wizard.

  • When the file size to be displayed exceeds 5MB, a confirmation dialog appears.

Update column list

Get column names and types from the first row values of the file specified in File and set them in Column list.

  • Disabled when the specified file doesn't exist or a variable is set in File.

  • When Start condition is set, the row that meets the condition specified in Start condition becomes the target row.

Note

When the value of a target cell is empty, a different type from that of the target cell might be set to Column list/Type.

Start/End condition

Item name

Required/Optional

Use of variables

Description

Remarks

Specify reading start condition

Optional

Not available

Select whether to specify a condition to start reading.

Selected

Specify the condition to start reading.

Not selected

(Default)

Don't specify the condition to start reading.

The read processing starts from the first column in the first row on the specified sheet.

 

Start condition

Optional

Not available

Select a condition to start reading.

From cell that matches specified string

(Default)

Start reading from the cell that matches Match string.

From specified cell address

Start reading from Cell address.

  • Enabled when Specify reading start condition is selected.

  • Depending on selected item, the corresponding input field (Match string/Cell address) is displayed.

Match string

Optional

Available

Enter the matching string as the condition to start reading.

  • Enabled when From cell that matches specified string is selected in Start condition.

  • Case-sensitive.

Cell address

Optional

Available

Enter the cell address as the condition to start reading.

Example: A1

  • Enabled when From specified cell address is selected in Start condition.

  • Case-sensitive.

Specify reading end condition

Optional

Not available

Select whether to specify a condition to end reading.

Selected

Specify the condition to end reading.

Not selected

(Default)

Don't specify the condition to end reading.

The read processing ends in the last column (last column in Column list of Required settings) in the last row where a value is written.

Any empty rows on the way to the end are also read.

 

End condition

Optional

-

Specify the condition to end reading.

Each column can be operated with the following buttons:

Up

Moves the selected column upward by one row.

Down

Moves the selected column downward by one row.

Add

Adds a column.

Delete

Deletes the column.

  • Enabled when Specify reading end condition is selected.

  • The read target will be all rows until and including the row before the row to end reading.

  • For details, refer to Conditions to end reading.

End condition/Column name

Required

Not available

In Column Name, select the column to check for the condition to end reading.

 

End condition/Row number

Optional

Available

Enter the number of the row to check for the condition to end reading.

When (Not specified) is selected, all rows are checked for the condition to end reading.

  • When omitted, all rows are checked for the condition to end reading.

End condition/Condition value

Optional

Available

Enter the value of the condition to end reading.

If (Not specified) is selected, regardless of the value of the cell, the row specified in Row Number is the row to end reading.

  • When omitted, checking will be performed considering the empty string as the condition value.

End condition/And/Or

Required

Not available

Select a boolean operator for Condition value.

AND

(Default)

OR

 

Read settings

Item name

Required/Optional

Use of variables

Description

Remarks

Do not get first row as value

Optional

Not available

Select whether or not to handle the first row of the specified file as data.

Selected

Don't handle as data.

Not selected

(Default)

Handle as data.

 

Read cell formula

Optional

Not available

Select whether or not to read cell formula as data.

Selected

Read formula as data.

Not selected

(Default)

Don't read formula as data.
Note

Disabled when an Excel workbook is specified in File.

Data processing method

Item name

Required/Optional

Use of variables

Description

Remarks

Data processing method

Required

Not available

Select a data processing method.

Apply script settings

(Default)

The setting for mass data processing in the script properties is applied to the connector.

Do not perform mass data processing

Mass data processing isn't performed.

Perform mass data processing

Mass data processing is performed.

Comment

Item name

Required/Optional

Use of variables

Description

Remarks

Comment

Optional

Not available

You can write a short description of this connector.

 

Schemas

Input schema

None.

Output schema

The number of columns varies depending on the Column list settings.

= Remarks =

For schema structure, refer to Table model type .

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Mass data processing

Mass data processing is supported.

Note

When the file specified in File is an Excel workbook, mass data processing can't be performed.

Parallel Stream Processing

PSP is supported.

= Remarks =

For details on PSP, refer to Parallel Stream Processing.

Available component variables

Component variable name

Description

Remarks

count

The number of read data records is stored.

  • The default value is null.

Note

Value isn't stored when using PSP.

filePath

The file path is stored.

  • The default value is null.

Note

Value isn't stored when using PSP.

message_category

When an error occurs, the category of the message code corresponding to the error is stored.

  • The default value is null.

message_code

When an error occurs, the code of the message code corresponding to the error is stored.

  • The default value is null.

message_level

When an error occurs, the severity of the message code corresponding to the error is stored.

  • The default value is null.

error_type

When an error occurs, the error type is stored.

  • The default value is null.

  • The format of the error type is as follows.

    Example: java.io.FileNotFoundException

error_message

When an error occurs, the error message is stored.

  • The default value is null.

error_trace

When an error occurs, the trace information for the error is stored.

  • The default value is null.

Conditions to end reading

  • When multiple OR boolean operators are specified in And/Or, the following row is the row to end reading.

    • The row that matches any of the conditions

  • When multiple AND boolean operators are specified in And/Or, the following row is the row to end reading.

    • If (Not specified) is selected for every Row number, the first row that matches all conditions

    • If a row number is specified in Row number, the last row by which all conditions are matched

    Example:

    Target file

    Row number

    Column 1

    Column 2

    Column 3

    Column 4

    1

    AAA

    BBB

    CCC

    DDD

    2

    EEE

    FFF

    GGG

    HHH

    3

    III

    JJJ

    KKK

    LLL

    4

    AAA

    BBB

    CCC

    DDD

    5

    EEE

    FFF

    GGG

    HHH

    6

    III

    JJJ

    KKK

    LLL

    Condition to end reading (When row numbers aren't specified)

    Column name

    Row number

    Condition value

    And/Or

    Column 1

    (Not specified)

    EEE

    AND

    Column 2

    (Not specified)

    FFF

    AND

    The row to end reading is the second row and only the first row is read.

    Condition to end reading (When row numbers and condition values are specified)

    Column name

    Row number

    Condition value

    And/Or

    Column 1

    2

    EEE

    AND

    Column 2

    5

    FFF

    AND

    The row to end reading is the fifth row and four rows are read.

    Condition to end reading (When row numbers are specified and condition values aren't specified)

    Column name

    Row number

    Condition value

    And/Or

    Column 1

    2

    (Not specified)

    AND

    Column 2

    7

    (Not specified)

    AND

    The row to end reading is the seventh row and six rows are read.

    Condition to end reading (When a condition with one row number specified and one not specified are mixed)

    Column name

    Row number

    Condition value

    And/Or

    Column 1

    4

    (Not specified)

    AND

    Column 2

    (Not specified)

    FFF

    AND

  • The row to end reading is the fifth row and four rows are read.


  • When (Not specified) is selected in both Row number and Condition value, processing will end without reading even a single row.


  • In an Excel 2003 workbook, when no value nor format is set after the column specified in end condition, it won't match the end condition.


Null and empty strings

  • When the target cell value is empty, the read data will be null.

Message codes, exception messages, and limitations

Connector

Message code

Exception message

Limitations

Messages and limitations of the Excel connector

check

check

check

Policies     (c) Saison Technology Co.,Ltd. 2021

TOP