From Excel to KNIME

Looking for an Excel alternative? Are you an experienced Excel user and want to start using KNIME Analytics Platform? Discover how to transition from Excel to KNIME and gain efficiency and effectiveness through automation in a free, low-code analytical environment. It’s sometimes difficult to switch from one software tool to another. This easy to follow guide can help you transition from Excel to KNIME. It is the perfect starting point as it maps the most commonly used Excel functions and techniques to their KNIME equivalents, taking you through the steps you’d take in Excel and showing you how they can be done in KNIME Analytics Platform. Find out, for example, how data reading, filtering, sorting, pivoting, math formulas, and commonly used functions such as vlookup are handled in KNIME. No previous knowledge of KNIME is required.

The PDF 'from Excel to KNIME' is provided free of charge by KNIME Press. Click on the PDF icon below and complete the form on their website to receive your complimentary copy.

Tools to Master KNIME

Input/Output Tools

Index 1/3

Open an Excel File Open a CSV or Text File Combine Multiple Files into One Combine Multiple Sheets into One Save an Excel File Add a Sheet to an Excel File Transform Data Types Connect to Databases

Append & Join Tools

Append/Union Data Join Data (VLOOKUP)

Tools to Master KNIME

Filter & Transformation Tools

Index 2/3

Filter Rows on Specific Values Filter Rows using Specific Rules Remove Duplicate Rows Sort Rows Filter Columns Reorder Columns Rename Columns Number to String and String to Number Table Manipulator Math Formulas String Formulas Cell Splitter aka Text to Columns

Output Formatting

Index 3/3

Excel Table Formatting

Data Aggregation

Pivot Tables Group & Aggregate Data Unpivot Tables

Tools to Master KNIME

To open an Excel file you can either double click the file or open Excel, go to “File” in the top menu, select “Open…” and then browse to the file you want to open.

Open an Excel File

Input/Output

Tools to Master KNIME

The Excel Reader node reads a single sheet of an Excel file. Similar to Excel you first have to define the file path. Next you can select the sheet. The additional setting options enable you to define whether the table has column headers and/or row IDs and to specify which part of the sheet you want to read.

Open an Excel File

Input/Output

Tools to Master KNIME

To open a CSV file click “File” in the top menu and select “Import”. Select CSV file and then choose the file you want to open. Fill in all necessary options, such as the separating character, to correctly read the file.

Open a CSV or Text File

Input/Output

Tools to Master KNIME

The CSV Reader node reads various text based files, e.g. CSV files. In the configuration window you can set all the necessary options, such as separating character, to correctly read the file.

Open a CSV or Text File

Input/Output

Tools to Master KNIME

If you have a folder containing multiple files of the same type (e.g. CSV), you can open them all at the same time in different Excel instances. Follow the instruction from the previous page and select all the files you want to open. To move data tables together into one single sheet, you have to proceed manually using copy and paste. Note: Before copying and pasting, ensure that all files have the same column order.

Combine Multiple Files into One

Input/Output

Tools to Master KNIME

Input/Output

Many reader nodes in KNIME Analytics Platform support reading multiple files in a folder and combining them into one table. For example the CSV Reader can read multiple text-based files. To read multiple files select the “Mode” “Files in folder” and specify the folder using the “Browse” button. A little note tells you how many files are currently selected. In case you don’t want to read all the files in a folder, click the button “Filter options” to specify which files should be included according to their file extension and/or name. Lastly you can also include files from subfolders by activating the checkbox “Include subfolders”.

Note: In the Transformation tab you can define whether you want to use the union or the intersection of the columns from the different tables

Combine Multiple Files into One

Tools to Master KNIME

To move data from multiple Excel sheets into one Excel sheet you proceed manually, using copy and paste. Note: Before copying and pasting, ensure that all files have the same column order.

Combine Multiple Sheets into One

Input/Output

Tools to Master KNIME

Input/Output

With a simple loop you can read all sheets of an Excel file automatically. The Read Excel Sheet Names node creates a list of all sheet names. The loop (the part in between the blue nodes) reads one sheet of the Excel file at each iteration. Therefore, at each iteration the Table Row to Variable Loop Start node creates a flow variable with the sheet name as its value. This flow variable is used in the Excel Reader node to control the sheet selection. The Loop End concatenates the content from the different tables.

Combine Multiple Sheets into One

Tools to Master KNIME

To save the sheet of an Excel file you have different options: • Click File->Save As… and define the output location • Or press Shift+Ctrl+S and define the output location

Save an Excel File

Input/Output

