Icon

Reading_​Excel_​Files_​wf

Read an XLS file

This workflow demonstrates how to read an XLS file using the XLS Reader node. It contains three workflows.
The goal of each workflow is to read accounting data from a restaurant business for all months in 2016 and to calculate the total revenues for each month. Each workflow is different, depending on how the data are stored in the .xls file.

Workflow 1: All accounting data are stored in one sheet of a single Excel file. One single Excel Reader node is enough to read all data.
Workflow 2: Reads data from all sheets in an Excel file using the Read Excel Sheet Names node, a loop, and the Excel Reader node
Workflow 3: Reads data from all sheets of all Excel files present in a folder. Here we use the List Files node, we loop on the list of files, we use the Read Excel Sheet Names node, we loop on the list of sheets, and we read the data from each sheet.

URL: Data Access in KNIME: Excel Reader https://youtu.be/goo5ClHlfT8

Iterate through sheets in an Excel file, and replace all cells in column B with 'replacement' when the cell is equal to 5.

All accounting data are stored in one sheet of a single Excel file.

We can manually go in to the settings and look at different portions of the excel files.

We can instead look through all of the individual sheets automatically and collect the output in one table (this happens automatically).

Accounting data are stored in a different file for each month. So we have both multiple files, and multiple sheets within each file.

Here we need to create a 'nested loop' in order to handle this.

Outer Loop

Inner Loop

Iterating Through Excel Files and Sheets

Convert Knime RowID to Excel row numbers

To do this we:

  • Remove the prefix 'Row' from it

  • Convert the 'string' type to a numerical type

  • Add +1 to each number, since Knime starts at zero, but Excel starts at one

Total revenue by month
GroupBy
to DateTime
String to Date&Time (deprecated)
Date&Time Part Extractor
Loop End
Keep onlyexcel_row_number
Column Filter
List Files/Folders
Remove thestring "Row"from each RowID
String Manipulation
Hint: Configure sheetin 'Flow Variables' tabunder settings > sheet_nameIn 'Data Area' tab, uncheckColumn Names > Use Values in Rowsince we want to preserve theoriginal row IDs in this case
Excel Reader
We want to replaceitems in column Bwhen it equals 5
Row Filter
Add 1 to each rownumber to make italign with Excel row nums. Also select 'Convert toInt', so it isn't adecimal number.
Math Formula
Excel Reader
Append the 'B'to excel_row_numberto get the full cell ref
String Manipulation
Turn the RowIDinto an actualcolumn. (Knimetreats it speciallyso we copy it explicitly)
RowID
Loop End
Convert it to a numerical type(otherwise the nextmath formula won'twork)
String to Number
Read Excel Sheet Names
Read Excel Sheet Names
Hint: Configure sheetin 'Flow Variables' tabunder settings > sheet_name
Excel Reader
String Manipulation
Excel Reader
Table Row to Variable Loop Start
Loop End
Total revenue by month
GroupBy
Table Row to Variable Loop Start
Date&Time Part Extractor
Table Row to Variable Loop Start
update cells
Excel Cell Updater
to DateTime
String to Date&Time (deprecated)
Date&Time Part Extractor
Total revenue by month
GroupBy
to DateTime
String to Date&Time (deprecated)
Variable Loop End
get all sheet names, create file flow variable
Read Excel Sheet Names
go through each sheet
Table Row to Variable Loop Start

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: oole
Created at: 2016-05-02
On NodePit since: 2026-06-25
Last update: 2026-06-26
Created with KNIME version: v5.8.0
Tags: readingXLSexcelloopdatadata accesssheetmultiple sheetmultiple filesloop reading

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!