Read from Named Range

Operation Name

Function Overview

Read Excel data in a defined range unit.

Data Model

Data model of this type of component is 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 the Excel file. Click [Browse] button to activate the file Selectr and Select the file.	Files with extension ".xlsx" and ".xlsm" are treated as an Excel workbook. Files with other extension are treated as Excel 2003 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.
Range name	Required	Available	Select or input predefined range of Excel file specified in [File].	Distinguishes on upper/lower cases.
Column list	Optional	-	Specify the column. Each column can be operated with the following buttons. [Add]: Add a column. [Up]: Move the order of the selected column upwards by one. [Down]: Move the order of the selected column downwards by one. [Delete]: get the column.	Column name specified in [Column list] is displayed in schema of Mapper.
Column list / Column name	Required	Available	Input column name. You can set the first line of the file specified in [File] in property action of [Update column list] as the column name.
Column list / Type	Required	Not available	Select [Column name] type. [General]: (default) [String]: [Number]: [Date]:

Property Action

Item name	Description	Remarks
Update column list	Get column name and type value from the first line of file specified in [File], and set in [Column list].	Disables when specified file dose not exist, or variables are set in [File]. You can set different cell type in [Column list / Type] when the cell value is empty.

Read Settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Do not get first row as a value	Optional	Not available	Select whether to treat the first line of the specified file as the data. [Checked]: Not as data. [Not Checked]: (default) As data.
Read formula of the cell	Optional	Not available	Select whether to read the formula in the cell as data. [Checked]: Read as data. [Not Checked]: (default) Not read as data.	Disables when Excel workbook is specified in [File].

Data processing method

Item name	Required / Optional	Use of Variables	Description	Remarks
Mass data processing	Required	Not available	Select a data processing method. [Use script settings]: (default) Use adapter to set script properties of the data process. [Disable]: Mass data processing is not performed. [Enable]: 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 adapter. The description will be reflected in the specifications.

Schema

Input Schema

None.

Output Schema

Depending on the [Column list] setting, the number of columns may be different.

See "Schema of Table Model" for details regarding schema structure.

Loading schema in Mapper

Schema is loaded automatically.

For any details, please refer to the 'Editing Schema'

Mass data processing

Mass data processing is supported.

Mass data processing cannot be performed when the file specified in [File] is Excel 2007 workbook.

PSP Usage

PSP is supported.

For details on PSP, refer to "Parallel Stream Processing".

Available Component variables

Component variable name	Description	Remarks
count	Stores the count of the data.	The value defaults to null. Null when using Parallel Stream Processing.
filePath	Return file path.	The value defaults to null. Null when using Parallel Stream Processing.
message_category	Stroes the category to which corresponding message code belongs to, when an error occurs.	The value defaults to null.
message_code	Stores its corresponding message code of occured error.	The value defaults to null.
message_level	Stores the severity of the corresponding message code of the occured error.	The value defaults to null.
error_type	Returns the error type when error occurred.	The value defaults to null. Error is represented in the format depicted below. Example:java.io.FileNotFoundException The message may vary depending on the DataSpider Servista version.
error_message	Return the error message when error occurred.	The value defaults to null. The message may vary depending on the DataSpider Servista version.
error_trace	Return trace information when error occurred.	The value defaults to null. The message may vary depending on the DataSpider Servista version or the client application used.

Null and empty string

Empty character is displayed '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, and hyperlink
- Among the format settings of cell, any format other than standard, value, date, and string (user definition, etc.)
Predefined range with multiple range
- read the first range if multiple range is set for one predefined range.
Print area (Print_Area)
- Get the print area of the last sheet in the book if print area is set in multiple sheets.
The file is locked by a password
- You cannot read data from Excel file which is encrypted with password.
[Read formula of the cell]
- You cannot read formula from Excel workbook.
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	Path input in [File] is directory.	Input file path in [File].
NoDefinitionAreaException	[Range name] is not specified, or does not exist.	Please confirm [Range name].
java.io.FileNotFoundException	File specified in [File] does not exist.	Please check [File].
jxl.read.biff.PasswordException	Excel 2003 workbook specified in [File] is set with read password.	Please input Excel 2003 workbook whose read password is not set in [File]
jxl.read.biff.BiffException Unable to recognize OLE stream	Excel 2003 workbook specified in [File] is not correct.	Check the following. If the file specified in [File] is Excel 2003 workbook included in version support or not. If the extension of a non-excel 2003 workbook is set to [.xls] and is specified in [File]. If the file is damaged or not.
jxl.read.biff.BiffException The file format is corrupt	The Excel 2003 workbook specified in [File] may be damaged.	Check the following. If the Excel 2003 workbook specified in [File] is damaged or not.
ZipEntryBrokenException: Working ZIP files entries are corrupt.	Excel workbook specified in [File] is not correct.	Check the following. If the file specified in [File] is an Excel workbook. If the extension of a non-excel workbook is set to "xlsx" or "xlsm" and is specified in [File] If the file is damaged or not.
ZipEntryNotFoundException: Entry of specified name didn't exist in zip file.	Excel workbook specified in [File] is set with a read password or is not correct.	Please confirm if the Excel workbook specified in [File] is set with a read password or not, or if the file is damaged or not.