Tools to Master KNIME

The Excel Writer node writes or append the input data table into a sheet in an Excel file, in either xls or xlsx format. In the configuration window you can set the output location and sheet name. The additional setting options enable you to overwrite an existing file and to define whether you want to write the column headers / row ids into the first row / column of your Excel sheet. Note 1: To write multiple tables into different sheets you can add dynamic input ports and define a sheet name for each input table. Note 2: Select “append” for the settings “Write options” and “If sheet exists” to append the input data after the last row of an existing sheet.

Save an Excel File

Input/Output

Tools to Master KNIME

To add a new sheet to an existing Excel file you have to click the plus below the table, next to the already existing sheets. To add a table from another Excel file you can copy & paste the table manually.

Add a Sheet to an Excel File

Input/Output

Tools to Master KNIME

Add a Sheet to an Excel File

Input/Output

The Excel Writer node can also add sheets to an existing Excel File. In the configuration window you can set the location of the existing Excel file and select “append” for the setting option “If exists”. In the “Sheets” part you can define the new sheet name and whether the node should overwrite or fail in case a sheet with the defined sheet name exists already

Tools to Master KNIME

The screenshot shows you the different available default datatypes in Excel, which can be mapped to the mathcing data types in KNIME.

Transform Data Types

Input/Output

Tools to Master KNIME

Transform Data Types

Input/Output

When reading a file, KNIME Analytics Platform tries to autodetect the correct data type. If you want to change this during reading you can use the Transformation tab of the Excel Reader node. Note 1: The Transformation tab is also available in other reader nodes. Note 2: In the Transformation tab you can also rename, remove, and change the order of columns. Note 3: To change the data type later in the workflow you can use one of the following nodes: String to Number, Number to String, or Table Manipulator.

Tools to Master KNIME

You can connect Excel to a database, such as Azure, and then import data and create tables and charts based on values in the database.

Connect to Databases

Input/Output

Tools to Master KNIME

Connect to Databases 1/2

Input/Output

A number of database connector nodes are available to connect to the most commonly used databases. However, the DB Connector node allows you to connect to all JDBC compliant databases. Furthermore, KNIME Analytics Platform also allows you to connect and read from many other sources and file system, e.g. Amazon S3, Microsoft SharePoint Online, Google Big Query, Snowflake and Databricks to name just a few. More details in the KNIME File Handling Guide:

Tools to Master KNIME

Connect to Databases 2/2

Input/Output

There are more database nodes to help build a SQL query for in database processing. You can use them in between the DB Table Selector and the DB Reader node.

Reading data from a database follows three steps: connect, select, and extract. The workflow is built step by step with a Connector, a DB Table Selector, and a DB Reader node. Database nodes simply build the SQL query, they do not execute it. Only the final node, e.g. the DB Reader node, executes the SQL query and extracts the data.

Tools to Master KNIME

To append data to a table select manually the area you want to append and copy and paste the content below the last row of table. Note: Before copying and pasting ensure that all tables have the same column structure and that you don't create copies of the column headers.

Append/Union Data

Append & Join Tools

Tools to Master KNIME

Append/Union Data

Append & Join Tools

The Concatenate node writes two or more tables below each other. In other words, you append rows of one table to the other. Often this is referred to as a union of tables. Note 1: The order of the column doesn’t have to be the same in KNIME. Note 2: You can decide whether you want the intersection or the union of the columns. Note 3: The Concatenate node has the option to add more input ports, to concatenate more than two tables. To add an additional input port, click on the three dots on the lower left of the node and select “Add input port”.

Tools to Master KNIME

The VLOOKUP function is used for different tasks. The most common uses are: 1. Look up a certain value, e.g. the price of a certain product. 2. Join columns based on a primary key (look up value), e.g. join product information based on the product ID. An alternative function for the second task is INDEX MATCH.

Join Data (VLOOKUP)

Append & Join Tools

Tools to Master KNIME

Join Data

Append & Join Tools

To look up a certain value, e.g. the price of a certain product. You join columns based on a joining column, e.g. join product information based on Product ID. Note 1: The “Joining Column” is the look up value column. Note 2: A Join differs from VLOOKUP as for every match on the 'Joining Column' a row will be created so the number of rows in the resulting table might increase contrary to Excel. Furthermore, depending on the type of join, mismatches might be ignored leading to less rows in the resulting table.

Tools to Master KNIME

Filter Data

Filter & Transform Tools

Based on certain criteria you can filter out rows or you select only the columns you desire for further processing. Note: Your full original table is still available at the output port of the CSV Reader node.

