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

Five Stars

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

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?  


Accepted Solutions
Five Stars

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

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

 


All Replies
Employee

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

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

Nine Stars

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

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

Six Stars

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

Check whether the respective values for headers and footers are correct

Five Stars

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

Attached the screen shots. 

 

Excel file is of older version .

Employee

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

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

Forteen Stars

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

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

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

It will through OLEException
Five Stars

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

 
Highlighted
Forteen Stars

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

@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.

Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

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

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.

 

   image.png

 

image.png

 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

Five Stars

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

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

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download