{"title":"Excel Reader","nodeType":"Source","id":"*I129Ntc6bhMN6EpL","factoryName":"org.knime.ext.poi3.node.io.filehandling.excel.reader.ExcelTableReaderNodeFactory","factoryId":"org.knime.ext.poi3.node.io.filehandling.excel.reader.ExcelTableReaderNodeFactory","path":["IO","Read"],"owner":"knime","ownerAccountId":"account:team:b5f0848f-496d-4e6a-b869-fa3532248db7","description":"<p>This node reads Excel files (xlsx, xlsm, xlsb, and xls format). It can read a single or multiple files at the same time, however reading only one sheet per file. The supported Excel types that can be read in are string, number, boolean, date, and time but not pictures, diagrams, etc.</p>\n        ","shortDescription":"Reads one or more Excel files.","keywords":["Input","Read","Excel","XLSX","XLS","XLSM","XLSB","Spreadsheet"],"deprecated":false,"streamable":true,"tags":["IO","Read","Streamable"],"icon":"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAACXBIWXMAAAsTAAALEwEAmpwYAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAADPSURBVHgBtVFBDoIwEByIiSfrA2xAH0A8C1E+4l+Es+EXHrkazsTg3fAAhfABevOEQFISlNImxEmazna3050uMBHa2jBO9e5J6rxXnvsigQoKqAW0oXOdk2eWtUsUizDjZGtZeKQpzkEAVFV3JkPPAqUUtyRp+d5xUBRFV2jQ1XgH3yCE9OL4+P6pcS/zvkDzehiGYGWJaxS1FhhjULLAP2xjmhiKRRaUx8gFXcdGnNw73ozRl11ekoUwp4kSB3un1BnGBDjGuI6J+J8FVXwArGpkXrM0Y1EAAAAASUVORK5CYII=","views":[],"inPorts":[],"outPorts":[{"objectClass":"org.knime.core.node.BufferedDataTable","color":"000000","dataType":"Table","optional":false,"name":"File Table","description":"The data table read from the Excel file."}],"dynInPorts":[{"groupName":"File System Connection","groupDescription":"The file system connection.","types":[{"objectClass":"org.knime.filehandling.core.port.FileSystemPortObject","color":"178ba5","dataType":"File System"}]}],"dynOutPorts":[],"additionalSiteInformation":{"url":"https://update.knime.com/analytics-platform/5.12","trusted":true,"enabledByDefault":true,"name":"KNIME Analytics Platform 5.12 Update Site","version":"5.12.0"},"dialog":[{"sectionName":"Options","fields":[{"name":"Mode","description":"Determine the mode how to select one or multiple files.\n<ul><li><b>File</b> : Select a single file.</li><li><b>Files in folders</b> : Select a folder and apply filters to select files within it.</li></ul>","optional":false},{"name":"Source","description":"The path to the file or folder to select.","optional":false},{"name":"Include Subfolders","description":"Whether to include subfolders when selecting multiple files within a folder.","optional":false},{"name":"Filter by file extension","description":"Enable filtering files by their extension (e.g. &#39;xlsx;xlsm&#39;).","optional":false},{"name":"File extensions","description":"Semicolon-separated list of file extensions to include (e.g. &#39;xlsx;xlsm;xls&#39;). Case-insensitive unless &#39;Case sensitive (extensions)&#39; is enabled.","optional":false},{"name":"Case sensitive (extensions)","description":"Treat the entered extensions as case sensitive when matching.","optional":false},{"name":"Filter by file name","description":"Enable filtering by file name pattern with wildcards or regular expression.","optional":false},{"name":"File name filter pattern","description":"Pattern for file name filtering. With type &#39;Wildcard&#39;, use &#39;*&#39; and &#39;?&#39;. With type &#39;Regex&#39;, enter a Java regular expression.","optional":false},{"name":"File name filter type","description":"Choose how to interpret the file name pattern.\n<ul><li><b>Wildcard</b> : Enable using &#39;*&#39; and &#39;?&#39; as wildcards.</li><li><b>Regular Expression</b> : Enable using a Java regular expression.</li></ul>","optional":false},{"name":"Case sensitive (names)","description":"Make file name filtering case sensitive.","optional":false},{"name":"Include hidden files","description":"Include hidden files in the selection.","optional":false},{"name":"Include special files","description":"Include special file types (workflows etc).","optional":false},{"name":"Filter by folder name","description":"Enable filtering of folders by name pattern before descending into them.","optional":false},{"name":"Folder name pattern","description":"Pattern for folder name filtering. Note that the pattern is applied to the path relative to the specified root folder. Use &#39;*&#39; and &#39;?&#39; with filter type &#39;Wildcard&#39;. With type &#39;Regex&#39;, enter a Java regular expression.","optional":false},{"name":"Folder name filter type","description":"Choose how to interpret the folder name pattern.\n<ul><li><b>Wildcard</b> : Enable using &#39;*&#39; and &#39;?&#39; as wildcards.</li><li><b>Regular Expression</b> : Enable using a Java regular expression.</li></ul>","optional":false},{"name":"Case sensitive (folders)","description":"Make folder name filtering case sensitive.","optional":false},{"name":"Include hidden folders","description":"Descend into folders that are hidden (if they otherwise pass filters).","optional":false},{"name":"Follow symlinks","description":"Follow symbolic links while traversing folders (only relevant when selecting a folder).","optional":false},{"name":"File encryption","description":"Allows you to specify a password to decrypt files that have been protected with a password via Excel. Files without password protection can also be read if you set a password.\n<ul><li><b>No encryption</b> : Files are not password protected.</li><li><b>Password</b> : Files are protected with a password.</li></ul>","optional":false},{"name":"Password","description":"The password to decrypt the Excel file.","optional":false},{"name":"Select sheet","description":"Choose which sheet to read from the Excel file. The order of the sheets is the same as displayed in Excel (i.e. not necessarily a lexicographic order).\n<ul><li><b>First sheet with data</b> : The first sheet of the selected file(s) that contains data will be read in. Containing data means not being empty. If all sheets of a file are empty, an empty table is read in.</li><li><b>By name</b> : The sheet with the selected name will be read in. If reading multiple files, the sheet names of the first file are shown and the node will fail if any of the other files does not contain a sheet with the selected name.</li><li><b>By position</b> : The sheet at the selected position will be read in. If reading multiple files, the node will fail if any of the files does not contain a sheet at the selected position. The position starts at 0, i.e. the first sheet is at position 0.</li></ul>","optional":false},{"name":"Sheet name","description":"The name of the sheet to read.","optional":false},{"name":"Sheet position","description":"The position (starting at 0) of the sheet to read. The maximum position that can be selected depends on the number of sheets available in the first read file.","optional":false},{"name":"Define read area","description":"Choose whether to read the entire sheet or a custom area.\n<ul><li><b>Whole sheet</b> : All the data contained in the sheet is read in. This includes areas where diagrams, borders, coloring, etc. are placed and could create empty rows or columns.</li><li><b>Range by column and row</b> : Only the data in the specified area is read in. Both start and end columns/rows are inclusive. By leaving a field empty, the start or end of the area is not restricted.</li></ul>","optional":false},{"name":"Read from column","description":"The first column to include specified as label (&#34;A&#34;, &#34;B&#34;, etc.) or number (starting at 1). Leave empty to read starting at the first column.","optional":false},{"name":"Read to column","description":"The last column to include specified as label (&#34;A&#34;, &#34;B&#34;, etc.) or number (starting at 1). Leave empty to read until the last column.","optional":false},{"name":"Read from row","description":"The first row number to include (starting at 1). Leave empty to read starting from the first row.","optional":false},{"name":"Read to row","description":"The last row number to include (starting at 1). Leave empty to read until the last row.","optional":false},{"name":"Define Column Names","description":"Choose how to define column names for the table.\n<ul><li><b>Use custom row</b> : Use a specific row as column names.</li><li><b>Excel headers (A, B, C)</b> : Use Excel column headers (A, B, C, ...) as column names.</li><li><b>Enumerate columns (Col1, Col2...)</b> : Use enumerated column names (Col1, Col2, ...) as column names.</li></ul>","optional":false},{"name":"Header as in row","description":"The row number (1-based) containing column names.","optional":false},{"name":"Prefix for empty headers","description":"Prefix to use for empty column headers.","optional":false},{"name":"Suffix for empty headers","description":"Choose the suffix format for empty column headers.\n<ul><li><b>A, B, ...</b> : Use Excel column headers (A, B, C, ...) as column name suffix for empty columns.</li><li><b>1, 2, ...</b> : Use enumerated indices (1, 2, ...) as column name suffix for empty columns.</li></ul>","optional":false},{"name":"Define RowID","description":"Choose how to define row IDs for the table.\n<ul><li><b>Enumerate rows (Row1, Row2, ...)</b> : Generate row IDs by enumerating rows.</li><li><b>Use custom column</b> : Use a specific column as row IDs.</li></ul>","optional":false},{"name":"RowID as in column","description":"The column (e.g., A, B, C or 1, 2, 3) to use as row IDs. The RowIDs in the selected column must be unique, otherwise the execution fails.","optional":false},{"name":"Skip empty columns","description":"If checked, empty columns of the sheet will be skipped and not displayed in the output. Whether a column is considered empty depends on the <i>Column and Data Type Detection</i> settings: If the cells of a column for all scanned rows were empty, the column is considered empty, even if the sheet contains a non-empty cell after the scanned rows. Removing the limit of scanned rows ensures empty and non-empty columns being detected correctly but also increases the time required for scanning.","optional":false},{"name":"Skip hidden columns","description":"If checked, hidden columns of the sheet will be skipped and not displayed in the output.","optional":false},{"name":"Skip empty rows","description":"If checked, empty rows of the sheet will be skipped and not displayed in the output.","optional":false},{"name":"Skip hidden rows","description":"If checked, hidden rows of the sheet will be skipped and not displayed in the output.","optional":false},{"name":"Use Excel 15 digits precision","description":"If checked, numbers are read in with 15 digits precision which is the same precision Excel is using to display numbers. This will prevent potential floating point issues. For most numbers, no difference can be observed if this option is unchecked.","optional":false},{"name":"Replace empty strings with missing values","description":"If checked, empty strings (i.e. strings with only whitespaces) are replaced with missing values. This option is also applied to formulas that evaluate to strings.","optional":false},{"name":"Reevaluate formulas in all sheets","description":"If checked, formulas are reevaluated and put into the created table instead of using the cached values. This can cause errors when there are functions that are not implemented by the underlying Apache POI library. Note: Files with xlsb format do not support this option. For xlsx and xlsm files, reevaluation requires significantly more memory as the whole file needs to be kept in memory (xls files are anyway loaded completely into memory).","optional":false},{"name":"On error insert","description":"Choose how to handle formula evaluation errors.\n<ul><li><b>String value</b> : Insert the given String value. Inserting a string value causes the entire column to become a string column in case an error occurs.</li><li><b>Missing value</b> : Insert a Missing value. Inserting a missing value is type innocent, but also unobtrusive.</li></ul>","optional":false},{"name":"String","description":"The string value to insert into a cell with a formula evaluation error.","optional":false},{"name":"If schema changes, ignore schema change (deprecated)","description":"If set, the node tries to ignore the changes and outputs a table with the old table specification. This option is deprecated. Once unchecked, it will disappear and cannot be selected again, as it may lead to invalid data in the resulting table.","optional":false},{"name":"If schema changes","description":"Specifies the node behavior if the content of the configured file/folder changes between executions, i.e., columns are added/removed to/from the file(s) or their types change. The following options are available:\n<ul><li><b>Fail</b> : If set, the node fails if the column names in the file have changed. Changes in column types will not be detected.</li><li><b>Use new schema</b> : If set, the node will compute a new table specification for the current schema of the file at the time when the node is executed. Note that the node will not output a table specification before execution and that it will not apply transformations, therefore the transformation tab is disabled.</li></ul>","optional":false},{"name":"Limit scanned rows","description":"If you limit the number of scanned rows, only the specified number of input rows are used to analyze the file (i.e. to determine the column types). This option is recommended for long files where the first n rows are representative for the whole file.","optional":false},{"name":"Maximum number of scanned rows","description":"The maximum number of rows to scan for determining column types.","optional":false},{"name":"Fail if schemas differ between multiple files","description":"If checked, the node will fail if multiple files are read via the &#34;Files in folder&#34; option and not all files have the same table structure, i.e. the same columns.","optional":false},{"name":"How to combine columns","description":"Specifies how to deal with reading multiple files in which not all column names are identical.\n<ul><li><b>Fail if different</b> : The node will fail if multiple files are read and not all files have the same column names.</li><li><b>Union</b> : Any column that is part of any input file is considered. If a file is missing a column, it is filled up with missing values.</li><li><b>Intersection</b> : Only columns that appear in all files are considered for the output table.</li></ul>","optional":false},{"name":"Append file path column","description":"Select this box if you want to add a column containing the path of the file from which the row is read. The node will fail if adding the column with the provided name causes a name collision with any of the columns in the read table.","optional":false},{"name":"Enforce types","description":"Controls how columns whose type changes are dealt with. If selected, the mapping to the KNIME type you configured is attempted. The node will fail if that is not possible. If unselected, the KNIME type corresponding to the new type is used.","optional":false},{"name":"Transformations","description":"Use this option to modify the structure of the table. You can deselect each column to filter it out of the output table, use the arrows to reorder the columns, or change the column name or column type of each column. Note that the positions of columns are reset in the dialog if a new file or folder is selected. Whether and where to add unknown columns during execution is specified via the special row &lt;any unknown new column&gt;. It is also possible to select the type new columns should be converted to. Note that the node will fail if this conversion is not possible e.g. if the selected type is Integer but the new column is of type Double.","optional":false}]}],"links":[],"kudos":[{"accountId":"account:user:399cb7b4-a446-4d1b-a64a-b697eb8578a4","user":"marcelw","date":"2023-08-08T09:33:46+00:00"},{"accountId":"account:user:655e0fc3-ee54-4e0e-aa79-a060fd9e85ff","user":"nils_borsik","date":"2024-04-08T15:18:28+00:00"},{"accountId":"account:user:1a89b8de-a1af-46a8-a907-d520758b3d19","user":"lada","date":"2021-02-24T14:04:49+00:00"},{"accountId":"account:user:6b4a0550-6d91-4729-8242-e0a6f3b68e2a","user":"grace_olatunji","date":"2026-02-17T09:59:05+00:00"}],"kudosCount":4,"bundleInformation":{"bundleVersion":"5.12.0.v202604281119","bundleName":"KNIME POI (Excel) Plug-in","bundleSymbolicName":"org.knime.ext.poi3","bundleVendor":"KNIME AG, Zurich, Switzerland","featureSymbolicName":"org.knime.features.ext.poi","featureName":"KNIME Excel Support","featureVersion":"5.12.0.v202605041104","featureVendor":"KNIME AG, Zurich, Switzerland","extensionId":"*ei_rc2H2e57n_qvV"},"frequentSuccessors":[]}