[Bug]: Excel Input eats incoming fields, but still passes the metadata for them downstream (as if they still exist)

[usbrandon]

Apache Hop version?

2.5-GA

Java version?

11

Operating system

Windows

What happened?

I wish the Excel Input had an option to pass incoming rows downstream, but it currently prevents incoming rows from passing through, but it does still forget to update the metadata and remove their definitions from the stream. Other steps like Select still think the fields exist in the stream, but as you can see from the preview on Excel, it only knows of the 16 fields configured from running that transform, not the 3 that came in before it. Those first three are still picked up by the Select transform downstream (to the right).

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[mattcasters]

Can you attach a sample @usbrandon? My eyes aren't that great anymore these days ;-)

[bjackson-ep]

excel-different-layout-solution.zip

There are 3 fields that go into the Excel Input Transform. The Excel file has 3 fields to output itself.
Notice when you check the dummy step output shows 3 fields only from the excel file. However, if you "Show Output Fields" it shows 6 (the three from the data grid, and the three from the Excel file). It would be great to have the option of letting those incoming fields pass through, or if that is not possible, to at least remove them from the stream that comes out of the Excel File Input. As is, if you have a Select Transform, it will see A, B, C, from the datagrid and the others from the excel file, but will mess up and throw errors because somehow it does not have real access to A, B, C.

Generally I like the option to either let incoming fields flow through or not.
This case is a bug because it looks like the transform did not intend to let those incoming fields flow out, but let the metadata for them flow out anyway.

[bjackson-ep]

My included example is 2.6-GA

[sramazzina]

Ciao @usbrandon. The standard behavior of this transform is to stop the incoming flow to go through the Excel Input. Overall, the best option in my opinion is to fix the discrepancy you have in your visualization. I mean, what you see in the "Show output fields" is wrong.

Apart from this, adding the flag you are asking for is trivial but, in case the user set it, we must decide what to do. IMO the way to go is cartesian product between the input rows and Excel file rows. I think we don't have any other options.

[usbrandon]

Hello @sramazzina! Thank you for taking a look. I agree with you on both fronts. By default the transform should not emit the incoming fields or metadata. If there were a checkbox to allow them to flow though, you are right make them appear for each row of the output. That is consistent with other steps.

Happy to write the checkbox thing as a feature request. The incoming field metadata escaping definitely is a bug and screws up downstream steps by listing fields that are not present in the stream from before the Excel transform.

Thanks for whatever you can do to fix it. I very much appreciate it.

[sramazzina]

Ciao @usbrandon thanks for your answer. The fix is almost done I was just waiting your confirmation to complete it. I will let you know when it will be ready so that you can test it. Have a nice day ;-)

[sramazzina]

Hi @usbrandon, after a careful evaluation I decided to proceed differently and just fix the issue that let unwanted metadata move along.

About the idea of having a flag that gives the ability to pass-through incoming rows, well I decided to postpone that request and ask you to open a feature request because is not that easy to be done and requires careful evaluation. Moreover, the same behavior can be obtained very easily by using a cartesian product the way it is shown below.

Attached the sample I shown you in the picture

3221.zip

[usbrandon]

Hi! I am delighted to have a fix. I will create a separate feature request for the other. The main issue you solved was actually making a big problem, the feature is just a nice to have. Thanks again for your help. Brandon

…

On Wed, Nov 22, 2023 at 4:13 PM Sergio Ramazzina ***@***.***> wrote: Hi @usbrandon <https://github.com/usbrandon>, after a careful evaluation I decided to proceed differently and just fix the issue that let unwanted metadata move along. About the idea of having a flag that gives the ability to pass-through incoming rows, well I decided to postpone that request and ask you to open a feature request because is not that easy to be done and requires careful evaluation. Moreover, the same behavior can be obtained very easily by using a cartesian product the way it is shown below. [image: image] <https://user-images.githubusercontent.com/1270945/285055601-5cd2598d-a618-48c9-a2da-94a1b009886e.png> Attached the sample I shown you in the picture 3221.zip <https://github.com/apache/hop/files/13444630/3221.zip> — Reply to this email directly, view it on GitHub <#3221 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJNF5VKGDLKIDPF5N7DFXLYFZ2IVAVCNFSM6AAAAAA4VPOWXWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMRTGU3TENRYGQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>