topic Re: How to get total count of Excel File in Talend Studio

How to get total count of Excel File

Anonymous — Wed, 11 Sep 2019 13:27:59 GMT

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

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

Re: How to get total count of Excel File

rmartin2 — Wed, 11 Sep 2019 14:19:33 GMT

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 ?

Re: How to get total count of Excel File

ankit7359 — Wed, 11 Sep 2019 14:43:30 GMT

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.

Re: How to get total count of Excel File

Anonymous — Wed, 11 Sep 2019 18:40:49 GMT

@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

Re: How to get total count of Excel File

rmartin2 — Wed, 11 Sep 2019 18:58:19 GMT

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

Re: How to get total count of Excel File

Anonymous — Thu, 12 Sep 2019 11:59:03 GMT

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

Re: How to get total count of Excel File

rmartin2 — Thu, 12 Sep 2019 14:16:59 GMT

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