topic Re: How to check if column exists in excel file in Talend Studio

How to check if column exists in excel file

Anonymous — Wed, 09 Dec 2015 09:35:48 GMT

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

Re: How to check if column exists in excel file

Anonymous — Thu, 10 Dec 2015 10:15:38 GMT

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

Re: How to check if column exists in excel file

Anonymous — Thu, 10 Dec 2015 11:10:51 GMT

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

Re: How to check if column exists in excel file

Anonymous — Tue, 22 Dec 2015 13:34:45 GMT

Does anyone has any reply for this question?

Re: How to check if column exists in excel file

Franz3 — Tue, 22 Dec 2015 14:22:33 GMT

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.