Read from Sheet

Operation Name

Read from Sheet

Function Overview

Read Excel data from a sheet.

Data Model

Table model type.

Properties

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 the name to show on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
File	Required	Available	Enter the Excel file path. You can click [Browse] button to launch file chooser and find the file.	A file with ".xlsx" or ".xlsm" extension is processed as an Excel workbook, and a file with other extension is processed as an Excel 2003 workbook. Specify the file path as an absolute path of DataSpider File System. Except for the path separator “/”, restricted characters of DataSpider File System cannot be used.
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 does not exist, the first sheet will be read. Case-sensitive.
Column list	Optional	-	Specify columns. Each column can be operated with the following buttons. [Add]: Add a column. [Up]: Move the selected column upwards by one row. [Down]: Move the selected column downwards by one row. [Delete]: Delete 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]. For how to configure, refer to "Excel Read Wizard".	Disabled when the specified file does not exist / when it is not an Excel file included in the supported versions / when a variable is set in [File]. When [Sheet name] is set, the sheet opens in Excel Wizard. When sheet name is not set, when the configured sheet does not exist, or when a variable is set in [Sheet name], the sheet displayed when saved in Excel application opens. In Studio for Web, 50MB is the maximum file size that can be displayed on the Excel Wizard. In Studio for Web, 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 does not exist or a variable is set in [File]. When [Start condition] is set, the row that matches [Start condition] will be the target row. When the value of a target cell is empty, a type different from that of the cell one 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 or not to specify the reading start condition. [Checked]: Specify the reading start condition. [Not checked]: (default) Do not specify the reading start condition.
Start condition	Optional	Not available	Select a reading start condition. [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 checked. Depending on selected item, the corresponding input field ([Match string]/[Cell address]) is displayed.
Match string	Optional	Available	Enter the match string for reading start condition.	Enabled when [From cell that matches specified string] is selected in [Start condition]. Case-sensitive.
Cell address	Optional	Available	Enter the cell address for reading start condition. Example: A1	Enabled when [From specified cell address] is selected in [Start condition]. Case-sensitive.
Specify reading end condition	Optional	Not available	Select whether or not to specify the reading end condition. [Checked]: Specify the reading end condition. [Not checked]: (default) Do not specify the reading end condition.
End condition	Optional	-	Specify the reading end condition. Each column can be operated with the following buttons. [Add]: Add a column. [Up]: Move the selected column upwards by one row. [Down]: Move the selected column downwards by one row. [Delete]: Delete the column.	Enabled when [Specify reading end condition] is checked. The rows preceding the reading end row will be the read target. Refer to "Reading End Condition" for more details.
End condition/Column name	Required	Not available	Select [Column name] in which reading end condition is to be checked.
End condition/Row number	Optional	Available	Enter the row number in which reading end condition is to be checked. When [(Not specified)] is selected, the reading end condition will be checked for all rows.	When omitted, the reading end condition will be checked for all rows.
End condition/Condition value	Optional	Available	Enter the condition value of reading end condition. When [(Not specified)]is selected, regardless of the cell value, the row of [Row number] will be the reading end row.	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. [Checked]: Do not handle as data. [Not checked]: (default) Handle as data.
Read cell formula	Optional	Not available	Select whether or not to read cell formula as data. [Checked]: Read formula as data. [Not checked]: (default) Do not read formula as data.	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 data processing method. [Apply script settings]: (default) Apply the mass data processing settings of the script property to the adapter. [Do not perform mass data processing]: Mass data processing will not be performed. [Perform mass data processing]: Mass data processing will be performed.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input schema

None.

Output schema

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

Refer to “Schema of table model type” for more details.

Loading schema in Mapper

Schema is loaded automatically.

Refer to “Edit Schema” for more details.

Mass Data Processing

Mass Data Processing is supported.

When the file specified in [File] is an Excel workbook, mass data processing cannot be performed.

PSP Usage

PSP is supported.

Refer to “Parallel Stream Processing” for more details.

Available Component Variables

Component variable name	Description	Remarks
count	The number of read data is stored.	Default value is null. Value is not stored in PSP.
filePath	The file path is stored.	Default value is null. Value is not stored in PSP.
message_category	When an error occurs, the corresponding message code's category is stored.	Default value is null.
message_code	When an error occurs, the corresponding message code is stored.	Default value is null.
message_level	When an error occurs, the corresponding message code's severity is stored.	Default value is null.
error_type	When an error occurs, the error type is stored.	Default value is null. The error type will be in the following format. Example: “java.io.FileNotFoundException” The contents that will be stored may vary depending on DataSpider Servista versions.
error_message	When an error occurs, the error message is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace	When an error occurs, the error's trace information is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Reading End Condition

When multiple [OR] is specified in [And/Or], the following row will be the reading end row.
- Row that matches any of the conditions
When multiple [AND] is specified in [And/Or], the following row will be the reading end row.
- If [(Not specified)] is selected for all [Row number], the row that matches all conditions
- If row number is specified in [Row number], the last row that matches all conditions.
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

Reading end condition (When row number is not specified)

Column name Row number Condition value And/Or

Column 1 [(Not specified)] EEE [AND]

Column 2 [(Not specified)] FFF [AND]

The reading end row is the second row and only the first row is read.
Reading end condition (When row number / condition value is specified)

Column name Row number Condition value And/Or

Column 1 2 EEE [AND]

Column 2 5 FFF [AND]

The reading end row is the fifth row and four rows are read.
Reading end condition (When row number is specified and condition value is not specified)

Column name Row number Condition value And/Or

Column 1 2 [(Not specified)] [AND]

Column 2 7 [(Not specified)] [AND]

The reading end row is the seventh row and six rows are read.
Reading end condition (When a condition with 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 reading end row 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 will not match the end condition.

In an Excel workbook, when no value nor format is set to the cell specified in end condition, it will not match the end condition.

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

Column name	Row number	Condition value	And/Or
Column 1	[(Not specified)]	EEE	[AND]
Column 2	[(Not specified)]	FFF	[AND]

Column name	Row number	Condition value	And/Or
Column 1	2	EEE	[AND]
Column 2	5	FFF	[AND]

Column name	Row number	Condition value	And/Or
Column 1	2	[(Not specified)]	[AND]
Column 2	7	[(Not specified)]	[AND]

Column name	Row number	Condition value	And/Or
Column 1	4	[(Not specified)]	[AND]
Column 2	[(Not specified)]	FFF	[AND]

Null and Empty Strings

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

Specification Limits

Reading values of cells with formulas

The result value of a formula in a cell whose format is "Number" will be read up to the number of decimal places set in the format in range within the limit of 15 significant digits as Excel specifications.

Digits that exceed the significant digits will be rounded off.
Digits under the specified number of decimal places will be rounded off.

Example:

Result value of formula (Number of decimal places)	Read value	Read method
1234567890123456.123 (2 digits)	1234567890123460	Round off the digits that exceed the number of significant digits
1234567890.1234567 (10 digits)	1234567890.12346	Round off the digits that exceed the number of significant digits
12345.123 (2 digits)	12345.12	Round off the digits that exceed the specified number of decimal places

The result value of a formula in a cell whose format is "General" will be read up to 9 decimal places in range within the limit of 15 significant digits as Excel specifications.

Digits that exceed the significant digits will be rounded off.
A value whose integer part is not 0 will be rounded off to 9 decimal places.
For a value whose integer part is 0, the 9 digits from the first non-zero digit will be counted as significant digits, and the 10th digit will be rounded off.

Example:

Result value of formula	Read value	Read method
1234567890123456.123	1234567890123460	Round off the digits that exceed the number of significant digits
1234567890.1234567	1234567890.12346	Round off the digits that exceed the number of significant digits
1.12345678944	1.123456789	Round off the decimal places to 9 digits.
0.0001234567894444	0.000123456789	The 9 digits from the first non-zero digit will be counted as significant digits, and the value will be rounded off to 9 decimal places

Date
- The oldest date that can be handled as date is 1900/01/01.
- The latest date that can be handled as date is 9999/12/31.
- Date type data will be read according to the cell format as follows.
  - If date is alone set as format, only date will be read. The data will be output in yyyy-MM-dd, the format of ISO8601 without timezone.
  - If time is set in addition to date as format, date and time will be read. The data will be output in date-time (yyyy-MM-dd HH:mm:ss) format, similar to ISO8601 without timezone.
For the following settings, reading is not supported.
- Settings of rows and columns / phonetic guides / comments / newlines / graphs / clip arts / shapes / pictures / macros / attachments / hyperlinks
- Cell formats other than General / Number / Date / Text (Custom format, etc.)
- When multiple rows of a column have different formats, the data will be read only as General or Text type.
  To read the first and second rows in the following example, set column A as General or Text type.
  Example:
  
  A B
  
  1 2005/4/1 (Date) Yamada (Text)
  
  2 1800 (Number) Suzuki (Text)
File encrypted with a password
- You cannot read data from an Excel file that is encrypted with a password.
[Read cell formula]
- You cannot read formulas from an Excel workbook.
[End condition/Column name]
- When [Specify reading end condition] is checked, [End condition/Column name] is set, and [Column list/Column name] is changed after that, as the column name differs, the end condition will not be used as condition. When changing [Column list/Column name], click "Finish" button and close the properties window. Then open the properties windows again and configure [Column list/Column name].
- When multiple columns with the same name are set in [Column list/Column name] and that name is set in [End condition/Column name], the column at the topmost position in columns of [Column list/Column name] with the same name will be used.
Cell with formula/comment
- A cell with formula/comment will be considered to be in the reading range and read even if the cell is empty, with no value.
  To exclude such cell from the reading range, set [End condition] or clear the formula or comment in Excel.
Cell with validation
- In an Excel 2003 workbook, if validation is set for a cell and "Input message" or "Error message" of the validation is set as null, ArrayIndexOutOfBoundsException may occur.
  In that case, you can prevent it by opening the "Validation" dialog for the cell in workbook, clicking "OK" button, and then saving it.
- In an Excel 2003 workbook, when you read a sheet where "Custom" is selected for validation of a cell and a function is used in formula of the cell, an error may occur.
Handling large Excel files
- When a large Excel file is specified in [File], OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.
  In that case, you can prevent this issue by specifying a variable such as script variable in [File].

	A	B
1	2005/4/1 (Date)	Yamada (Text)
2	1800 (Number)	Suzuki (Text)

Major Exceptions

Exception name	Cause	Solution
InvalidPropertyConfigurationException <property name> is not specified.	[<property name>] is not specified.	Specify [<property name>].
FileIsDirectoryException	The path entered in [File] is directory.	Enter a file path in [File].
InvalidStartCellAddressException: The specified starting cell address is invalid.	The cell address entered in [Cell address] is invalid.	Check [Cell address] and enter a valid one.
java.io.FileNotFoundException	The file specified in [File] does not exist.	Check [File].
jxl.read.biff.PasswordException	A password to open is set in the Excel 2003 workbook specified in [File].	Enter an Excel 2003 workbook in which a password to open is not set in [File].
jxl.read.biff.BiffException Unable to recognize OLE stream	The Excel 2003 workbook specified in [File] is in an invalid state.	Check the following. Whether the file specified in [File] is an Excel 2003 workbook included in Supported Versions Whether a file that is not Excel 2003 workbook is specified with ".xls" extension in [File] Whether the file is corrupted
jxl.read.biff.BiffException The file format is corrupt	The Excel 2003 workbook specified in [File] might be corrupted.	Check the following. Check whether the file specified in [File] is corrupted.
ZipEntryBrokenException: The entry of the operating ZIP file is broken.	The Excel workbook specified in [File] is in an invalid state.	Check the following. Whether the file specified in [File] is an Excel workbook Whether a file that is not Excel workbook is specified with ".xlsx" / ".xlsm" extension in [File] Whether the file is corrupted
ZipEntryNotFoundException: Entry of the specified name does not exist in the zip file	The Excel workbook specified in [File] is protected with password to open or is in an invalid state.	Check whether password to open is set to the Excel workbook specified in [File], and whether the file is corrupted.

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

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

Read from Sheet

Operation Name

Function Overview

Data Model

Properties

Basic settings

Required settings

Property actions

Start/End condition

Read settings

Data processing method

Comment

Schema

Input schema

Output schema

Loading schema in Mapper

Mass Data Processing

PSP Usage

Available Component Variables

Reading End Condition

Target file

Reading end condition (When row number is not specified)

Reading end condition (When row number / condition value is specified)

Reading end condition (When row number is specified and condition value is not specified)

Reading end condition (When a condition with row number specified and one not specified are mixed)

Null and Empty Strings

Specification Limits

Major Exceptions

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