Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Snap type:

Parse

Description:

This Snap reads binary data in XLS and XLSX formats from the specified sheet and provides output document data.

Prerequisites:

None

Support and limitations:
  • Works in Ultra Task Pipelines.
  • Does not read password-protected Excel files and fails with the error: "Failure: Failed to parse input data."
  • Does not support excel uncompressed worksheets larger than 4GB data. Worksheets exceeding this limit fail with the following error:
    "Caused by: 
    java.io.IOException: Zip bomb detected!" 
Account: 

Excel Parser does not use accounts.

Views:


InputThis Snap has exactly one binary input view, where it gets the Excel binary data to be parsed.
OutputThis Snap has exactly one document output view, where it provides the Excel document data stream.
Error

This Snap has at most one document error view and produces zero or more documents in the view. If the Snap fails during the operation, the error view receives an error document containing the fields error, reason, original, resolution, and stacktrace:

Code Block
{ error: "Input was neither a XLS stream, nor a XLSX stream" reason: "Input was neither a XLS stream, nora a XLSX stream" original: {...} 
resolution: "error while reading input data"
stacktrace: "com.Snaplogic.api.ExecutionException:  ...
}


Settings

Label

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Sheet index

Required. Index (0 to n) or name of the sheet which must be parsed.

Note
  • It can use Pipeline Parameters or it can be hardcoded. It cannot use fields from the upstream Snap.
  • If the Sheet name property is specified, this value is overwritten.
  • If the name of the sheet is a number (for example, 2018), use the Sheet name field. 

Example: 0 or Sheet1
Default value: 0

Sheet name

The name of the sheet that must be parsed.

Note
  • It can use Pipeline Parameters or it can be hardcoded. It cannot use fields from the upstream Snap.
  • It overwrites the Sheet index field.

Example: PriceList

Default value: N/A

Start row

Required. Specifies the row number to start reading from the spreadsheet.

Note
This cannot be set to 1 if the Contains headers property is selected.

Example: 1
Default value: N/A

End row

Required. The row number to stop reading in the spreadsheet.
Example: 100 
Default value: N/A

Contains headers

Required. This option specifies whether the input data contains headers. If this option is not selected, the Snap uses the Column names property for the header information when parsing Excel data.

Default value: Unselected

Header row

Specifies the row number in which headers are located in the input data. This property is ignored if Contains headers is false.

If Contains headers is true and Start row is smaller than or equals to Header row, the Start row is reset to Header row+1.

If Header row is empty, it is regarded as a default value of 1.

Default value: 1

Evaluate formulas

When selected, the Snap checks if there are cell formulas to be evaluated and displays the results instead of the raw formulas.

Default value: Unselected

Round dates

Select the check box to round numeric Excel date values to the closest second. You can enable the option when you have numeric excel date values that have milliseconds values and want to round off the milliseconds to the closest second.

For example, 1 Jan 2020 14:29:29.600 is rounded to 1 Jan 2020 14:29:30. 1 Jan 2020 14:29:59.700 is rounded to 1 Jan 2020 14:30:00.

Note

This Snap supports Internal Excel date format or date formatting characters, for example, the default date format for US dates is “m/d/yyyy” (1/27/2016). It does not support the date format with timezone such as UTC at the end, for example, 2020-09-15 00:00:00 UTC.

Default value: Not selected

Cell formatting

Lets you determine whether to format numeral outputs. Valid options are:

  • Formatted: Determines the cell format used for numeric types. For example, Snap converts real numbers to two decimal places when formatted for currency.
  • Unformatted: Supports the unformatted output of numerals.

Default value: Formatted

Column names 

Conditional. Specify a header to map the columns in the Excel sheet. If the Contains header property is not selected and column names are not set, the column names are taken from the Excel data, such as A and B.

Example

  • Item 
  • Name 
  • Description
  • Price 

Default value: N/A

Insert null columns

RequiredThis option specifies whether to insert nulls when rows have missing column data. Applies to only .xls files; the .xlsx files insert nulls by default. 

Default value: Unselected

Number of blank headers after the last header

Specify the number of additional headers that must be added.

Note

The Insert null columns property must be selected for this property to work.

Default value: 0

Multiexcerpt include macro
nameSnap Execution
pageAnaplan Read

Multiexcerpt include macro
nameSnap_Execution_Introduced
pageAnaplan Read

Multiexcerpt include macro
nameTemporary Files
pageJoin

See Also

Excerpt Include
Transform Snap Pack
Transform Snap Pack
nopaneltrue