Snap type:
Parse
Description:
This Snap reads binary data in XLS and XLSX formats from the specified sheet and provides output document data.
None
- 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!"
Excel Parser does not use accounts.
Snap type: | Parse | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap reads binary data in XLS and XLSX formats from the specified sheet and provides the output document data.
| |||||||||||||
Prerequisites: | None | |||||||||||||
Support and limitations: |
| |||||||||||||
Account: | Excel Parser does not use accounts. | |||||||||||||
Views: |
| |||||||||||||
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.
Example: 0 or Sheet1 | |||||||||||||
Sheet name | The name of the sheet that must be parsed.
Example: PriceList Default value: N/A | |||||||||||||
Start row | Required. Specifies the row number to start reading from the spreadsheet.
Example: 1 | |||||||||||||
End row | Required. The row number to stop reading in the spreadsheet. | |||||||||||||
Contains headers | Round dates |
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. |
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
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
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
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
Note |
---|
If an Excel file generated by the Excel Formatter Snap is passed to the Excel Parser Snap where formula strings have been written as formulas, the Snap will not display the result of the formula even if the checkbox is selected. For such files, open the file in any spreadsheet application (such as Microsoft Excel or LibreOffice Calc), select the option to execute the full workbook, save the file, and then use this saved file in the Excel Parser Snap. |
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.
Default value: Not selected
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. |
Custom Locale
Select a user-defined locale that the Snap should use to format numbers, instead of the default locale.
For example, if the default locale that is configured in the Snaplex uses “,” (comma) to separate the integer and fractional parts, you can select any of the user-defined locale that uses. (dot) to format numbers.
If you leave this field empty, then the default locale that is configured on the Snaplex is used. Click the Suggestion icon to view all the available locales on the Snaplex.
Default Value: None
Example: English (Jamaica)
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
Required. This 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
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 | ||||
---|---|---|---|---|
|
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Multiexcerpt include macro name Temporary Files page Join
See Also
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|