Read Specified Cells

Operation Name

Function Overview

Read Excel data from specified cells.

Data Model

Table model type.

Properties

For information about using variables, refer to "variables".

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter the name on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
File	Required	Available	Enter the path of an Excel file. Click [Browse] button to activate the file chooser and select the file.	Files with ".xlsx" and ".xlsm" extension are processed as Excel book, and files with other extensions are processed as Excel 2003 book. Specify the absolute path of the DataSpider file system as the file path. Constrained characters of DataSpider File System except for path separator "/" cannot be used.
Cell settings	Optional	-	Specify cells to be read. Each column can be operated with the following buttons. [Add]: Adds a column. [Up]: Moves the order of the selected column upwards by one. [Down]: Moves the order of the selected column downwards by one. [Delete]: Removes the column.
Cell settings/Sheet name	Required	Available	Select or enter a sheet name of the Excel file specified in [File].	If sheet name is omitted, or the specified sheet does not exist, the head sheet will be read. Distinguishes on upper/lower cases.
Cell settings/Cell address	Required	Available	Enter the cell address to be read. Example: A1
Cell settings/Cell name	Optional	Available	Enter the name of the cell.	[Cell name] will be used in Mapper schema. This value is not used when reading.
Cell settings/Type	Required	Not available	Specify the type of the cell. [General]: (default) [String]: [Number]: [Date]:

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

Depending on [Cell settings] the number of columns varies.

For details on schema structure, refer to "Table model type schema".

Loading Schema on Mapper

Schema is loaded automatically.

For details, refer to "Edit schema".

Mass Data Processing

Mass data processing is not supported.

PSP Usage

PSP is not supported.

Available Component Variables

Component variable name	Description	Remarks
filePath	Stores the file path.	Default value is null.
message_category	Stores the category of message code corresponding to the occurred error.	Default value is null.
message_code	Stores the code of message code corresponding to the occurred error.	Default value is null.
message_level	Stores the severity of message code corresponding to the occurred error.	Default value is null.
error_type	Stores the error type of occurred error.	Default value is null. Error is represented in the format depicted below. Example:java.io.FileNotFoundException The value may vary depending on the DataSpider Servista version.
error_message	Stores the error message of occurred error.	Default value is null. The value may vary depending on the DataSpider Servista version.
error_trace	Stores the trace information of occurred error.	Default value is null. The value may vary depending on the DataSpider Servista version or the client application used.

Null and empty character

If the value of the cell to be read is empty character, read data will be null.

Specification Limits

To read value of cell whose formula is set.

Read the result of the formula in cell, whose format is [value], within 15 digit range, the same as the Excel specifications, to [number of decimal places] specified in format.

Round digit value beyond valid decimal places.
Round digit value beyond "number of decimal places".

Example

The result of the formula (Number of decimal places)	Reading	How to Read
1234567890123456.123 (2 digits)	1234567890123460	Round digit value beyond valid decimal places.
1234567890.1234567 (10 digits)	1234567890.12346	Round digit value beyond valid decimal places.
12345.123 (2 digits)	12345.12	Round digits beyond 'number of decimal places'.

Read the result of formula in the cell, whose format is "General", within 15 digit range, the same as Excel specifications, up to 9 digits after the decimal point.

Round digit value beyond valid decimal places.
Round the value whose integral part is not 0 to 10-digit decimal.
For value whose Integral part number is 0, treat 9-digit from the first non-zero digit as the valid digit, and round the 10th decimal digit.

Example

The result of the formula	Reading	How to Read
1234567890123456.123	1234567890123460	Round digit value beyond valid decimal places.
1234567890.1234567	1234567890.12346	Round digit value beyond valid decimal places.
1.12345678944	1.123456789	Round the 10th decimal digit
0.0001234567894444	0.000123456789	treat 9-digit from the first non-zero digit as the valid digit, and round the 10th decimal digit.

About date
- The earliest date available is "January 1, 1900".
- The latest date available is "December 31, 9999".
- Date type data format is ISO8601 date format (yyyy-MM-dd) excluding time zone.
- In addition to the date, time when the data are also included, including time zone, similar to ISO8601 date - time format (yyyy-MM-dd HH: mm: ss) is formatted.
Read the following settings are not supported.
- line/column settings, furigana, comment, new line, graph, clip art, pictures, macro, attached data, hyperlink
- Among the format settings of cell, any format other than Number, Date, String(user definition, etc.)
About Excel files for which passwords is set
- Excel files encrypted with passwords cannot be read.
Cells with constraint
- When reading worksheet in Excel 2003 workbook, if the cells with input constraints are set to null by the "Error Message" in the "Data Validation" of "Input Message", ArrayIndexOutOfBoundsException may occur.
  This can be avoided by saving it after clicking 'OK' button in the "Data Validation" dialog for the workbook cell.
- When reading worksheet in Excel 2003 workbook, selecting "User Settings" for the input constraint and a function as a formula will cause an error.
Handling of Excel file whose file size is very large
- Specifying a large Excel file in the [File] may cause OutOfMemoryError. It may also impair the controllability of user interface.
  To prevent this issue, specify a script variable dereferencing the workbook location in the [File].

Main exceptions

Exception name	Causes	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].
java.io.FileNotFoundException	The file specified in [File] does not exist.	Check [File].
InvalidPropertyConfigurationException Cells to be read are not specified.	[Cell setting] is not specified.	Specify [Cell setting].
InvalidPropertyConfigurationException Cell address is not specified.	[Cell setting/Cell address] is not specified.	Specify [Cell setting/Cell address].
InvalidCellAddressException The specified cell address is invalid.	The cell address entered in [Cell setting/Cell address] is invalid.	Check [Cell setting/Cell address] and enter a new cell address.
jxl.read.biff.PasswordException	Read password is set for the Excel 2003 workbook specified in [File].	Input a Excel 2003 workbook to [File], for which read password is not set.
jxl.read.biff.BiffException Unable to recognize OLE stream	Excel 2003 workbook specified in [File] is in false condition.	Check the following. Whether the file specified in [File] is a Excel 2003 workbook included in Support version Whether a non-Excel 2003 workbook file is set with extension ".xls" and specified in [File] Whether the file is not broken
jxl.read.biff.BiffException The file format is corrupt	The Excel 2003 workbook specified in [File] may be broken.	Check the following. Check whether the file specified in [File] is not broken.
jxl.read.biff.BiffException The input file was not found	The Excel 2003 workbook specified in [File] is in false condition.	Check the following. Whether a file with 0 byte is not specified
ZipEntryBrokenException Entry of operating ZIP file is broken	Excel workbook specified in [File] is in false condition.	Check the following. Whether the file specified in [File] is Excel workbook Whether a non-Excel workbok file is set with extension ".xlsx" or ".xlsm" and specified in [File] Whether the file is not broken
ZipEntryNotFoundException Entry of specified name does not exist in zip file	A read password is set to the Excel workbook specified in [File], or the workbook is in false condition.	Check whether read password is set to the Excel workbook specified in [File], or the file is not broken.