XLSDataReader
We assume that you have already learned what is described in:
If you want to find the right Reader for your purposes, see Readers Comparison.
Short Summary
XLSDataReader reads data from XLS or XLSX files.
| Component | Data source | Input ports | Output ports | Each to all outputs1) | Different to different outputs2) | Transformation | Transf. req. | Java | CTL |
|---|---|---|---|---|---|---|---|---|---|
| XLSDataReader | XLS(X) file | 0-1 | 1-n | yes | no | no | no | no | no |
Legend
1) Component sends each data record to all connected output ports.
2) Component sends different data records to different output ports using return values of the transformation. See Return Values of Transformations for more information.
Abstract
XLSDataReader reads data from the specified sheet(s) of XLS or XLSX files (local or remote). It can also read data from compressed files, console, input port, or dictionary.
![[Note]](figures/note.png) | Note |
|---|---|
Remember that XLSDataReader stores all data in memory and has high memory requirements. |
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | no | For port reading. See Reading from Input Port. | One field (byte,
cbyte, string). |
| Output | 0 | yes | For correct data records | Any1) |
| 1-n | no | For correct data records | Output 0 |
Legend:
1): Metadata can use Autofilling Functions.
XLSDataReader Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| Type of parser | Specifies the parser to be used. By default, component guesses according the extension (XLS or XLSX). | Auto (default) | XLS | XLSX | |
| File URL | yes | Attribute specifying what data source(s) will be read (input file, console, input port, dictionary). See Supported File URL Formats for Readers. | |
| Sheet name | 1) | Name of the sheet to be read. Wild cards
? and * can be used
in the name. | |
| Sheet number | 1) | Numbers of the sheet to be read. Numbering starts from
0. Sequence of numbers separated by comma and/or got together
with a hyphen. Following patterns can be used:
number,
minNumber-maxNumber,
*-maxNumber,
minNumber-*. Example:
*-5,9-11,17-*. | |
| Charset | Encoding of records that are read. | ISO-8859-1 (default) | <other encodings> | |
| Data policy | Determines what should be done when an error occurs. See Data Policy for more information. | Strict (default) | Controlled | Lenient | |
| Metadata row | Number of the row containing the names of the columns. By default, the header of the sheet is used as metadata row. See Mapping and Metadata for more information. | 0 (default) | 1-N | |
| Field mapping | Mapping of XLS fields to CloudConnect fields. Expressed as a
sequence of individual mappings for CloudConnect fields separated
from each other by semicolon. Each individual mapping looks
like this: $CloudConnectField:=#XLSColumnCode or
$CloudConnectField:=XLSColumnName. See Mapping and
Metadata for more
information. | ||
| Advanced | |||
| Number of skipped records | Number of records to be skipped continuously throughout all source files. See Selecting Input Records. | 0-N | |
| Max number of records | Maximum number of records to be read continuously throughout all source files. See Selecting Input Records. | 0-N | |
| Number of skipped records per source | Number of records to be skipped from each source file. See Selecting Input Records. | Same as in Metadata (default) | 0-N | |
| Max number of records per source | Maximum number of records to be read from each source file. See Selecting Input Records. | 0-N | |
| Max error count | Maximum number of allowed errors for the
Controlled value of Data
Policy before the graph fails. | 0 (default) | 1-N | |
| Incremental file | 2) | Name of the file storing the incremental key, including path. See Incremental Reading. | |
| Incremental key | 2) | Variable storing the position of the last read record. See Incremental Reading. | |
| Deprecated | |||
| Start row | Has inclusive meaning: First row that is read. Has lower priority than Number of skipped records. | 0 (default) | 1-n | |
| Final row | Has exclusive meaning: First row that is not already read following the last row that still has been read. Has lower priority than Max number of records. | all (default) | 1-n | |
Legend:
1) One of these attributes must be specified. Sheet name has higher priority.
2) Either both or neither of these attributes must be specified.
Advanced Description
Mapping and Metadata
If you want to specify some mapping (Field mapping), click the row of this attribute. After that, a button appears there and when you click this button, the following dialog will open:
This dialog consists of two panes: XLS fields on the left and Mappings on the right. At the right side of this dialog, there are three buttons: for automatic mapping, canceling one selected mapping and canceling all mappings. You must select an xls field from the left pane, push the left mouse button, drag to the right pane (to the XLS fields column) and release the button. This way, the selected xls field has been mapped to one of the output cloudconnect fields. Repeat the same with the other xls fields too. (Or you can click the button.)
Note that xls fields are derived automatically from xls column names when extracting metadata from the XLS file.
When you confirm the mapping by clicking
, the resulting Field
mapping attribute will look like this (for example):
$OrderDate:=#D;$OrderID:=#A
On the other hand, if you check the checkbox on the XLS mapping
dialog, the same mapping will look like this:
$OrderDate:=ORDERDATE,D;$OrderID:=ORDERID,N,20,5
You can see that the Field mapping attribute is a sequence of single mappings separated from semicolon from each other.
Each single mapping consists of an assignment of a cloudconnect field name and xls field. The CloudConnect field is on the left side of the assignment and it is preceded by dollar sign, the xls field is on the right side of the assignment and it is either the code of xls column preceded by hash, or the xls field as shown in the Xls fields pane.
You must remember that you do not need to read and send out all xls columns, you can even read and only send out some of them.
Example 53.2. Field Mapping in XLSDataReader
Mapping with Column Codes
$first_name:=#B;$last_name:=#D;$country:=#EMapping with Column Names (XLS Fields)
$first_name:=f_name;$last_name:=l_name;$country:=country