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

Six Stars

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

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)".

 SourceFile.PNG

 

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".

 

ComponentSettings.PNG

  

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]

 

Eight Stars

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

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.
Six Stars

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

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.
Eight Stars

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

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
Six Stars

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

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.

 

Six Stars

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

anyone ?
Eight Stars

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

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.

Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.