Would it be possible to add a way to read data from Tables within a sheet in Excel rather than only the entire sheet at once to the Excel Reader node? This would be a great improvement for users in my company that have data in Excel tables, sometimes more than one table per sheet.
In addition to what John is stating above those tables are dynamic so they grow or shrink as data is updated so you canāt just use read from a specific area of the sheet to accomplish this. Additionally there are also Named Ranges in Excel that act similarly. It would be nice to be able to have all these options when reading from Excel.
1 Like
Hi @JohnMThom and welcome to the Knime Community. Iām assuming that @Experteece works together with @JohnMThom .
I am not sure what you guys mean by ātables within a sheetā, are they just different area/cells within a sheet?
You can specify what rows and columns for the Excel Reader to read within a sheet via the options I highlighted in the Excel Reader:
2 Likes
Bruno29a. If you go to the Formulas tab in Excel and look at the Name Manager you will see a list of Tables and Named Ranges if some one has configured the workbook that way.(See the screenshot below) These tables make referencing the data in formulas much easier and make the formulas themselves more readable. The tables are dynamic in nature. That is, if you add more contiguous rows or columns the defined area automatically grows to encompass this new content. Similarly, when data is deleted, the table shrinks. Using the Read only data in option you show limits you to a fixed area on the sheet. When a user adds new data that new data would not be read and if data is deleted you will end up with blank rows or columns.
Hi @Experteece , thanks for the detailed explanation, Iām no expert in Excel and I certainly was not aware of this feature. Iām not sure the Excel Reader has the ability to read these - someone would have to confirm this. I think itās able to evaluate formulas, but not read the limits of a table from the Name Manager.
If what I said above is correct, as a workaround, you may need to define these limits manually per sheet in the Excel Reader as I suggested through the range option, and based on whatās in the Name Manager:
1 Like
@bruno29a As I stated, the limits option will not take into account growth and shrink. We know that getting data from the tables is possible because Power BI an Alteryx are able to do it. That is why we are suggesting it as an improvement to the Excel Reader
Hi @Experteece , donāt get me wrong, Iām not saying that the Excel Reader should not support this feature, but as it stands, it seems like it currently does not (again, someone to confirm this). And yes I understand that what I suggested will limit the option on the fact that itās not dynamic, but this was more of a workaround in the meantime.
@Experteece actually you can use Python and openpyxl with KNIME to do that. Identify named tables and import them (cf. this thread).
You van extract the tables directly or read all the ranges from your ānamedā table (areas) and then use KNIME to import them.
4 Likes
Hi all,
thatās great feedback (and a great suggestion, @mlauber71!), so thanks for that! I created a feature request to include the Named Ranges (internal reference: AP-18115) since I couldnāt find it in our system yet and I think it would be a valuable enhancement.
Cheers!
Lukas
4 Likes
Thanks for the suggestion @mlauber71 this may be something we could try implementing and getting to our users via a component. Iāll investigate that possibility!
Thanks @LukasS for creating the feature request, I think it could be a nice addition!
3 Likes
Hello!
This is a great idea! +1 from me!
Cheers
3 Likes
Nice one @mlauber71 . Thanks for the ticket @LukasS , definitely a great feature to have. +1 for me
2 Likes
In any companyās Excel community of finance department, storing data in āTableā format is a recommended practice. In current version of excel, many of excel functions and features are āTableā aware.
For knime to be compatible with Excel files containing many tables in a single sheet or in a single file, it is essential that there are dedicated nodes to read / write Excel āTablesā.
Reading Excel tables:
While writing Excel tables in excel sheet, we can select the destination table or create a new table.
Till then, we need to use the python OpenPyxl package for above functionality.
1 Like