tFileExcelSheetInput Component not able to read the column name using the option Name in Header

[VK2]

Hello All,

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:

	4Mth Bias	YTD Bias	Jun-18	Jul-18
CH Less New Item Exceptions	2%	1%	74%	70%
Grand Total CH	3%	2%	84%	65%

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.

Error

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
[statistics] connected
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!
    at de.jlo.talendcomp.excel.SpreadsheetInput.configColumnPositions(SpreadsheetInput.java:791)
    at ms_cs_rdmp_esb_us.excel_0_1.Excel.tFileExcelSheetInput_1Process(Excel.java:1151)
    at ms_cs_rdmp_esb_us.excel_0_1.Excel.tFileExcelSheetList_1Process(Excel.java:636)
    at ms_cs_rdmp_esb_us.excel_0_1.Excel.tFileExcelWorkbookOpen_1Process(Excel.java:422)
    at ms_cs_rdmp_esb_us.excel_0_1.Excel.runJobInTOS(Excel.java:1635)
    at ms_cs_rdmp_esb_us.excel_0_1.Excel.main(Excel.java:1469)
[statistics] disconnected
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]

[Anonymous]

Can you use a String for the column header in Excel? Excel stores dates internally as numbers: what you see in the workbook is just the formatted date value, and not what is "really" in there. If you change the column date type to Number, you should see what value is actually in the cell.

[VK2]

Hi DVSCHWAB,

Thank you for your reply.

No, I cannot use String because the data it is holding is Big Decimal and also percentages.

Yes, In excel I have changed that column to Number type and I can see the values like 43282 - Jul 2018, 43313 - Aug 201, etc and I understand the concept of number date in excel.

But how to use that in Talend ?
How to read that in Talend, could you please help to read that in Talend.

[Anonymous]

Have you tried checking the "convert date column to string" on the Advanced tab of tFileInputExcel? Here's the documentation on that (I haven't done this myself):

https://help.talend.com/reader/jomWd_GKqAmTZviwG_oxHQ/NbQZuwgNcj_FdUhPpR3dXQ

[VK2]

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.

[VK2]

anyone ?

[Anonymous]

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:

    while(more columns)

        process row

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.

[Anonymous]

Hello,

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.

Best regards

Sabrina