Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

How to check if column exists in excel file

Highlighted
One Star

How to check if column exists in excel file

Hi,
I have a requirement where we are loading a set of excel files to DB. But one or two columns may be missing in the file. If that is the case, where a column is missing we have to insert a column with Null values.
Please let me know how do I check this. Thanks in advance.
To be more precise, in Talend we have defined Col1, Col2, Col3, .. Col10. But in excel file Col3 is missing. So in that case the job has to insert a new Column Col3 with null values.
Regards,
Rashmitha
Highlighted
Moderator

Re: How to check if column exists in excel file

Hi,
The missing one is null or empty string? Could you please try to  use row1.Column==null||row1.Column.equals("")?"null":row1.Column in tMap to see if it is OK with you?
Best ergards
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.
Highlighted
One Star

Re: How to check if column exists in excel file

Thank you for your reply. But probably you got my question wrong.
I have a Excel Input component with schema defined as follows:
Name   Type-String
ID type-Integer
Location Type-String
But in my excel file if Column ID is missing, Talend reads Location Column which is type String as ID Column and throws an error.  So I want to check if ID column exists in file and if it does not exist, I want to insert a new column ID with NULL values.
Hope the scenario is clear now. Awaiting a reply.
Regards,
Rashmitha.B
Highlighted
One Star

Re: How to check if column exists in excel file

Does anyone has any reply for this question?
Highlighted
One Star

Re: How to check if column exists in excel file

Well lol, that's not going to be that easy.
Talend stick with the column order, because files haven't necessarily a header, or he can be false.
First of all, i think you need to convert it into csv, easier to manipulate.
So you probably want to test only first line of file, and to adapt your file to this.
Like this : you compare your header with the schema and if column 2, 4 and 5 are missing, you open you file without defining column separator and add your real column separator to simulate a null column to each rows.
Excel isn't really your friend in this. 
Tell me if that may work for you.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog