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
4 REPLIES
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.
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
One Star

Re: How to check if column exists in excel file

Does anyone has any reply for this question?
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.