Excel
This parser works similar to the CSV parser whereas the Excel parser reads the datasets not only from the text via the separator for rows and columns, but reads directly from the rows and columns of an Excel worksheet within a workbook. Supported formats: Excel from version 97 (XLS, XLSX, XLSB, XLTX, XLTM, XLSM, XML).
Settings
(1) Sheets: Fixed index. Numeric specification of the worksheet to be used in the workbook, starting with 1.
Use all Excel sheets. All existing worksheets are attached to each other and processed like a single worksheet.
Defined setting. Specification of the worksheet(s) by worksheet index (negative values address from the end, i.e. -1 for the last worksheet), worksheet name or mixed (separated by commas). The worksheets are read in the specified order. Variables in the form @MSG_CALL_Name@ are resolved. Examples: So -1,-2 for the last two data sheets, for example, or dataSheet,3,-1 as a mixture. Use prefix regex: for regular expressions. Example: regex:^(January|February|September)( 20[0-9][0-9])$
(2) Insert into each line at start: You can prefix the worksheet name or index as the first column. So, for example, instead of just line A;B;C from the first worksheet Table1, the parser would deliver the line Table1;A;B;C or 1;A;B;C.
(3) Recalculate formulas when opening: If the checkbox is not set, then formulas are not recalculated. Can be used if in exceptional cases the recalculation of complicated formulas does not work.
(4) Skip hidden rows/columns: Hidden rows/columns (see corresponding Excel functionality) can be skipped.
(5) Same matchcodes exist: Indicates whether identical record types occur multiple times (nested), but do not belong to the same node. Example:
DatasetADatasetB...DatasetC...DatasetDDatasetCDatasetDDatasetC...The first C dataset should be parsed into a separate node, the subsequent C datasets each in a D/C node. So the CSV parser will work similar to an EDIFACT parser here, i.e. it goes through the structure from top to bottom and remembers where it is. Once the lower D/C node has been entered, nothing will be inserted into the upper C node anymore. Note: The set checkbox also affects when the parser creates a new record.
(6) Execute semantic checks: Incoming files can be checked with semantic rules. See section Semantic check .
See also
See section Supported Excel Functions as well as the system variables VAR_SYS_EXCEL_SHEET_NAMES and VAR_SYS_LINES.