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 Data
Select this option to read in all the data from a sheet in the spreadsheet.
Range Data
Select this option to read in a subset of data from a sheet by specifying a range of cells to read.
Named Range
Select this option to read in a subset of data from a sheet by specifying a named range from the spreadsheet.

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.