How to Dynamically Pass Input to Microsoft Excel Input Transform?

[Raja10D]

I have created a pipeline with get filenames and filter rows with the xlsx files and giving the file path to the excel input, but it is not regonizing.

I am trying to dynamically pass the file path to the Microsoft Excel input transform from a previous transform. However, I am encountering the following issues:

The Preview Rows option does not work when the file path is dynamically passed.

Clicking Get Sheetnames does not display the sheets of the Excel file.

Clicking Get Fields does not show any fields from the Excel file.

Is there a way to dynamically pass the file path to the Microsoft Excel Input transform and still make these features work? If yes, could you please provide the steps or guidance on how to achieve this?

[dave-csc]

Hi @Raja10D,

you can't use Preview rows indeed because Hop GUI doesn't know the file names you are passing, when working in a single transform.

To get an idea of the output, you need to select the transform (click on the icon and not the name), and choose Preview output: this will run the pipeline up to the selected transform, and shows the first 1000 rows of output.

Hope this helps :)

[Raja10D]

Hi @Raja10D,

you can't use Preview rows indeed because Hop GUI doesn't know the file names you are passing, when working in a single transform.

To get an idea of the output, you need to select the transform (click on the icon and not the name), and choose Preview output: this will run the pipeline up to the selected transform, and shows the first 1000 rows of output.

Hope this helps :)

Hi @dave-csc I have preview output for my Excel input transform; it is showing an error, and it is not even recognizing the input path from the previous transform. I will attach the screenshot of the error.
I understand Preview rows don't work; also, get sheetnames and get fields are not working when we are passing the filepath from previous transform

Screenshot image:

I want to pass the input path to my excel input component dynamically, is there any other way in Hop??

[dave-csc]

The error shows that you have not defined the input fields you need to read from the file.

You can either specify them manually, or if you like to use the guided tools from Hop GUI you can at first configure the transform by using a template Excel file (disable - temporarily - the option "Accept file names from previous transform"). When you're done, and checked the output corresponds to what you expect by using Preview rows (you can now use it), re-enable the option and you should get the file name(s) dinamically...

[Raja10D]

The error shows that you have not defined the input fields you need to read from the file.

You can either specify them manually, or if you like to use the guided tools from Hop GUI you can at first configure the transform by using a template Excel file (disable - temporarily - the option "Accept file names from previous transform"). When you're done, and checked the output corresponds to what you expect by using Preview rows (you can now use it), re-enable the option and you should get the file name(s) dinamically...

Working now.
Thankyou very much @dave-csc
Another doubt how to get the schema of the excel file, There is a component in the hop file metadata with this I can get the schema of the csv files but can't get the schema of the excel file.
Is there any option in hop we can get the schema of the excel files.

[hansva]

At the moment we have no way available of "guessing" excel schema's at runtime.
We expect the input to be configured for the excel file it will be reading.
If your goal is to extract information from a large set of different excel types there are 2 options.
option 1: You know the schema of each "type" of excel file
--> Use Metadata injection
option 2: You do not know the schema of the file
--> Use the Tika transform this transform can extract data from a number of file types

[Raja10D]

At the moment we have no way available of "guessing" excel schema's at runtime. We expect the input to be configured for the excel file it will be reading. If your goal is to extract information from a large set of different excel types there are 2 options. option 1: You know the schema of each "type" of excel file --> Use Metadata injection option 2: You do not know the schema of the file --> Use the Tika transform this transform can extract data from a number of file types

Actually I have kept the fixed schema in my excel input component and enable the accept fields from previous transform for getting the excel input path dynamically
Problem facing:
Even if the column is not present it is giving the columns with all row values which is good for next processing
But what I need is that if the sheet has 10 columns(requirement) only, I need to go the excel input component itself because after the excel input component we are having all 10 columns, even though the columns are not present in the sheet due to the schema we have given in the Excel input component.

Is there any way to tackle this issue with Hop, if sheet has 10 columns proceed otherwise abort
This is what I need?
If anybody knows help me