Write to Spreadsheet

The Write to Spreadsheet stage writes data to a spreadsheet, such as an Excel spreadsheet, as output from a dataflow.

Configuring the options

On the Write to Spreadsheet Options: Write to Spreadsheet page, configure these options:

Option Description
File name It specifies the path to the file. Select button to browse your file.
Note: If the Spectrum Technology Platform server is running on Linux, remember that the file names and paths on this platform are case sensitive.
Write mode

It specifies how you want to write data in the spreadsheet to write into the dataflow. Select any of these options from the drop-down to create a spreadsheet at runtime:

create or overwrite
Creates a new file and replaces the existing data in the output file each time the dataflow runs.
Insert
Adds the dataflow's output to the mapped area and shifts the data down if already present there.
Append
Adds the dataflow's output to the end of the file without erasing the file's existing data.
Sheet It specifies a sheet in the spreadsheet to which you want to write data into the dataflow.
Start position It specifies either a row-column combination (A1 or B2 ..) or a column from where you want the data to be written.
  • For the Write mode option Insert, you need to provide both row and column.
  • For the Write mode option Append, you can only provide the column as it ignores the row value.
First row as column header It specifies the first row in a file contains header information and not data.
Quick Add To define fields with default values for position, length, and data type, click this button and select the fields you want to add.
Regenerate If the output file contains a header record, click this button to define the fields.
Nullable If the Nullable field is checked and the Name field contains a null value, then the dataflow will write the null value in the spreadsheet.
Refresh Click this button to preview your schema. The preview you see is based on the options you configure for your spreadsheet.

Adding fields in an output file

If the input file does not contain a header record, or if you want to add fields to the output manually, perform these steps:
  1. In File Schema, click ADD in the Name field, and enter the name of the field you want to add.
  2. In the Type field, select one of these data types from the drop-down:
    Option Description
    boolean A logical type with two values: true and false.
    double A numeric data type that contains both negative and positive double-precision numbers between 2-1074 and (2-2-52)×21023. In E notation, the range of values is -1.79769313486232E+308 to 1.79769313486232E+308.
    float A numeric data type that contains both negative and positive single-precision numbers between 2-149 and (2-223)×2127. In E notation, the range of values -3.402823E+38 to 3.402823E+38.
    integer A numeric data type that contains both negative and positive whole numbers between -231 (-2,147,483,648) and 231-1 (2,147,483,647).
    bigdecimal A numeric data type that supports 38 decimal points of precision. Use this data type for data that will be used in mathematical calculations requiring a high degree of precision, especially those involving financial data. The bigdecimal data type supports more precise calculations than the double data type.
    long A numeric data type that contains both negative and positive whole numbers between -263 (-9,223,372,036,854,775,808) and 263-1 (9,223,372,036,854,775,807).
    string A sequence of characters.
    date A data type that contains a month, day, and year. For example, 2012-01-30 or January 30, 2012. You can specify a default date format in Spectrum Management Console.
    time A data type that contains the time of day. For example, 21:15:59 or 9:15:59 PM.
    datetime It is a data type that contains a month, day, year, hours, minutes, and seconds. For example, 2012/01/30 6:15:00 PM.
  3. In the Format field, select your Locale from the drop-down that consists of language and region, and a Format for the data type you chose in step 2.
    Note: You cannot select a format for boolean and string data types.