How to read Excel Sheet Name?

Hello,
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?

Hi @SeemaK

Here is how you can do it:

  1. You can create a flow variable from the path to the excel file in the Excel Reader (XLS) dialog
  2. Do some String manipulation using the String Manipulation (Variable) node.
  3. 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)

2 Likes

Hi!
I’m trying to do the first step but can’t make it…
Can you help?
Br,
Ivan

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

Cheers, Iris

1 Like

Thank you. Going through the videos now. Got the Flow variable ports now :slight_smile:

1 Like

Thank you. Have managed to extract the year :slight_smile:

Hi there!

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?

Br,
Ivan

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:

Ivan

2 Likes