[resolved] How to count the number of column before import a file

[_AnonymousUser]

Hello,
Do you have an advice to give me please?
I need to count the number of columns of a file in order to link the file to the right schema.
i thought about:
tfilelist --> tfile properties --> if 48 columns --> Schema 1
                                      --> if 78 columns --> Schema 2
but i don't find the right syntax for the tfileproperties
thank for your help

[Anonymous]

tFileProperties is not dedicated to solve such task.
I suggest you read the first line as string (tFileInputDelimited with a delimiter which does not occurs in the content and limit to one line) and count the delimiters. 
In case of the first schema is a subset of the second schema (or similar) you could use the user component tFileInputTextFlat to solve your task. This component can setup the column position by a header line and is able to accept missing columns.
http://www.talendforge.org/exchange/index.php?eid=1278&product=tos&action=view&nav=1,1,1
Please take a look into the documentation before using it.

[_AnonymousUser]

Hello,
thank you for your feedback, this suggstion seems to be a great idea, i just don't know how count the delimiters 🙂
tfilelist --> inputfile (limit = 1 and delim = ";" (real delim = "~") --> count delim?
here is an example of the tlogrow
|1234567891012~M~Premium~Premium~~~~GAGNY~FRANCE~93220~00000000~~~12/06/1934~~~~~~~~~~~~~~187~~1~~8~~10~~~~~~~~0~~~93032~930320105~07/15/2009~~0~0~0~0~0~0~0~0~0~0~0~0~0~0~~~~~~~~~~~~~~~~10/30/2012|
thank you for your advice

[Anonymous · Accepted solution]

based on delimiter count you want to assign your schema then please follow below steps. 

create context variable counter(integer).
design your job as follow.

tFileInputFullRow---main---tJavaRow----main--tMap------firstSchema--tExtractDelimitedField
                                                                  |_ _ _ _ secondSchema--tExtractDelimitedField

Read your file line by line using tFileInputFullRow.
in tJavaRow synch the column and click on Generate code button then add below code in it. 

context.counter=0;
output_row.line = input_row.line;
context.counter=input_row.line.split("~").length;

Now you have field count then used tMap expression filter to redirect your flow based on filed count. 
context.counter==50 ( your first schema)context.counter==60 ( your second schema)
add tExtractDelimitedField component to parse relevant schema. 
this should work for you.

[_AnonymousUser]

Hello,
I found how :-)
output_row.count = StringHandling.COUNT(input_row.line,"~" );
many thanks for your advice

[Anonymous]

Hi Stephane,

The solution is short and sweet !!!, thanks for the update.

Vaibhav

[Anonymous]

Hi,
There is one more solution to this problem if you want to avoid java components as explained below (See job flow attached for more details):

Assuming that you have a comma separated text file and if you want to count the number of columns for that file, pull a tFileInputDelimited component and after configuring the parameters like path, keep only one column in the schema for this component with type as string (we will count the number of delimiters for fetching number of columns). Put the value for field separator anything except comma like semi column. 
Put the header and footer value as 0.
Link this component to a tMap and in the expression filter section of the tMap, find the number of commas: StringHandling.COUNT(row8.Col,",") + 1 . Here, Col is the name the field from tFileInputDelimited. We are adding 1 in order to find the number of columns in the file.
Add a tSampleRow to get only one record which will contain number of columns in our source file.
Attach this output to a LogRow to view the results.

Thanks,
Zaki Hasan.

[Anonymous]

Hi Zaki Hasan,
Thanks for sharing your solution on forum.
Best regards
Sabrina

[JBoudarat]

Hello!

My question it's pretty similar. I need to create some flow to allow pass only the lines who have "x" number of delimiters, in this case "|".

It's possible to have any advice? I have tried with the pretty much the same components but I'm not able to find the right function in the tFilter.

My flow is :

tFileInputFullRow---main--tFilter--main---tFileOutputDelimited.

Thanks in advance.