Read from Sheet

Operation Name

Read from Sheet

Function Overview

Read Excel data based on sheet 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 2013/2010/2007 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.
Sheet name Optional Available Select or input Sheet of Excel file specified in [File].
  • If the sheet name is omitted, or the specified sheet does not exist, the first sheet is read.
  • 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 list / Column name Required Available Input column name.  
Column list / Type Required Not available Select [Column name] type.
  • [General]: (default)
  • [String]:
  • [Number]:
  • [Date]:
 
Property Action
Item name Description Remarks
Start Excel Wizard Start Excel wizard with file specified at [File].
For information on setting, please refer to "Excel Write Wizard".
  • Version includes support for the specified file the specified file does not exist unless the workbook Excel [File] menu, if the variable is set to disabled.
  • Opens the set sheet with excel wizard if [Sheet name] is set.
    If not set, or the set sheet does not exist, or any variable is set in [Sheet name], the sheet shown when the book was saved in Excel application is opened.
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].
  • If [Starting condition] is configured, the row matches the [Starting condition] is the target.
  • You can set different cell type in [Column list / Type] when the cell value is empty.
Start/end conditions
Item name Required / Optional Use of Variables Description Remarks
Specify reading starting condition Optional Not available Select whether to specify a condition to start reading.
  • [Checked]:
    Specify the condition to start reading.
  • [Not Checked]: (default)
    Do not specify the condition to start reading.
 
Starting condition Optional Not available Specify the condition to start reading.
  • [From cell that matches with 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 starting condition] is checked.
  • According to the selected specifying method, switches to the corresponding input field ([Match string]/[Cell address]).
Match string Optional Available Enter the matching string as the condition to start reading.
  • Enabled when [From cell that matches with specified string] is selected in [Starting condition].
  • Distinguishes on upper/lower cases.
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 [Starting condition].
  • Distinguishes on upper/lower cases.
Specify reading end condition Optional Not available Select whether to specify a condition to end reading.
  • [Checked]:
    Specify the condition to end reading.
  • [Not Checked]: (default)
    Do not specify the condition to end reading.
 
End condition Optional - Specify the condition to end reading.

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.
  • Enabled when [Specify reading end condition] is checked.
  • Reads till the last row before the specified "end reading row".
  • Please refer to "End reading condition" for details.
End condition / Column name Required Not available Select the column name to check [end reading condition].  
End condition / Row number Optional Available Enter the row number to check the end reading condition.
If [(Not specified)] is select, all the rows are checked for end reading condition.
  • If omitted, all the rows are checked for end reading condition.
End condition / Condition value Optional Available Enter the
If [(Not specified)] is selected, regardless of the value of the cell, the row specified in [Row number] is the row to end reading.
  • If omitted, check empty string as the condition value.
End condition / And/Or Required Not available Select the logical operator of [Condition value] .
  • [AND]: (default)
  • [OR]:
 
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 2013/2010/2007 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.

Specify End Reading Condition

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].
InvalidStartCellAddressException:
Starting cell address you specified is invalid
[Cell address] invalid address entered in the cell. Check [Cell address] and enter correct cell.
java.io.FileNotFoundException Please check [File]. Please check [File].
jxl.read.biff.PasswordException Excel book specified in [File] is set with read password. Please input Excel book whose read password is not set in [File]
jxl.read.biff.BiffException
Unable to recognize OLE stream
Excel book specified in [File] is not correct. Check the following.
  • If the file specified in [File] is Excel book included in version support or not.
  • If the extension of a non-excel book 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 book specified in [File] may be damaged. Check the following.
  • If the Excel book specified in [File] is damaged or not.
ZipEntryBrokenException:
Working ZIP files entries are corrupt.
Excel book specified in [File] is not correct. Check the following.
  • If the file specified in [File] is an Excel 2013/2010/2007 book.
  • If the extension of a non-excel book 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 2013/2010/2007 book specified in [File] is set with a read password or is not correct. Please confirm if the Excel 2013/2010/2007 book specified in [File] is set with a read password or not, or if the file is damaged or not.