Read from Named Range

Operation Name

Read from Named Range

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

Specification Limits

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.