[resolved] tFileExcelSheetInput Limit reading rows by empty column

One Star

[resolved] tFileExcelSheetInput Limit reading rows by empty column

Hi there,
The tFileExcelSheetInput is a great component for extracting data from Excel sheets. However, one thing I'm stuggling with is to be able to limit the rows extracted based on a blank cell in a single column. There is an option to limit rows by ticking 'Stop at empty row'. However, the spreadsheets I'm dealing with are quite messy in the sense that I need to base my extraction on the column A having a date value. If column A does not have a date value then stop row extraction because there are rows without date values and I don't want these rows but the component will carry on extracting rows until it meets a completely empty row.
Please see image.
I was thinking in the tFileExcelSheetInput component settings where it says 'Limit rows to' I could put in a global variable from the result of some sort of counter that will return the number of rows in just the date column. If this is possible how could I do it in practice?
Thanks,
Facoda

Accepted Solutions
Community Manager

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

I see you set the date column as a group element, but this column might has different values. In this case, you want to count the number of all rows, in order to achieve this request, I will suggest you to add a tMap before tAggregateRow, add a new column and set a fixed value to it in the output table of tMap, this new column will be group element on tAggregateRow.
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

Hi
It is impossible to do it, you have to read all rows and filter the rows later on other component. Another approach is to read only date column and count the number of line, store the line number to a context variable, use this context variable to set the limit rows for extract all columns next.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

Hi Shong,
I can't seem to get count of the number of rows in the single date column. Please check my job in the screenshot sample:
I've setup the tFileExcelSheetInput to read the single date column. But when I use the tAggregateRow and the count functions it just counts the same dates and aggregates the number. I know this is aggregation. How could I just return the number of rows in the date column that I have setup to attached to a global variable?
Thanks,
Facoda
Community Manager

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

can you upload a screenshot of the settings of tAggregateRow?
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

Hi Shong: please see the screenshot below of tAggregateRow
Community Manager

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

I see you set the date column as a group element, but this column might has different values. In this case, you want to count the number of all rows, in order to achieve this request, I will suggest you to add a tMap before tAggregateRow, add a new column and set a fixed value to it in the output table of tMap, this new column will be group element on tAggregateRow.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tFileExcelSheetInput Limit reading rows by empty column

Great stuff Shong! Thanks =)