Tools to Master KNIME

Filter Rows on Specific Values

Filter & Transform Tools

Select the values you would like to delete from the drop down menu. To remove rows, select the rows you want to delete, right click and select “Delete Rows”. Note: Unless you keep a copy of the original sheet or file, deleted rows are removed permanently.

To filter data select a random cell in the data table, go to the Home tab, click the “Sort& Filter” button and select “Filter”.

Tools to Master KNIME

Filter Rows on Specific Values

Filter & Transform Tools

In KNIME there is no difference between filtering and removing rows, as the original table is not deleted and is still available at the output port of the previous node. The Row Filter node filters the table based on a filter criteria, e.g. by including / excluding all rows with a certain value in the filter column. Note 1: On the left you can choose whether you want to include or exclude the rows with the matching value Note 2: If you only interested in the rows with one specific value you can use the Row Filter node. Note 3: Further filter options are available, e.g. on a numerical range, filter rows by row number or row ID, or missing values only. Note 4: If you want to include rows based on different values or criteria you can use the Rule-based Row Filter.

Tools to Master KNIME

Filter Rows using Specific Rules

Filter & Transform Tools

In KNIME there is no difference between filtering and removing rows, as the original table is not deleted and is still available at the output port of the previous node. The Rule-based Row Filter node filters rows in or out according to a set of rules. To include / exclude rows with two different values you can use the following expression: $Column_Name$ = “Value 1” OR $Column_Name$ = “Value 2”=> TRUE Note 1: At the bottom of the configuration window you can choose whether you want to include or exclude TRUE matches. Note 2: Columns are given by their name surrounded by $. Add them to the expression frame by double clicking a column name in the Column List. Note 3: Different rows in the expression frame work like an OR conjunction.

Tools to Master KNIME

Remove Duplicate Rows

Filter & Transform Tools

Select the range of cells that might have duplicates that you want to remove. Go to the Data tab and select “Remove Duplicates” Select the columns for duplicate detection and click “OK to remove the duplicate values.

Tools to Master KNIME

Remove Duplicate Rows

Filter & Transform Tools

The Duplicate Row Filter node detects and treats duplicates. The default treatment removes duplicate rows like in Excel. The columns in the “Include” frame correspond to the selected columns for duplicate detection in Excel. This means rows that have the same values in these columns are detected as duplicates. Note 1: In the “Advanced” tab you can change the treatment for duplicates, for example to keep duplicate rows and to add a column showing which of the rows are unique, chosen, or duplicates.

Tools to Master KNIME

Sort Rows by Multiple Columns

Filter & Transform Tools

To sort rows by multiple columns, select the columns by which you want to sort. Then click on Sort&Filter and select “Custom Sort”.

Tools to Master KNIME

Sort Rows by Multiple Columns

Filter & Transform Tools

With the Sorter node you can sort by one or more columns in either ascending or descending order. Note: You can add as many key-columns as you want by clicking the “+ Add Rule” button.

Filter Columns in Excel

Filter & Transform Tools

To remove columns in Excel just select and delete the superfluous columns.

With the Column Filter node you can delete superfluous columns. Remember that the full data table will be still available at the output port of the previous node and for calculations you run upstream. Note 1: You can use the arrow buttons in the middle to move columns from the Include to the Exclude frame and vice versa. Note 2: You can use the Wildcard/Regex Selection to automatically remove columns by a name patter. Note 3: You can use the Type Selection to automatically remove columns by data type.

Filter & Transform Tools

Filter Columns in KNIME

Tools to Master KNIME

Filter Columns

Filter & Transform Tools

With the Column Filter node you can delete superfluous columns. Remember that the full data table will be still available at the output port of the previous node and for calculations you run upstream. Note 1: You can use the arrow buttons in the middle to move columns from the Include to the Exclude frame and vice versa. Note 2: You can use the Wildcard/Regex Selection to automatically remove columns by a name patter. Note 3: You can use the Type Selection to automatically remove columns by data type.

Reorder Columns in Excel

Filter & Transform Tools

Select the column header of the column you want to move, then press ctrl + shift + down arrow, and click and hold the green outline of the column you want to move. Finally, drag your column to the desired position

To resort columns you can use the Column Resorter node. Define the desired order of the columns by selecting the one you want to move and using the “Actions” buttons on the right.

Filter & Transform Tools

Reorder Columns in KNIME

Reorder Columns

Filter & Transform Tools

To reorder columns: • Select the column header of the column you want to move • Press ctrl + shift + down arrow • Click and hold the green outline of the column you want to move • Drag your column to the desired position

