topic Re: Unable to fetch the data from excel(.xls) file in Talend Studio

Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 07:14:37 GMT

Guys,

I'm facing weird issue while reading .xls file. File is not fetching any data rows even there is data present in a excel file. I'm not getting any error.

If I read same file from repository (metadata) I'm getting the data rows but there will be multiple files so cannot use metadata.

Any idea about this?

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 07:30:54 GMT

Hi,

Could you please add screenshots of the following details:-

a) Job flow where data read is happening using tfileinputexcel from multiple files and printing to tlogrow.

b) screenshot of tfileinputexcel basic and advanced tabs

c) Schema of tfileinputexcel

Please also provide 2 sample files so that we can replicate the flow at our end. Please remove any confidential data before providing sample file.

Warm Regards,

Nikhil Thampi

Re: Unable to fetch the data from excel(.xls) file

ankit7359 — Thu, 13 Dec 2018 07:31:56 GMT

Hi @SanjayK9592,

Greetings of the Day,

Can you pls share a screenshot of the excel file which you are tryin to upload also can you attach the screenshot - while reading the file in Metadata.Also it seems that you are reading the older version of excel format(EXCEL - 97 to EXCEL2003)..

Pls reach out to Community,if necessary.

Thanks,

Ankit

Re: Unable to fetch the data from excel(.xls) file

dipanjan93 — Thu, 13 Dec 2018 07:38:01 GMT

Check whether the respective values for headers and footers are correct

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 08:08:13 GMT

Attached the screen shots.

Excel file is of older version .

Basic_setting.PNG
FileInputAdvanced.PNG
inputData.PNG
readExcel.PNG
RepositoryAdvanced.PNG

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 08:13:33 GMT

Hi,

Thanks for the screenshots. Could you please attach the input file also as it is very difficult to create sample set from screenshots?

Warm Regards,

Nikhil Thampi

Re: Unable to fetch the data from excel(.xls) file

manodwhb — Thu, 13 Dec 2018 08:19:09 GMT

@SanjayK9592,can you enable Read excel2007 fileformat in Basic setting of tFileExcelinput try and let me know.

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 08:21:25 GMT

It will through OLEException

Re: Unable to fetch the data from excel(.xls) file

manodwhb — Thu, 13 Dec 2018 10:02:15 GMT

@SanjayK9592,i do not get any error,might be you need to install the required jars to read a excel file.

can you check in the Problems view from Window>>show view..>>General>>Problems,what was the problem and check the excel file required jars also.

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Thu, 13 Dec 2018 10:03:34 GMT

Hi,

I was able to read the file without any issues. I could see there are lot of empty rows at the end and it is the reason why the output reached almost 2310 records.

If you are able to read it through metadata mode, I would recommend you to first create a tfileinputexcel from metadata and then change it to Built in mode once it is present in studio canvas.

Warm Regards,

Nikhil Thampi

Re: Unable to fetch the data from excel(.xls) file

Anonymous — Fri, 14 Dec 2018 07:58:30 GMT

Nikhil/Guys Thanks for reply.

I found the actual problem. While reading this file i'm checking filter empty row checkbox in advanced tab.

whenever i'm doing that it is reading 0 rows. When I unchecked this I'm able to read the data. I used tFilterRow to filter NULL values.

@talend Team,

This may be a bug in Talend Studio. I'm using Talend Data Management 6.5.1 platform(Enterprise version).

Thanks,

Sanjay