Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. If you work with data every day, let me guess your biggest frustration: You try to sum up a column of sales figures, but the result is zero. Why? Because the system exported the numbers as “Text.” Or you try to match customer names, but invisible spaces ruin everything.
As a DX manager, I have seen entire reporting processes break down just because a date was formatted incorrectly or a string had trailing spaces. In our previous post, we learned how to filter rows and remove duplicates easily, which handles the “amount” of data. But what about the “quality” of the data?
In this tutorial, I am going to show you how to fix these exact problems. We will look at how to easily change data types and clean messy strings in KNIME Analytics Platform—without writing any Python or complex Excel formulas.
Why Data Types Matter (And Why Excel Fails Us)
In data analytics, knowing whether a column is a Number (Integer/Double), Text (String), or Date is critical. If KNIME thinks a number is a String, you cannot do math on it. In Excel, you might try multiplying by 1 or using the “Text to Columns” trick to fix this, but doing that manually every week is a waste of your time.
In KNIME, we can lock in the correct data types automatically using specific nodes.
How to Change Data Types in KNIME
The easiest way to fix data types is by using conversion nodes. The most common scenario is converting text that looks like a number into actual numbers.
Using the String to Number Node
- Go to your Node Repository and search for the String to Number node. Drag it onto your canvas and connect it to your data flow.
- Double-click the node to configure it.
- You will see two boxes. Move the columns you want to convert (e.g., “Revenue”, “Quantity”) into the Include box on the right.
- Select the appropriate number type (usually Integer for whole numbers or Double for decimals) at the bottom.
- Click OK and Execute. The column headers in your data view will change from “S” (String) to “I” or “D” (Number). You are now ready to do math!
Note: If you need to do the reverse, simply use the Number to String node.
How to Clean Messy Strings Without Coding
Now, let’s talk about text. Maybe you have customer names exported from a CRM, and some are uppercase, some lowercase, and many have random spaces at the beginning or end. This will destroy any Join (VLOOKUP) you try to do.
To fix this, we use the String Manipulation node.
Using the String Manipulation Node
- Drag the String Manipulation node onto your canvas and open the configuration window.
- In the top left panel, you will see a list of your columns. In the top middle panel, you will see a list of functions.
- To remove invisible spaces, double-click the
strip()function. It will appear in the “Expression” box at the bottom. - Place your cursor inside the parentheses of
strip(), and then double-click the column name you want to clean (e.g., “Customer Name”). Your expression should look like this:strip($Customer Name$). - At the bottom of the window, choose to Replace Column and select “Customer Name” to overwrite the messy data.
Want to make everything uppercase too? You can nest functions! Just wrap your expression in upper() like this: upper(strip($Customer Name$)). Execute the node, and your text data is instantly standardized and perfectly clean.
Conclusion: Your Next Steps
Data preparation is often the most time-consuming part of analytics, but with the String to Number and String Manipulation nodes, you can automate the clean-up process permanently. Your workflow now guarantees perfectly formatted data every single time you run it.
Now that you know how to bring data in, merge it, filter it, and clean it, you have officially mastered the basics of Data Prep & ETL in KNIME. You are no longer just manipulating spreadsheets—you are engineering data pipelines!
Are you ready to see how these skills translate into massive time savings? In our next module, we are entering the Automation Hacks category. Join me as I show you how to automate Excel reporting and combine multiple sheets in seconds using KNIME.
