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

When multiple [OR]s are specified in [End condition / And/Or], the following row is the row to end reading.
- Rows that match either condition
When multiple [AND]s are specified in [End condition / And/Or], the following row is the row to end reading.
- If you select all [(Not specified)] at [Row number], a row that matches all the criteria on the same row
- If you specify a row number at [Row number], the last row, matching all
Example
File to read

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

End reading condition (when row number is not specified)

Column Name Row Number Condition And/Or

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

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

Read the end row and second row, only read the first row.
End reading condition (when row number/condition is not specified)

Column Name Row Number Condition And/Or

Column 1 2 EEE [AND]

Column 2 5 FFF [AND]

End reading row is 5th, reads till the 4th row.
End reading condition (when row number is specified, condition is not specified)

Column Name Row Number Condition And/Or

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

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

End reading row is 7th, reads till the 4th row.
End reading condition (when condition that row number is specified and not specified is mixed)

Column Name Row Number Condition And/Or

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

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

End reading row is 5th, reads till the 4th row.

If both [Row number] and [Condition value] is [(Not specified)], reading ends without reading any row.

If any value or format is not set after the specified column as end condition in Excel 2003 book, it does not match the end condition.

If any value or format is not set on the specified cell as end condition in Excel book, it does 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	And/Or
Column 1	[(Not specified)]	EEE	[AND]
Column 2	[(Not specified)]	FFF	[AND]

Column Name	Row Number	Condition	And/Or
Column 1	2	EEE	[AND]
Column 2	5	FFF	[AND]

Column Name	Row Number	Condition	And/Or
Column 1	2	[(Not specified)]	[AND]
Column 2	7	[(Not specified)]	[AND]

Column Name	Row Number	Condition	And/Or
Column 1	4	[(Not specified)]	[AND]
Column 2	[(Not specified)]	FFF	[AND]

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, hyperlink
- Among the format settings of cell, any format other than Number, Date, String(user definition, etc.)
- If format of a column is different in more than one row, the data can only be treated as General or String type
  In order to process the first and the second row depicted below, the column type of A column need to be set as standard or string.
  Example
  
  A B
  
  1 April 1, 2005 (Date) Yamada (string)
  
  2 1800 (number) Suzuki (string)
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 2013/2010/2007 book.
For [End condition / Column name]
- When [Specify reading end condition] is checked and [End condition / Column name] is specified and [Column list / Column name] is changed, end condition will not used because column name is different from [End condition / Column name].If you changed [Column list / Column name], press "Done" to close Properties window, open again, and re-specify [Column list / Column name].
- When multiple column name is set at [Column list / Column name] and same name is specified at [End condition / Column name], the top of same name column in [Column list / Column name] is used as appropriate.
Cell has been set for form and comments
- The comments have set the cell formatting or even an empty cell value is not entered, will read the read range is considered.
  If you want to exclude such cells from read range, specify [End condition] or clear comments or styles on Excel.
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].

	A	B
1	April 1, 2005 (Date)	Yamada (string)
2	1800 (number)	Suzuki (string)

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.

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 Action

Start/end conditions

Read Settings

Data processing method

Comment

Schema

Input Schema

Output Schema

Loading schema in Mapper

Mass data processing

PSP Usage

Available Component variables

Specify End Reading Condition

File to read

End reading condition (when row number is not specified)

End reading condition (when row number/condition is not specified)

End reading condition (when row number is specified, condition is not specified)

End reading condition (when condition that row number is specified and not specified is mixed)

Null and empty string

Specification Limits

Main 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