1. Hands-on advanced data reading and writing
In this video, we will use a better way to import many monthly Excel files. Then, we will automate our work to create and store an aggregated report. This setup is perfect for monthly reports that need repeated steps.
We imported files one by one, changing the path each month. This method will save us a lot of time. Let’s start by adding the Excel Reader node to our workflow. We'll point the node to a folder instead. It contains all the monthly Excel files we want to process.
In the configuration, use the file path selection to specify the folder with all the sales files. Let's set up the filter options. We make sure to only bring in the right files. For example, filter by file type and file name. This pulls in all files that match. In this example, "2023-01-sales-data.xlsx" through “2023-06-sales-data.xlsx.”
Now, KNIME will automatically import any new files added to this folder that match the filter. This eliminates the need for a monthly manual update of file paths and keeps your dataset up to date.
Now that we’ve streamlined the data import let’s keep our output files organized, too. Add the Create Folder node to your workflow. In the settings, specify the location and name of this folder. Let's create a folder on your desktop named "Monthly Reports." Every time this workflow runs, it will check to ensure this folder exists and create it if it doesn’t.
This desktop folder will make it easy to find your reports as they accumulate over time. By storing them in one place, you'll have instant access to all past reports, making it easy to track trends and review historical data.
Now that our processed data is ready, it’s time to save it. Add the Excel Writer node to your workflow and connect it to the aggregated data. In the configuration, set the output path to the “Monthly Reports” folder we just created. For the write mode, select the option to append new sheets rather than overwrite the file.
Each time you run the workflow, KNIME will create a new worksheet in the Excel file. You’ll end up with a single Excel workbook. Each month’s report will be in a new tab. This replaces separate Excel files for each report. This setup offers a single, easy-to-access file with all the necessary info. You will also save time by not having to track down or open multiple files for a historical view.
With these steps, we have automated the complete process. Now, with a few clicks, your monthly data is updated, aggregated, and stored, saving us more time for analysis.
2. Let's practice!