topic Re: Variable Number of Delimited Fields in Talend Studio

Variable Number of Delimited Fields

Anonymous — Sat, 16 Nov 2024 13:21:20 GMT

Hi,
I need help with a task in Talend.
I have a delimeted tab file, but the number of columns is variable in each row.
Let me sample it:
john 23 productx productx add-info
jack 25 productx add-info
july 33 productx productx productx productx add-info
Theres no reserved space for products, and I need to get the add-info after.
Example (output that I need to generate):
john 23 productx productx (blank) (blank) add-info
jack 25 productx (blank) (blank) (blank) add-info
july 33 productx productx productx productx add-info
Don't know if I made myself clear enough.... But thanks for any help.

Re: Variable Number of Delimited Fields

Anonymous — Wed, 14 Jul 2010 08:06:28 GMT

Hope someone from Team Talend take a look at your case 🙂 it's kinda pretty hard :-)
These are the things that make the input file complicated:
1. the unknown number of productx
2. there is no identifier or key to each column(this will help us to determine the header value)
3. the "add-info" has no permament place.
4. it is not typical input flat file :-)
But still, your case is very much interesting 🙂

Re: Variable Number of Delimited Fields

alevy — Tue, 20 Jul 2010 07:10:34 GMT

If you read across each row, how do you tell which value is a "productx" and which is "add-info". Is it just that there is always exactly one "add-info" as the last value in a row?

Re: Variable Number of Delimited Fields

Anonymous — Tue, 20 Jul 2010 07:28:03 GMT

Hi alevy,
It seems like, the add-info always at the end, assuming yes, how can this be done?

Re: Variable Number of Delimited Fields

alevy — Tue, 20 Jul 2010 08:16:31 GMT

Well, assuming:
-- we know only that there is always exactly one "add-info" as the last value in a row
-- we do not know the maximum number of "productx" there can be on any row
-- the output is also to a delimited file
-- the "add-info" must remain the last value in the row
Then we need to first read the file to find the maximum number of "productx" across all rows. Use tFileInputFullRow and send to tMap. There define a new field ProductCount = StringHandling.COUNT(row1.line,"\t")-2. The output of tMap goes to tAggregateRow, which calculates the max of all ProductCounts. The output of tAggregateRow goes to tSetGlobalVar.
Then link the first tFileInputFullRow to another identical tFileInputFullRow using OnSubjobOK. The flow from the second tFileInputFullRow goes to tJavaRow, which contains the following code:

Integer LastDelimiter = input_row.line.lastIndexOf('\t');
output_row.line = input_row.line.substring(0,LastDelimiter)
   +StringHandling.STR('\t',(Integer)globalMap.get("MaxProductCount")-StringHandling.COUNT(input_row.line,"\t")+2)
   +input_row.line.substring(LastDelimiter);

The flow from tJavaRow should be what you need to write to tFileOutputDelimited.

Re: Variable Number of Delimited Fields

Anonymous — Tue, 20 Jul 2010 09:09:21 GMT

Hi alevy,
Confirmed, it works! (assuming it is delimited) You are great

Re: Variable Number of Delimited Fields

Anonymous — Mon, 26 Jul 2010 21:25:59 GMT

Thanks alevy and lovely, will try this solution asap.

Re: Variable Number of Delimited Fields

Anonymous — Wed, 04 Aug 2010 19:30:35 GMT

Hi, sorry for taking so long to do the test, but I was really busy.
I tried your solution alevy, but is just outputted the same file that was inputted. Did I miss something?
Thanks.

Re: Variable Number of Delimited Fields

alevy — Fri, 06 Aug 2010 07:33:32 GMT

I'd have to say: probably . Did you set MaxProductCount correctly in tSetGlobalVar? Add a tLogRow after tAggregateRow and a tJava with the following code after tSetGlobalVar to test that the max has been correctly stored.

System.out.println((Integer)globalMap.get("MaxProductCount"));

If both print the same result then put up screenprints of your job.

Re: Variable Number of Delimited Fields

Anonymous — Fri, 06 Aug 2010 09:00:56 GMT

Geeez it's so nice to be back in Talend ( got hooked with Silverlight project recently and it's driving me nuts!!!)
MaxProductCount -> This did the trick, right Alevy? You must know the maximum count of the productx so that the add-info will be put on the nth(maxproductcount) place.