topic Re: How to read metadata in Excel header cells in Talend Studio

How to read metadata in Excel header cells

Anonymous — Thu, 22 Dec 2016 05:47:49 GMT

Hi there,
I have the following scenario, I must read several excel files with hundreds of sheets inside them (one for each day of the year); all the excel files have the same schema:
- The first row contains some metadata in grouped cells
- The second row contains the headers of the data
- From the third row we have the data
The goal is to read all the rows of all the sheets, apply some filter and string manipulation, then store all the filtered rows in a new excel file.
Everything works fine with a job with this sequence
tFileList ---> tFileInputExcel ----> tFilterRow -----> tMap ----> tFileOutputExcel
but now I need to add a further step: read from the first row (at the moment skipped in tFileInputExcel) the value of 2 grouped cells, keep them in some way (content variables ? tSampleFlow ? tFixedFlowInput ? subflow ? other ideas ?) throughout the flow and use the values in tMap to write as columns in tFileOutputExcel.
Some idea about how to accomplish this task ?
Thanks a lot
Gf

Re: How to read metadata in Excel header cells

Anonymous — Thu, 22 Dec 2016 07:11:05 GMT

Hi,

but now I need to add a further step: read from the first row (at the moment skipped in tFileInputExcel) the value of 2 grouped cells, keep them in some way (content variables ? tSampleFlow ? tFixedFlowInput ? subflow ? other ideas ?) throughout the flow and use the values in tMap to write as columns in tFileOutputExcel.

Could you please elaborate your case with an example?
Best regards
Sabrina

Re: How to read metadata in Excel header cells

Anonymous — Thu, 22 Dec 2016 11:51:04 GMT

Hi,

but now I need to add a further step: read from the first row (at the moment skipped in tFileInputExcel) the value of 2 grouped cells, keep them in some way (content variables ? tSampleFlow ? tFixedFlowInput ? subflow ? other ideas ?) throughout the flow and use the values in tMap to write as columns in tFileOutputExcel.

Could you please elaborate your case with an example?
Best regards
Sabrina

Hi Sabrina,
you can find an example in the attacched excel file.
I have the data to convert in sheets "INPUT1-1309" and "INPUT1-1409", while in the sheet "OUTPUT" you can find the result I need.
I have highlighted in YELLOW and ORANGE the cells of the two INPUT sheets that I need to replicate in the OUTPUT file.
Kind regards,
Gf
DataEntryExample.rar.rar

Re: How to read metadata in Excel header cells

Anonymous — Fri, 23 Dec 2016 06:11:36 GMT

Hi,
The attached excel file is not available on forum. Could you please take a look at it?
Best regards
Sabrina

Re: How to read metadata in Excel header cells

Anonymous — Tue, 27 Dec 2016 08:53:48 GMT

Hi,
The attached excel file is not available on forum. Could you please take a look at it?
Best regards
Sabrina

Here we are !
DataEntryExample.rar_20161227-0055.rar

Re: How to read metadata in Excel header cells

Anonymous — Tue, 27 Dec 2016 08:57:41 GMT

Hi,
The attachment link is ok with us. We will check it and then come back to you as soon as we can.
Best regards
Sabrina

Re: How to read metadata in Excel header cells

Anonymous — Tue, 27 Dec 2016 16:57:41 GMT

Give this a try.
excel_grpd.zip_20161227-0858.zip

Re: How to read metadata in Excel header cells

Anonymous — Mon, 09 Jan 2017 09:07:14 GMT

Give this a try.
excel_grpd.zip_20161227-0858.zip

Thank You, I tried to open the demo project, but it gives me the following error
java.lang.NullPointerException
    at org.talend.migrationtool.MigrationToolService.appendToLogFile(MigrationToolService.java:490)
    at org.talend.migrationtool.MigrationToolService.access$0(MigrationToolService.java:488)
    at org.talend.migrationtool.MigrationToolService$1$1.run(MigrationToolService.java:258)
    at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2313)
    at org.talend.migrationtool.MigrationToolService$1.run(MigrationToolService.java:474)
    at org.talend.repository.RepositoryWorkUnit.executeRun(RepositoryWorkUnit.java:99)
    at org.talend.core.repository.model.AbstractRepositoryFactory.executeRepositoryWorkUnit(AbstractRepositoryFactory.java:258)
    at org.talend.repository.localprovider.model.LocalRepositoryFactory.executeRepositoryWorkUnit(LocalRepositoryFactory.java:3311)
    at org.talend.core.repository.model.ProxyRepositoryFactory.executeRepositoryWorkUnit(ProxyRepositoryFactory.java:2059)
    at org.talend.migrationtool.MigrationToolService.executeMigrationTasksForLogon(MigrationToolService.java:481)
    at org.talend.repository.localprovider.model.LocalRepositoryFactory.executeMigrations(LocalRepositoryFactory.java:3395)
    at org.talend.core.repository.model.ProxyRepositoryFactory.executeMigrations(ProxyRepositoryFactory.java:283)
    at org.talend.core.repository.model.ProxyRepositoryFactory.logOnProject(ProxyRepositoryFactory.java:1847)
    at org.talend.repository.ui.login.LoginHelper$1.run(LoginHelper.java:428)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)

Re: How to read metadata in Excel header cells

Anonymous — Thu, 26 Jan 2017 12:52:16 GMT

Any other idea to help me solve the problem ?
There is a way to obtain the current row number of an excel file while processing it ? This could be a workaround, storing in a global variable the value of the first column when I'm on a first row and the use the variable for the following lines