Tools to Master KNIME

Reorder Columns

Filter & Transform Tools

To resort columns you can use the Column Resorter node. Define the desired order of the columns by selecting the one you want to move and using the “Actions” buttons on the right.

Rename Columns in Excel

Filter & Transform Tools

To rename a column just click on the column header and change the cell value.

To rename columns you can use the Column Rename node. Double click the column you want to rename, activate the checkbox “Change” and define the column header in the textbox.

Filter & Transform Tools

Rename Columns in KNIME

Tools to Master KNIME

Rename Columns

Filter & Transform Tools

To rename a column just click on the column cell and change the cell value.

Tools to Master KNIME

Rename Columns

Filter & Transform Tools

To rename columns you can use the Column Rename node. Double click the column you want to rename, activate the checkbox “Change” and define the column header in the textbox.

Tools to Master KNIME

String to Number / Number to String

Filter & Transform Tools

To change the data type of a column or a cell, select it and go to the home tab. Then use the drop down menu in the middle.

Tools to Master KNIME

Rename Columns

Filter & Transform Tools

To change the data type of a column you can use either the String to Number or the Number to String nodes. In the String to Number node you can choose between different numerical types, e.g Double, Integer, and Long.

Tools to Master KNIME

Table Manipulator

Filter & Transform Tools

The Table Manipulator node allows you to perform a lot of the described transformations in one node. You can use it to: • Concatenate multiple tables (after adding dynamic input ports to the node) • Filter, resort, and rename columns • Change the data type of column

remove columns

sort columns

rename columns

change data type

concatenate columns

Math Formulas

Filter & Transform Tools

Here is a list of frequently used math formulas in Excel. You will find a translation into the KNIME formulas on the next page: • SUM • AVERAGE • MEDIAN • SUMPRODUCT • ABS • SUMIF • ROUND • ROUNDUP • ROUNDDOWN

Tools to Master KNIME

Math Formulas

Filter & Transform Tools

The Unpivoting node is configured via one tab: • In the upper section, “Value columns” defines the column(s) to unpivot, aka column selection in Excel. • In the lower section, “Retained columns” defines the column(s) that remains unchanged, aka unselected columns in Excel. Note: the Unpivoting node requires selecting at least one column in the “Value columns” section

String Formulas

Filter & Transform Tools

2. To find and replace a certain value you have to go to “Edit -> Find -> Replace…” to open the dialog above. In the dialog that opens, you can define the value you want to replace and the value you want to replace it with. This overwrites the original values.

1. The function CONCATENCATE( Cell1, Cell2,..) combines different texts into one cell.

Tools to Master KNIME

String Formulas

Filter & Transform Tools

The String Manipulation node manipulates columns of type String based on the defined expression. A lot of different functions are available. 1. CONCATENATE (text1, text2,..) from Excel join(col1, col2 ,…) or join(col1, “your own string”,…) Note 1: You can either join the values of different columns row wise, or add also another String value to a column. Note 2. Find & Replace Replace(string, search, replace) Note 1: “string” corresponds to the column where you want replace something. Note 2: To perform the same manipulation on multiple string columns you can use the String Manipulation (Multi Column) node

String Manipulation (Multi Column)

Tools to Master KNIME

Cell Splitter aka Text to Columns

Filter & Transform Tools

This node uses a user-specified delimiter character to split the content of a selected column into parts. It appends either a fixed number of columns to the input table, each carrying one part of the original column, or a single column containing a collection (list or set) of cells with the split output. It can be specified whether the output consists of one or more columns, only one column containing list cells, or only one column containing set cells in which duplicates are removed.

Cell Splitter

Pivoting

Filter & Transform Tools

To create a pivot table in Excel • Click on any single cell inside the data set • Go to the Insert tab and click Pivot Table • Select table / range and output location • Choose pivot table fields by dragging them into “Columns”, “Rows” and “Values”.

Tools to Master KNIME

Pivoting

Filter & Transform Tools

The Pivoting node is configured via three tabs: • “Groups” defines the group columns, aka columns in the “Rows”-frame in Excel (final row IDs) • “Pivots” defines the pivoting columns, aka columns in the “Columns”-frame in Excel (final column headers) • “Manual Aggregation” corresponds to the “Value” setting option. Select one or more columns for aggregation from the available columns list and select an aggregation method for each selected column. The Pivoting node produces three output tables: the pivot table and the total values for columns and rows. Note 1: The Pivoting node doesn’t have “Filter” options, but you can simply use a Row Filter node beforehand. Note 2: In KNIME you have to choose at least one column for the Groups and Pivots. In case you want to choose only “Rows” you can use the GroupBy node.

