Output Excel - Data360_Analyze

Outputs data to one or more worksheets within an Excel workbook.

You can have multiple inputs to the OutputExcel node and each input is exported to a worksheet within the output Excel file. You can export each input to a separate worksheet or you can combine data from multiple inputs into a single worksheet, provided that the data is output to different cells within the worksheet. The names of the worksheets correspond to the names of the input pins.

Note: The following characters are not permitted within input pin names: / \ * = [ ] Invalid pin names will not cause the node to fail, but the name will be changed to remove any invalid characters and a warning will notify you of this change.

In the File property, click the folder icon and browse to the Excel file to which you want to export the data, or enter the path to where you want to output the Excel data, including the filename and file type, for example:

C:\Users\<user>\Documents\Output_Excel.xlsx

If the file does not exist, a new file will be created, otherwise the existing file will be overwritten.

Note: The node will not run if you are exporting data to an existing Excel file that is open. Close the Excel file before running the Output Excel node.

CAUTION:

Long data types will be stored as text when output to Excel.

To append data to an existing Excel file, you can use the Append Excel node.

To import data from an Excel file, use the Excel File node.

Formatting the output

You can configure the following optional properties to modify the formatting of the output:

HeaderBold - By default, the header row in the output Excel will be displayed in bold text. If you do not want the header row to be bold, set this property to False.
FormatOutput - Select an option from the list to specify the source for the output Excel formatting. You can select from the following options, the default is From BRD:
- From BRD - The data in the output Excel is formatted to match the data types in the input BRD (selecting true also performs this action). This means that boolean, numeric, and date data types will be output in the corresponding Excel types and formatting.
- From template - The data in the output Excel uses the same format as specified in the template file.
- No Formatting - All data is output as text (selecting false also performs this action).
PreserveCellStyles - By default, existing cell styles are preserved in the output Excel file. If you do not want to preserve existing Excel styles, set this property to False. If this property is set to True, or default, and the FormatOutput property is also set to true, this ensures that the data is output in a format which matches the field type, while maintaining cell formatting and styling in an existing cell.

Using an existing Excel file as a template

If you want to use an existing, formatted Excel file as a template, specify the file in the templateInputFile property.

Note: The template file must be in XLS or XLSX format, not in XSLT format. Also, ensure that the template file contains only a very limited amount of data, as using a large file can cause the node to run out of memory.

This file will not be overwritten, but existing data, charts, images, formulas and so on, will be included in the new output file. This allows you to populate an Excel file using a preconfigured template file, where only the data is to be populated. The cells into which the data is to be populated can be referenced in existing formulas and charts in the template file.

Configuring the output of specific worksheets

To define which input pin is written to which Excel worksheet, and where within the worksheet the data is to be output, add XML code to the OutputSpec property.

