I have a few excel files with similar structure, but the problem is dynamic number of columns in each file. But in every file is a field (1st row, different column) with the exact same name, let's call it "THE END" and my goal is load columns until this specific field (the rest of columns is not interesting for me).

I am not sure if this task is doable without metadata injection.
I have been trying to use row normaliser with filter rows step, tried to use different settings in excel input step but without success...

The only solution which comes to my mind is transpose columns to rows, then use filter rows step to filter "THE END" columns then transpose the table back.
But I am pretty sure this is not the right solution..

Here is an example:
google apple microsoft blizzard pentaho THE END
name john josh jerry jimmy jack AAA
surname peterson paterson puterson pyterson poterson CCC

File 2:
apple british airways heineken microsoft THE END
name adam jim peter dan FFF EEE
surname sandler jackson samko thompson SSS WWW

According to my description I would like to load 6 columns (until "THE END") in file 1 and in file 2 i would like to load 5 columns..

Thanks for tips!