How to get total count of Excel File

[Anonymous]

I want to display the number of rows in an Excel File (.xlsx) 

Tried to use tFileRowCount component but it throws an error: java.lang.RuntimeException: not support excel 2007".

So tried a solution which @sanvaibhav 

Had suggested in a post:  https://community.talend.com/t5/Design-and-Development/Error-in-tFileRowCount/td-p/92065 

context.cnt=context.cnt+1

but above prints concatenated result 

Example if there are 5 rows in excel file its prints 11111

Rather I want it to print directly 5

[rmartin2]

Hi,

Strange ! It should have worked.
Can you show us a screenshot of this part of the job and the type of your "cnt" context variable ?

[ankit7359]

Hi @Vikas ,

You can use Global Variable - NB_Line to capture the counts/number of lines in the excel files.

Pls post a screenshot of your Job design which could help further solve the issue.

Thanks,

Ankit.

[Anonymous]

@mhodent cnt type is of StringJobtJavaRow(cnt variable)

@ankit7359 Can you please provide exact syntax on how to use global variable NB line in this scenario

[rmartin2 · Accepted solution]

Ah, it's normal so !

You need to have an "Integer|int" type, and start it at zero.

Else it is doing that :

""

""+1 = "1"

"1"+1="11"

"11"+1="111"

There's an autocast of 1 because cnt is a String !

EDIT : anyway, you should use @ankit7359 solution. It's simpler and better : on the bottom part of the Studio, drag and drop the "Number of Line (AFTER)" variable into any tJava.

So it's tFileInputExcel==>OnComponentOk==>tJava (with the variable)

You will see a globalMap.get function used to retrieve your info

[Anonymous]

Thanks @mhodent and @ankit7359  It worked.

I am sharing the screenshot of updated job design, might help someone with a similar issue.

In tJava (DisplayCount) component added below code:  

System.out.println("Number of Rows in Excel -> " + ((Integer)globalMap.get("tFileInputExcel_1_NB_LINE")));

Updated_Job

[rmartin2]

You don't even need the tJava_2.
Your file will be read anyway !