My source is excel where I want to read only first 3 columns and a dynamic column where the column name contains month and year and the data will have decimal data
please find below sample header names and data:
CH Less New Item Exceptions
Grand Total CH
Here in Excel File, the cell header name is showing as "Jul-18 (MMM-YY)" but If I click on that then it is showing as "7/1/2018 (M/d/YYYY)".
So I used this component to read specific column by giving the exact column header name by selecting the option "Use Individual column configuration -> Use header to config position of columns".
I am getting below error that "Column with name: jul-18 does not exists in header!":
I tried by giving different formats, but still it is failing.
Please help me to give proper date format in "Name in header" to pull the column data.
Please let me know, how to achieve this.
Starting job Excel at 12:46 20/11/2018.
[INFO ]: ms_cs_rdmp_esb_us.excel_0_1.Excel - TalendJob: 'Excel' - Start.
[statistics] connecting to socket on port 3667
Sheet Name : GG
[FATAL]: ms_cs_rdmp_esb_us.excel_0_1.Excel - tFileExcelSheetInput_1 Column with name: jul-18 does not exists in header!
Exception in component tFileExcelSheetInput_1
java.lang.Exception: Column with name: jul-18 does not exists in header!
Picked up _JAVA_OPTIONS: -Djavax.net.ssl.trustStore=c:\windows\sun\java\deployment\trusted.cacerts
Job Excel ended at 12:46 20/11/2018. [exit code=1]
Yes, I have checked that but I cannot use tFileInputExcel component because in my requirement the column is not fixed, it is dynamic column.
Every month one column will be added that too in the middle of the headers.
I believe the licensed version of Talend has the ability work with dynamic schemas. I haven't done this, so I'm not sure if it will solve your problem, but it's probably worth a look.
If that's not an option, you might try using tJavaRow to process the file. This would let you run through an arbitrary number of columns one row at a time using a while() loop; the additional date column that is added each month would get processed along with all the other columns:
You'd have to write the Java yourself and deal with the overhead that Talend handles for you, but I can't think of another way to do what you're asking.
The dynamic schema is not supported by the tFileInputExcel component in talend.
With this custom component tFileExcelSheetInput, feel free to contact the author Jan Lolling please.
Try Talend Cloud free for 30 days.
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.