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.
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.
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.
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
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?