Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. If there is one task that haunts every non-tech business professional, it is the dreaded “end-of-month reporting.”
Back before I became a DX manager, I spent hours every week opening dozens of Excel files sent by different regional managers, copying the data, and pasting it into one massive master spreadsheet. Sound familiar? Not only is this mind-numbing, but one missed row or accidental keystroke ruins the entire report.
In our last tutorial, we covered how to clean messy strings and fix data types. Now, we are entering the Automation Hacks territory. In this guide, I will show you how to automate your Excel reporting by combining multiple sheets and files instantly using KNIME Analytics Platform. No VBA, no Python, just pure drag-and-drop magic.
The Magic of the Excel Reader Node
In Excel or Power Query, combining multiple files can sometimes be clunky. In KNIME, it is surprisingly simple thanks to a hidden superpower inside the standard Excel Reader node.
Most beginners think the Excel Reader node can only read one file at a time. But with one click, you can tell it to read an entire folder of Excel files and stack them on top of each other automatically.
Step-by-Step: Combine Multiple Excel Files Automatically
Let’s say you have 10 different Excel files (e.g., Sales_NY.xlsx, Sales_LA.xlsx, Sales_CHI.xlsx). They all have the same columns, and you just need them combined into one master table.
Step 1: Organize Your Files
First, create a new folder on your computer (for example, “Monthly_Sales_Reports”) and put all your regional Excel files inside this single folder. Make sure no other random files are in there.
Step 2: Add the Excel Reader Node
Open your KNIME workspace, search for the Excel Reader node in the Node Repository, and drag it onto your canvas.
Step 3: Change the Read Mode to “Files in Folder”
Double-click the node to open the configuration window. Look at the top section where it says “Mode”. By default, it is set to “File”.
Click the dropdown menu and change it to Files in folder. Now, click the “Browse” button and select the “Monthly_Sales_Reports” folder you created in Step 1.
Step 4: Check Your Settings and Execute
In the configuration window, check the preview at the bottom. You should see the data from all your files stacked perfectly. KNIME automatically appends (unions) the rows as long as the column headers match.
Click OK and press F8 to execute the node. Boom! You just combined 10 files in two seconds. If you add 50 more files to that folder next month, you do not have to change anything—just re-run the node!
Writing the Result to a New Master File
Now that KNIME has combined your data in its memory, you probably want to export it back out as a shiny new master report.
Search for the Excel Writer node and connect it to your Excel Reader. Double-click the Excel Writer, choose where you want to save the new file (e.g., “Master_Sales_Report.xlsx”), and execute it.
Your entire weekly workflow—reading, combining, and exporting—is now fully automated. Next week, you just click the green play button once.
Conclusion: Your Next Steps
Automating the consolidation of Excel files is often the “Aha!” moment for many business users. When you realize you never have to manually copy and paste data again, your perspective on work changes completely.
But wait, what if you don’t even want to click the “play” button? What if you want KNIME to wake up at 6:00 AM every Monday, combine the files, and have the report ready before you even log in?
If you are ready to take your automation to the ultimate level, join me in our next tutorial: How to schedule KNIME workflows to run automatically.
