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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Eight 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

Four 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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Highlighted
Thirteen 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
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

 
Thirteen 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
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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
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

 

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

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