I have an excel file with the part of the Date reference (Year) in the Sheet name. I would like to read this to create a year column in my data table. Please could someone guide me on how to do this?
Here is how you can do it:
- You can create a flow variable from the path to the excel file in the Excel Reader (XLS) dialog
- Do some String manipulation using the String Manipulation (Variable) node.
- Use the new Flowvariable in the Constant Value Column node.
I created an example for this here: excel_year_extraction_to_column_example.knwf (10.8 KB)
I’m trying to do the first step but can’t make it…
Can you help?
Thank you for your response. I am trying to recreate the same with my files.
But not sure how you got the ‘Red dot’ output port on the Excel Reader. Excuse me for asking maybe the obvious! I am teaching myself to use KNIME.
Did you already go through our e-learing course?
There is a chapter on workflow control, which you can find here: https://www.knime.com/knime-introductory-course/chapter7
There is also a chapter about reading xls files, and a example workflow for this, which you can find here: https://www.knime.com/nodeguide/data-access/common-type-files/reading-excel-files
Thank you. Going through the videos now. Got the Flow variable ports now
Thank you. Have managed to extract the year
If I’m not mistaken oole created a flow variable just by adding a name in a Excel Reader Node next to XLS_LOCATION on Flow variable tab. I’m I right?
Yes, the name of the Excel sheet.
If you want to read multiple sheets from same Excel within a loop here is workflow example to check: