topic Re: How to read same cell values from every sheet in a workbook in Talend Studio

How to read same cell values from every sheet in a workbook

Anonymous — Fri, 06 Mar 2015 12:43:56 GMT

Hi there,
I would like to read particular cell values (same location) from every single worksheet in a particular workbook. So far I can only read particular cell values in just one sheet using the technique described here: Extracting data from specific Excel cells. For some reason (I still have an issue pending on this forum about it) I cannot cycle through all the sheets having selected 'All Sheets' in the tFileInputExcel component.
Now I'm trying a second technique setting up my process as shown in the diagram attached. The question I have are:
1. How can I get the cell value (using Excel cell reference such as A1) from the sheet in the workbook that I am referencing so I can actually output that value in tLogRow
2. Is it possible that I can use this technique to get Excel cell values from all the sheets in the workbook
Thanks,
Facoda

Re: How to read same cell values from every sheet in a workbook

Anonymous — Fri, 06 Mar 2015 20:08:22 GMT

For sure you can: your design was nearly ok 😉
Open the schema of the tFileExcelReferencedCellInput and copy all IN_ columns to the input schema.

Iterate through all sheets (or filter them) and use the return variable CURREN_SHEET_NAME the setup the sheet.

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 12:39:01 GMT

Hi Jlolling,
Thanks for your assistance. I have setup my job as recommended but I'm having problems:

Exception in component tFileExcelReferencedCellInput_1
java.lang.Exception: Cell does not exists: ref=null sheet=1 row=2 col=B
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.tFileExcelSheetList_1Process(ExcelCell.java:1790)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.tFileExcelWorkbookOpen_1Process(ExcelCell.java:427)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.runJobInTOS(ExcelCell.java:2480)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.main(ExcelCell.java:2339)

I cannot understand this. Even when I put the index of the sheet as one still same problem i.e. the cell does not exist clearly the return variable for sheet index is correct.
This is confusing.
Thanks,
Facoda

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 13:07:36 GMT

I have made only an example. You have to address the cell you need (for sure another cell than my example row 2 and column B).
Perhaps you post a screenshot of your excel file to show me where is your content?

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 13:59:12 GMT

There is definitely a value in Sheet1 as can be seen in the diagram below:
The tLogRow output is strange though for sheet index. It's 0 as can be seen. Shouldn't it be returning 1 from the return variable?

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 14:57:26 GMT

The sheet index is 0-based (perhaps a bit confusing because of the default name) but technically the first sheet has the index 0.

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 15:10:21 GMT

Hi Jlolling,
I've tried again referencing a new workbook and the same setup that wasn't working for me before now works like magic.
I really don't know what to say.
But thanks for your help.
Facoda
-PS Also using this job could I get multiple cell values per sheet or a range per sheet? I want to get more than just one cell value

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 15:13:24 GMT

Of course you can. It up to you to address cells in the same or another sheet. In my example one row from the tFixFlowInput addresses one cell in a sheet.

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 15:54:56 GMT

Sorry to be pestering here. I'm not sure it's possible to reference more than one cell per sheet. The outputs are single cells only. For example if I want to reference the following ranges in a single sheet in Excel:
1. D7:E8
2. H8:H10
I couldn't do it unless I was using multiple tFileExcelReferenceCellInput component for each cell value?
Thanks,
Facoda

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 16:10:35 GMT

I guess you see it more complex as it is actually.
Every input row of the component addresses one cell and it does not matter in which sheet it is.
If you have 3 sheets with 4 cells you have to define 4 rows in the tFixedFlowInput and because of the iteration through the tFileExcelSheetList you get in the end 12 rows.
The component unfortunately does not support a cell range. To digger the values of cell ranges you should use the tFileExcelSheetInput.

Re: How to read same cell values from every sheet in a workbook

Anonymous — Mon, 09 Mar 2015 17:48:25 GMT

Re: How to read same cell values from every sheet in a workbook

Anonymous — Tue, 10 Mar 2015 14:45:11 GMT

Hi Jlolling,
The actual solution that I need that is perfect for what I want to do is here: https://community.talend.com/t5/Design-and-Development/Extracting-data-from-specific-Excel-cells-tutorial/td-p/84549
But I can only get the solution to select the specific cells on the first sheet (even if I select 'All Sheets' on the tFileInputExcel component) the whole solution does not cycle through all the sheets in the entire workbook.
Actually, in the screenshot below it looks like the 'All Sheets' selection is working as it is cycling through a significant amount of rows.
However, the tFixedFlowInput is only returning one row it would seem.
Maybe I need to use another component rather than the tFixedFlowInput?
Any suggestions?
Thanks,
Facoda

Re: How to read same cell values from every sheet in a workbook

Anonymous — Wed, 20 Jul 2016 16:30:42 GMT

I guess you see it more complex as it is actually.
Every input row of the component addresses one cell and it does not matter in which sheet it is.
If you have 3 sheets with 4 cells you have to define 4 rows in the tFixedFlowInput and because of the iteration through the tFileExcelSheetList you get in the end 12 rows.
The component unfortunately does not support a cell range. To digger the values of cell ranges you should use the tFileExcelSheetInput.

I'm not sure I understand. Increasing the row just duplicates the cell output. For example, if I cycle through 2 Sheets and 1 cell on each sheet then I just get 2 copies of the same value = 4 rows. That's if I put 2 rows in the FixedFlowInput?
Hello,
I am having the exact same issue and I don't understand how to select multiple cells.
I tried using an inline table in tFixedFlowInput, but I get that error :

Exception in component tFileExcelReferencedCellInput_2
java.lang.IllegalArgumentException: Row index must >= 1

Have you found a solution?