topic Re: How to output row numbers from a multi-sheet excel input in Talend Studio

How to output row numbers from a multi-sheet excel input

Anonymous — Sat, 16 Nov 2024 14:23:58 GMT

Hello,
I'm reading a 5 sheet Excel workbook and doing some exception reporting (validations) on this worksheet. For example, I output NULL records, duplicates etc. I want to write out the Sheet Name and the Sheet Row number for each of the rows that I flag so that users can easily find the worksheet/row to fix the problem.
I have tried this in a tPerlRow (that follows the tFileInputExcel) which gives me a line number in total.
@output_row = @input_row ;
$output_row = "ROW_NUMBER-->" . (1+sequence('s2',1,1)) ;
If there are 10 rows in each of the 5 worksheet (in reality the row numbers change all the time though the worksheets are always the same) and it catches the last row in the 5th worksheet as incorrect, it gives me #50 which is not very useful to the users. I want to tell them that this row is "ROW 10 from SHEET_5" or something like that.
The above example works fine with single sheet workbooks. Btw, I add 1 to the sequence above as I am skipping the header row in the excel worksheets.
Is there a solution to this? I was not able to access anywhere a SHEET NAME to provide. If I cannot provide the sheet name, is it possible to re-set the sequence number after each sheet within the workbook?
Thanks in advance.
Regards,
Sean

Re: How to output row numbers from a multi-sheet excel input

Anonymous — Sun, 13 Apr 2008 12:28:38 GMT

I suppose your using the new features of tFileInputExcel (3359) and so you're not "really" with a TOS 2.3.2 🙂
In your tPerlRow, I suggest:

@output_row = @input_row;
$output_row = sprintf(
    'sheet "%s", row %u',
    $_globals{tFileInputExcel_1}{CURRENT_SHEET_NAME},
    1 + sequence($_globals{tFileInputExcel_1}{CURRENT_SHEET_NAME}, 1, 1)
);

Re: How to output row numbers from a multi-sheet excel input

Anonymous — Sun, 13 Apr 2008 23:00:57 GMT

A big thanks to you! This works nicely for me.
You are right. It is slightly modified TOS 2.3.2.
Q. When I do the Control-Space Bar, I do not see the CURRENT_SHEET_NAME attribute. How do I go about looking for these attributes? What component file will be the one I need to look at?
Q. Also why does something simple like this does not work for me. It gives me zero for all rows.
$output_row = sprintf('%u', $_globals{tFileInputExcel_1}{NB_LINE});
Thanks.
Regards,
Sean

Re: How to output row numbers from a multi-sheet excel input

Anonymous — Mon, 14 Apr 2008 00:33:58 GMT

Q. When I do the Control-Space Bar, I do not see the CURRENT_SHEET_NAME attribute. How do I go about looking for these attributes? What component file will be the one I need to look at?

In the tFileInputExcel_perl.xml component description file, you have:

	<RETURNS>
		<RETURN NAME="NB_LINE" TYPE="INTEGER" AVAILABILITY="AFTER" />
		<RETURN NAME="CURRENT_SHEET" TYPE="STRING" AVAILABILITY="FLOW" />
		<RETURN NAME="CURRENT_SHEET_NAME" TYPE="STRING"
			AVAILABILITY="FLOW" />
		<RETURN NAME="CURRENT_SHEET_POSITION" TYPE="STRING"
			AVAILABILITY="FLOW" />
	</RETURNS>

These are the global variables related to tFileInputExcel.

Q. Also why does something simple like this does not work for me. It gives me zero for all rows.
$output_row = sprintf('%u', $_globals{tFileInputExcel_1}{NB_LINE});

Because NB_LINE is available only once the whole data flow is over.

Re: How to output row numbers from a multi-sheet excel input

Anonymous — Mon, 14 Apr 2008 00:41:39 GMT

Sweet! Thanks for that quick reply. You guys are spoiling me 🙂

Re: How to output row numbers from a multi-sheet excel input

Anonymous — Mon, 26 Sep 2011 09:02:05 GMT

i have excel file -2007 (file name - customer)
and its excel sheet
1 - client
2 - user
and i want to count the both sheet rows .
i used tFileRowCount but its not support xlsx or excel 2007 sheet .
show me snapshoots
thanks
Naveen Dewase