Read sheets from the Excel file

Hi Guys,

I have been started making my workflow in Knime.

Sometimes I intent to make several workflow with the same excel file but from different sheets. At present I am dragging the excel reader Xls for every workflow but when i need to change the file then i have manually change all the files in the workflow. Is there a possibility that I load the files only once and can use the different sheets instead of reloading it again and again

Also string to number is not able to convert (41,421.27) into negative value. Do you know which node can handle this conversion. I know this is something which is possible at string manipulation, but I will have there negatives numbers at multiple columns and multiple sheet won’t be a good idea to convert all the sheets using string manipulation.

image

image

Thanks
Ankit

For the first question see solution here


Fort he second one use Column Expression node.

1 Like

Thanks for providing the solution. I understand that above solution reads all the sheet in the excel file and combine it together. What I intend to do was use the use a one particular sheet in excel, apply nodes and get some data. Then I will use another particular sheets apply noted and get my data.

I was thinking if there was way to define the excel workbook once and then you use the multiple sheet required on your requirement instead of loading two excel files and selecting the different sheet.

image

Maybe you could provide us with an example of what you want to do. I do not fully understand what it is you want to do.

You can work thru lists with loops and define conditions. You will have to find the right combination for your question.

Sorry if I have not been clear with my query.

I have a workbook lets say. Test.xlsx (13.0 KB) for example. This workbook has 9 sheets in it.

What I want to do is to extract data from certain sheets(based on my preference, not on any logic). Let say I want to extact data from sheet 1, sheet 3 , sheet 5 and sheet 8. For this data extraction I created my workflows and they are working as intended KNIME_project_query.knwf (24.6 KB)

In order to extract this data I used Excel reader node 4 times, because I wanted to extract data from different sheet each time.

Now lets say for example my file is changed from Test to Test 2, I have to update the excel file and the sheets in the excel reader 4 times(or may be more if I am extracting data from 10-15 sheets). I was trying to ask if there is possibility to Load excel file only once(which can be changed easily) and leave the preexisting sheets as it is and continue with rest of the nodes instead of changing the file in each node and then selecting sheets. A node which allows you to choose specific sheet of the preexisting excel file.

I am uploading the pic for reference but at the moment it will only select Test excel file sheet 1 cause this is what I select in the excel reader. I want sheet 3 to be selected for the 2 node function and sheet 5 to be selected for 3 Node function and so on.

Hi,

Here How can I read all sheets of an excel file with one upload to the excel reader? you can see how combine excel sheets in one excel file (just delete table creator and concatenate nodes). The output gives you one additional column with the sheets names which you can filter (with Dictionary filter for example)

Hope this helps

Andrej

1 Like

For filter you can simply use “Row filter” with regex … for example the pattern Sheet[138] will filter Sheets 1, 3 and 8

Okay, got you point, so I Load the sheets of the excel file in one go and use filter to take out the relevant data. The file which I have has different formats on different sheet.

but for my knowledge is this the only way? Excel reader nodes only supports selection of 1 sheet at a time unless you use Loop and combine all the sheets which you need and then use filter to access the data

Hi,

Your “different formats” I understand that you have different column names … if so, then you can leave the table creator and concatenate nodes so the data of all sheets will be in one table.
But I will do some manipulations to the columns (table) after is read with the Excel Reader node and Constant value Column node so all columns (tables) will be the same and all data in one column of the same type.

I am trying to use Column expression node to change Test 1 - Final 1 and Test 2 - Final 2 and Test 3 - Final 3 but Column expression is only replaces the last expression.

If I remove replace column then it gives me three separate columns. Can we do this using the column expression node or I will have to use string manipulation 3 times?

image

image

Will it be enough to replace just Test with Final?
Or use if Column(Col0) == “Test1” {“Final1”} else {if Column(Col0) == “Test2” {“Final2”}
else {…}}

1 Like

This code which you have written is it Java?

Yes. You can look for more examples on the Hub

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.