How to read metadata in Excel header cells

One Star

How to read metadata in Excel header cells

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
Moderator

Re: How to read metadata in Excel header cells

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to read metadata in Excel header cells

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
Moderator

Re: How to read metadata in Excel header cells

Hi,
The attached excel file is not available on forum. Could you please take a look at it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to read metadata in Excel header cells

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
Moderator

Re: How to read metadata in Excel header cells

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to read metadata in Excel header cells

One Star

Re: How to read metadata in Excel header cells

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)
One Star

Re: How to read metadata in Excel header cells

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