read multiple spreadsheets from one excel file and edit data

[Maya229]

Hi all,

I’m quit new to Knime, thank you for any help

Please assume my following test data:

Test_Data_FY2022.xls (49 KB)

I have 5 different spreadsheets in that excel.
Is there a possibility to read all 5 (could also be more or less) spreadsheets at the same time?

So the first spreadsheet would be “TestData_A”

Furthermore I would need the data edited in the following way:

• I only need the last row of each spreadsheet
• and I need a new column e.g. “Bezeichnung”, and in that new column (with only the last row) should appear the name of the spreadsheet

So in the end of my workflow I would have (in that case) 5 different rows with the data (only the last row from each spreadsheet & the header) from the 5 spreadsheets and a new column e.g. “Bezeichnung” and the contant/value of that new column should be the name of the spreadsheet.

Thank you so much for any help
Best
Maya

[mlauber71]

@Maya229 welcom eto the KNIME forum.

Maybe you take a look at these sample workflow:

Bulk load of excel sheet with empty worksheet

@MarekV if you take a look at this example with your files the empty entry is being skipped. [image] You can configure what the reader would consider as a missing entry and skip it if you want. [image] If you still have duplicates you can further elevate them with the Duplicate Row Filter or advanced techniques. kn_forum_43934_excel_bulk_import.knwf (51.2 KB)

[bug or no bug ?] Excel Reader preconfigured work sheet selection options

@Geo what you could do is force the use of sheet_selection = “NAME” thru a Flow Variable in case of doubt. Maybe not the most elegant way but shout provide an additional layer of security. [image] And indeed under certain circumstances it seems to revert to the default value. Not able to reproduce it yet in a constant way. But might be worth looking into, @laaaarsi. [image]

[Maya229]

Thank you @mlauber71

with a few adjustments it worked like I wanted:

BR
Maya