topic Re: [resolved] How to extract specific cell from multiple excel in a folder in Talend Studio

[resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 05:03:20 GMT

Hye,
I need to extract specific cell from multiple excel in a folder.
But, I have a problem which is the output is repeated.
How to solve the problem? Here, i attached the screenshot what im used to show the output.

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 06:54:57 GMT

The resolution of your screenshot is to low to spot anything on it.
What do you mean with "The output is repeated". The tFileInputExcel component is fairly simple and start reading from the start row until the last row and starts with the start column until the last schema column is reached.
To get one value you have to specify one schema column and setup the start and end row according to your needed position.
If you find that a bit cumbersome (me too) you could try the alternative Excel component tFileExcelReferencedCellInput. This component can read a particular cell addressed by row/col or the Excel address expression "D1" on a specific sheet.

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 08:21:57 GMT

The output is repeated means I have input from multiple excel in a folder, when run the job execution the output read from the first excel only... so, all the output are same..

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 09:38:18 GMT

Hi,
Can you show the settings for your excel component?
vaibhav

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 09:50:09 GMT

Hi, here attached the setting tfileinputexcel..TQ

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 10:34:04 GMT

hi,
sorry but we still not seeing your screenshoot clear enought.
regards

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 10:43:31 GMT

sorii..here, there is new attachment for your reference....

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 11:08:41 GMT

Hi,
Suppose you want to read cell on 5th row and 3rd column then you can set following properties in your tFileInputExcel component
- Set header - 4
- Start column position - 3
- End column position - 3
- Set Limit to - 5
Try above... while doing so you will get an idea.
Thanks
Vaibhav

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Wed, 08 Oct 2014 11:18:58 GMT

The reason for getting always the same values must be in the tJavaRow. You design to iterate through the files with tFileList and read every file with tFileInputExcel is fine and should work.
For us between the reading from the excel file and the log output there are a lot of not visible steps and I am sure you will find here the problem. If you need held, please tell us what you do in the tJavaRow and the tFixedFlowInput.

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 02:56:26 GMT

Hi, jlolling...
Here, there is new attachment for reference.. Example, I want to get source from B4, B5 and J1..TQ...

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 07:04:11 GMT

Hi,
Can anyone give me some suggesstion how to solve the problem... pleasee advice me... TQ...

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 08:40:58 GMT

At first you do not reset the sequence, means every file the sequence value will grow.
Next you write the context variables once but never again, because of that you will always get the same value.
To get an idea what I mean I suggest you set null the values before you set the values, you will see you get mostly null values instead of always the same value.
I would use a tJavaFlex instead of tJavaRow and reset the sequence in the begin part or use simply the file name as name for the sequence.

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 09:19:58 GMT

What do you meant set null the values before set the values? Can you give me some examples how to set the null value?
Can we set the null value in tJavarow?

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 09:32:07 GMT

Mr. Jlolling,
How to use tJavaFlex? Can you give me some example how to set in tJavaFlex? Example, i want to extract specific data from D1, J1, and B3 in excel file...
Did i should replace tJavaRow with tJavaFlex in my job design... it is right? TQ

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 09:33:54 GMT

Hey, it is your Java code and this has nothing to do with Talend.
I would simply implement my last suggestion and use the file name as name for the sequence
Instead of "s1" use ((String)globalMap.get("tFileList_1_CURRENTFILE")).
This way you start a new sequence for a new file and the you will get the values always from the right number of rows.

Re: [resolved] How to extract specific cell from multiple excel in a folder

Anonymous — Thu, 09 Oct 2014 09:51:47 GMT

tQ so muchhh..... i have successfully completed... 😃