Hey data friends! In today’s post were going to write our Knime Data table to an excel file.
Although I love using the Knime Analytics Platform for all my data work – from querying data to manipulating data to modeling data to reporting data – I understand that Knime isn’t the only tool in the tech stack that’s needed to get the job done. Not only is it not the only tool to get the job done, but it isn’t really a tool that key decision makers (in a typical business) understand or talk. The status quo in business is Excel and most key decision makers talk excel.
Given this conundrum, today’s post will show you how to write a data table from the Knime Analytics Platform to an excel file. Yes, we’re going to output data from knime to excel.
Lets get started.
We don’t really need a specific data table for today’s exercise, so we’ll run with this table from our video on creating lookup ID’s. The Lookup IDs will be useful when using the data in Excel bc they’re allow our vlookups to be a bit lighter in syntax.
Let’s search for “Excel Writer” in our node repository.
We can drag and drop the node into our workflow and double-click into the node to start the configuration. The configuration menu should look like the below.
I added a few boxes in the image to help us breakdown the different options Knime offers in the Excel Writer node.
· Blue Box: This blue box has the File input box where we will input the location that we want to write our Excel file to
· Below the Blue Box: Here you have the option to Overwrite the file if it already exists
o You also have the option of having the file open after its output
o You have the option to change the name of the Excel File’s tab/sheet that we’re outputting the data to
· Green Box: These are the options to write the column headers and row ids to our output
· Below the Green Box: These are the options to fill in missing values as well as column sizing and page layout
· Yellow Box: Here you’ll see an Exclude section in red and an Include section in Green; these allow you to filter out any columns that you don’t want to include in the file you’re writing to
o This gives us flexibility to filter out columns without having to use a Column Filter node in the previous step of our workflow
The setup should be relatively straight forward. In the most basic form, we’ll provide the location of where we want to save the output file and select the columns we want to include/exclude. Your configured menu might look similar to mine, below:
My favorite options are the
· column headers
o Headers help me remember exactly what data I have in my table
· Sheet name
o This also helps me with organization
· Overwrite existing file
o When working with reporting, where you update a reference data source each time the reporting needs updating, this option allows the ability to automatically allow the overwrite with existing file with updated data
· Open file after execution
o This is personal preference, but typically is best when constantly working within the file that contains the exported data
Once you’ve got your node filtered to your liking, you can click apply, ok, and execute the node.
Below is our data, exported from the Knime Analytics platform to an Excel file!
That’s all there is to it!
If you’re wondering if its possible to export more than data source into the same excel file, I’m here to tell you that it is possible, You can export multiple data tables from Knime into one Excel file. The next post I’ll be working on will show you how to export multiple data sets to one Excelfile, I’ll link that here once it’s done.
As always, share this post with any of your friends that might find it helpful. If you have any questions or need anything clarified don’t hesitate to shoot me a DM on twitter @cest_nick ; otherwise, I’ll catch you on the next post.
-Nick