row(s)

values

column(s)

Grouped Rows in Excel (with Pivot)

Filter & Transform Tools

Create a pivot table as described on the page 'Pivoting' and drag only columns into the “Rows” and “Values” frames.

The GroupBy node is configured via two tabs: • “Groups” defines the group columns, aka columns in the “Rows” frame in Excel. • “Manual Aggregation” corresponds to the “Value” setting option. Select one or more columns for aggregation from the available columns list and select an aggregation method for each selected column.

Filter & Transform Tools

Grouped & Aggregate Rows in KNIME

row(s)

values

Unpivoting in Excel

Filter & Transform Tools

To unpivot a table in Excel: • Store the dataset in a table. • Select any cell in the table. • Click the “Data” tab and select “From Table/Range”. • This opens the “Power Query Editor”. Select the columns to unpivot by holding down the Shift key. • Click the “Transform” tab of the Power Query Editor and select “Unpivot Columns”. • Click the “Home” tab of the “Power Query Editor”, and select “Close & Load” to save the data unpivoted back in the Excel workbook.

Tools to Master KNIME

Unpivoting

Filter & Transform Tools

The Unpivoting node is configured via one tab: • In the upper section, “Value columns” defines the column(s) to unpivot, aka column selection in Excel. • In the lower section, “Retained columns” defines the column(s) that remains unchanged, aka unselected columns in Excel. Note: the Unpivoting node requires selecting at least one column in the “Value columns” section

Tools to Master KNIME

Automated Excel Table Formatting

Excel Formatting

KNIME can easily be extended with packages created by third parties contributing to the community. The figure below shows you an overview of all the nodes in the Continental extension to programmatically format tables in Excel sheets.

Tools to Master KNIME

Automated Excel Table Formatting

Excel Formatting

The XLS Sheet Selector and the XLS Merger node are really helpful nodes if your EXCEL file has more than one sheet. By default the formatting is always applied to the first sheet. So, if you have an Excel file with only one sheet you don’t have to worry about these two nodes. However, if you have multiple sheets the XLS Sheet Selector allows you to define which sheet your XLS Control table is for. The XLS Background Colorizer node changes the background color of cells. You can assign either a static color and / or pattern fill. One option is to assign the same color to all cells with a specific tag value, e.g. all cells with tag “header” should have a yellow background. Another option is to use RGB values in either hex syntax #FFD800 or decimal syntax R/G/B as tags and use them as the background color. The XLS Conditional Formatter node changes the background for the cells with a certain tag value according to their numerical value. In the configuration window you can define a color scale by setting a minimum and maximum value and assigning a color to each. Optionally you could set a mid point value and assign a color to that. Cells with values higher or lower than the thresholds will have the background color of the minimum/maximum value. The XLS Border Formatter node can add borders to a given range specified by a certain tag or by all tags. By activating the corresponding checkboxes, you can add borders to the top, right, bottom, and left. In addition to adding a border around the range specified by the tags, the node gives you the option to use inner vertical and horizontal boarder lines in each cell, too.

Tools to Master KNIME

Automated Excel Table Formatting

Excel Formatting

The XLS Format Merger node allows you to either combine formatting instructions for different sheets prior to using the XLS Formatter (apply) node or when applied to the same sheet, it merges the properties at the lowest detail level (e.g. the formatting instructions for the cell A1 is bold in control table one and italic in control table two. The subsequent formatting instruction for A1 is italic and bold). Thereby, the upper input port overwrites a lower one in case of conflicting information (e.g. two different font colors for the same cell). The XLS Cell Merger node merges the cells for given rectangular ranges of input tags into one cell. For example, we can merge all cells in the first row and centralize the title with the XLS Font Formatter node. This node works only on strictly rectangular ranges. The value of the merged cell is the value of the most top left cell of the merged range. The XLS Formatter (apply) node reads an unformatted Excel file, applies all the collected formatting instructions, and saves the formatted Excel file in the defined output location.

This is the last page of the rework of the PDF 'from Excel to KNIME', which is provided free of charge after registration with KNIME Press. Click on the PDF icon below for the link. If you have any questions or would like to discuss your business case, then feel free to get in touch with me through the Email icon or book a meeting through the Calendar icon at the bottom of the page. For a more extensive introduction to KNIME, please refer to the paid book “KNIME Beginner’s Luck” also available from KNIME Press.