Read from Spreadsheet
Read from Spreadsheet reads data from an Excel spreadsheet as input to a dataflow in these supported formats: *.xls and *.xlsx.
File Properties Tab
The File Properties tab contains options for specifying the spreadsheet and data to read into the dataflow.
|
Field Name |
Description |
|---|---|
| Server name | Indicates whether the file you select as input is located on the computer running Spectrum Enterprise Designer or on the Spectrum Technology Platform server. If you select a file on the local computer, the server name will be My Computer. If you select a file on the server the server name will be Spectrum Technology Platform. |
|
File name |
Specifies the path to the file. Click the ellipses button (...) to locate the file you want. Note: If the Spectrum Technology Platform server is running on Linux,
remember that file names and paths on these platforms are case sensitive.
|
|
Data selection |
Specifies how you want to select data from the spreadsheet to read into the dataflow:
|
|
Sheet selection |
If you choose Sheet Data or Range Data in the Data selection field, use this option to choose the sheet from which you want to read data into the dataflow. |
|
Range |
If you choose Range Data in the Data selection field, use this option to specify the cell that starts the range and the cell that ends the range. |
|
Named range |
If you choose Named Range in the Data selection field, use this option to specify the name of the range you want to read into the dataflow. Ranges are defined in the spreadsheet. If no ranges are listed it means that no ranges are defined in the spreadsheet. |
|
Header row |
Check this box to specify a row that contains column headings. Column headings become the dataflow field names although you can change field names on the Fields tab. If you do not check this box, the dataflow fields are given generic default names such as Column1 and Column2. The header row you specify is relative to the data selection. For example, if you choose Range Data in the Data selection field and the range begins on the fifth row, and you specify 1 as the header row, then the fifth row in the spreadsheet will be used as the header because the fifth row of the spreadsheet is the first row of the range. |
|
Data offset from header |
If you specify a header row, this field specifies the first row that contains data, relative to the header. For example, if you specify 1, the first row below the header will be the first row of data to be read into the dataflow. If you specify 2, the second row below the header will be the first row read into the dataflow. |
|
First data row |
If you do not specify a header row, this field specifies which row within the data selection contains the first row of data to read into the dataflow. The row you specify is relative to the data selection. For example, if you choose Range Data in the Data selection field and the range begins on the fifth row, and you specify 1 as the first data row, then the first row of data to be read into the dataflow will be the fifth row. |
|
Ignore empty rows |
Select this option if you want empty rows to be excluded from the dataflow. If you do not select this option, empty rows in the spreadsheet will result in empty records in the dataflow. Note: This option does not affect the data shown in the preview. Empty rows are
always shown in the preview even if this option is selected.
|
Fields Tab
The Fields tab contains options for mapping the data from the spreadsheet to fields in the dataflow.
|
Option |
Description |
|---|---|
|
Regenerate |
Click this button to populate the fields tab with the fields in the input file defined on the File Properties tab. |
|
Detect Type |
Click this button to automatically determine the data type for all the fields. You can manually change a field's data type by selecting the field and clicking Modify. |
|
Modify |
Select a field then click this button to modify the field name or data type. |