All indexes are 1-based (first is 1, second is 2). All attributes must have values surrounded by double quotes (") even if they are numeric. Use the following key words when adding the XML code:

<workbook> Top level container. Contains <sheet> elements.

<sheet> Use this tag to refer to individual worksheets within an Excel file. There are various optional and required attributes that can be used within the <sheet> tag to describe which input is mapped to which worksheet, and how the data is to be output:

inputIndex - Specifies which input pin the data is coming from (required).
outputIndex - Specifies which worksheet of the output Excel file the data is to be output to (required).
dataStartRow - Specifies which row in the worksheet will contain the first record of the input (required).
dataStartColumn - Specifies which column in the worksheet will contain the first input field (required).
headerRow - Specifies which row within the worksheet will contain the input metadata (optional). This attribute only takes effect when the a header row is output. If headerRow is not set, and a header row is included in the output, the header row is output to the row prior to that specified in dataStartRow. The first field in the header row is always output to the worksheet column specified by dataStartColumn.
outputHeaderRow - Specifies whether or not to include the input metadata in the output. If set to false, the headerRow attribute is ignored (optional).

For any worksheets that are referenced within the OutputSpec property, the attribute provided overrides the behavior specified by other properties. The output behavior of any inputs that are not referenced in the OutputSpec property is defined by the other node properties.

You want to output data from the first input pin to the second worksheet, and you want to output data from the second input pin to the first worksheet:

<workbook><sheet inputIndex="1" outputIndex="2" dataStartRow="2" dataStartColumn="1"/><sheet inputIndex="2" outputIndex="1" dataStartRow="2" dataStartColumn="1"/></workbook>

CAUTION:

If you are exporting data from multiple inputs to a single worksheet, configure the OutputSpec property to ensure that the data does not overlap in the output. See the following example for more information.

You have two different inputs that you want to combine into a single Excel worksheet. In this example, data from both the first and second input pins is output to the second worksheet, where the data from input 1 is output to the first 3 columns and the data from input 2 is output to the fourth, fifth and sixth columns (in this case, both input data sets consist of three columns of data):

<workbook><sheet inputIndex="1" outputIndex="2" dataStartRow="2" dataStartColumn="1"/><sheet inputIndex="2" outputIndex="2" dataStartRow="2" dataStartColumn="4"/></workbook>

Properties

File

Click the folder icon and browse to the Excel file to which you want to export the data, then click Choose.

To create a new file, click the folder icon and navigate to the folder where you want to output the file, then enter a name for the file in the Filename field and click Choose.

A value is required for this property.

Note: If you are using the server edition, the output file will be created on the Data360 Analyze server. If you wish to open the file, and you don't have access to the file system on the server, you will need to download the file onto your local machine. See Download data files for more details.

HeaderBold

Optionally specify whether the header row in the output Excel should be bold text.

The default value is True.

FormatOutput

Optionally specify the source for the output Excel formatting. Choose from:

From BRD - The data in the output Excel is formatted to match the data types in the input BRD (selecting true also performs this action). This means that boolean, numeric, and date data types will be output in the corresponding Excel types and formatting.
From template - The data in the output Excel uses the same format as specified in the template file.
No Formatting - All data is output as text (selecting false also performs this action).

The default value is From BRD.

PreserveCellStyles

Optionally specify whether to preserve existing Excel styles. If this property is set to true, and the FormatOutput property is also set to true, this ensures that the data is output in a format which matches the field type, while maintaining cell formatting and styling in an existing cell.

The default value is True.

templateInputFile

Optionally click the folder icon and browse to an existing, formatted Excel file to use as a template. Note: The template file must be in XLS or XLSX format, not in XSLT format. Also, ensure that the template file contains only a very limited amount of data, as using a large file can cause the node to run out of memory.

This file will not be overwritten, but existing data, charts, images, formulas and so on, will be extracted and written to the new output file. This allows you to populate an Excel file using a preconfigured template file, where only the data is to be populated. The cells into which the data is to be populated can be referenced in existing formulas and charts in the template file.

OutputSpecExample

The OutputSpecExample property gives an example of the XML code that you can use in the OutputSpec property.

OutputSpec

To define which input pin is written to which Excel worksheet, and where within the worksheet the data is to be output, add XML code to the optional OutputSpec property.

All indexes are 1-based (first is 1, second is 2). All attributes must have values surrounded by double quotes " even if they are numeric. Use the following key words when adding the XML code:

<workbook> Top level container. Contains <sheet> elements.

inputIndex - Specifies which input pin the data is coming from (required).
outputIndex - Specifies which worksheet of the output Excel file the data is to be output to (required).
dataStartRow - Specifies which row in the worksheet will contain the first record of the input (required).
dataStartColumn - Specifies which column in the worksheet will contain the first input field (required).
headerRow - Specifies which row within the worksheet will contain the input metadata (optional). This attribute only takes effect when the a header row is output. If headerRow is not set, and a header row is included in the output, the header row is output to the row prior to that specified in dataStartRow. The first field in the header row is always output to the worksheet column specified by dataStartColumn.
outputHeaderRow - Specifies whether or not to include the input metadata in the output. If set to false, the headerRow attribute is ignored (optional).

TrustedSource

Specify whether or not to trust any Excel file source used by the node.

XLSX and XLSM files are stored in a zip format. Malicious zip files can contain "zip bombs".

To prevent such attacks, this node is configured to error if it detects that the file is decompressing to a much larger size than the compressed version. This can lead to false positives where non-malicious files are detected to contain zip bombs simply due to the way that they are decompressed.

If you receive such errors while attempting to process an Excel file that you know is from a trusted source and cannot contain a zip bomb, set this property to True which will inform the node that the file is safe to process.

The default value is False.

Example data flows

A number of sample Data Flows are available from the Samples workspace, found in the Analyze Directory page.

In the Directory under the /Data360 Samples/Node Examples/ folder, you will find "Reading and Writing to Excel", which shows examples of how to use this node.

Note: Upgrades will overwrite all data flows in the workspace. If you want to make changes to one of the sample data flows, we recommend you create a copy and save it elsewhere, using Save as...

Inputs and outputs

Inputs: in1, multiple optional.

Outputs: out1.