How to check a specific column name in tFileInputExcel by using talend studio

[LNYAKA1625749060]

Hello,

I have a two tFileInputExcel component. The first component have three columns names (A,B,C)and the second have

five columns names (A,B,C,D,E). How can I check if my tFileInputExcel have a specific column name ?

Example tFileInputExcel1---runif(contains column name D and C)---tmap1 or

tFileInputExcel1---runif(contains column name D and C)---tmap2

Thanks

[gjeremy1617088143]

Hi,i think dynamic shema is not supported for tFileInputExcel. Instead maybe you can use custom component :

take a look on Jan Lolling excel components :

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%252F1%252Fproducts%252F542&pi=marketplace%252F1%252Fproducts%252F542%252Fitems%252F2998

Send me love and kudos

[Anonymous]

Hello,

Are you trying to load unknown column data structure from your excel files?

As schemas must be defined during design, not at run time. How did you define your schema in tFileInputExcel component with unknown column data structure?

Best regards

Sabrina

[LNYAKA1625749060]

hello @Xiaodi Shi​ 

I have to process an excel file in a folder. This excel file will be put by a user.

And I have to process this file. There are two different treatments depending on the name of the column in the file. The first file has three columns (name, first name, age) and the second file has five columns (name, first name, age, sex, address).

What I want is to check if the file deposited by the user contains the columns sex and age to know if it is the first file or the second file.

[gjeremy1617088143]

@xdshi, maybe you now if dynamic schema was implemented for excel components on the latest version of studio?

if yes

you can use just one field in the fileinputexcel as dynamic type just read one line and in a main row linked tjavarow :

Dynamic columns = input_row.(your field name);

ArrayList<String> liste = new ArrayList<String>();

for (int i = 0; i < columns.getColumnCount(); i++) 

{  

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

String inp_col=columnMetadata.getName().toString();

liste.add(inp_col);

    };

then you can put the list of column name (here 'liste') in a globalvar and made your condtions on it after

[LNYAKA1625749060]

Hello @guenneguez jeremy​ @Xiaodi Shi​ 

Could you show it to me via screenshots?

Also is it possible to show me how I can recover the number of columns in my excel file (My excel file has only one sheet).

So if I test for my first excel file having the columns (name, first name) I will have three columns and if I test for my second excel file having the columns (name, first name, sex, address) I will have five columns.

Something like :

tfileInputExcel---main link --> tjavarow---tsetGlobalvar---->first processing if number of columns equals 3 otherwise processing 2

Can you help me to find how the code in tjavarow

[gjeremy1617088143]

I assume you use entreprise edition not open source:

if yes you set one field in tfileInptexcel schema with type as dynamic. (you can name it excel_column_count for example)

and if you just want to count columns

in the tjavarow :

int counter = (input_row.excel_column_count).getColumnCount();

globalMap.put("column_count",counter);

you add a an on subjob ok between tFileInputExcel and a tJava empty and after you can set a condition in a run if link ex:

(int)globalMap.get("column_count")==3

[LNYAKA1625749060]

Hello,

Yes, I have an enterprise version. I tried to count the number of columns in my excel file which has three columns and which is in a directory that I specified, but it does not work. 

I have attached my job and the different components. Can you tell me where I am stuck.

Also how to put a sub job ok between my tfileinputexcel and my java component.

[gjeremy1617088143]

greetings from france , i'm french too, in the tjavaRow just write:

System.out.println((input_row.header).getColumnCount());

to see if you have something

and don't use a tlogrow after

and delete output schema of tjavarow

[LNYAKA1625749060]

Hello @guenneguez jeremy​ 

Yes i'm french. It's work now.

But i have another question. I used a tfileinputexcel with one field (dynamic type). I will now to check if tfileinputExcel contains 10 columns, i retrieve its data in another excel file (tfileoutputxcel) by addiong to the first row the message "This file contains 10 columns" and the rest of rows remain unchanged in their respective columns