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

One Star

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

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

Accepted Solutions
Five Stars

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

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.

All Replies
Seventeen Stars

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

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

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

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
Five Stars

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

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

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

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

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

Hi Stephane,

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

Vaibhav
One Star

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

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.
Moderator

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

Hi Zaki Hasan,
Thanks for sharing your solution on forum.
Best regards
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.