topic How to divide the comma separate string into columns in Talend Studio

How to divide the comma separate string into columns

anitha_manohar — Sat, 16 Nov 2024 00:07:52 GMT

Hi,

I have a requirement to split comma separated string from the database to different column.

The string will have variable number of fields for each type.

I initially extracted all the fields along with the comma separated fields to a tfileoutputdelimited component to create a CSV output and it worked.

current Job:

Input format ->>>>>>> Output format

test1,test2,”data1,data2,data3”. ->>>>>>> test1,test2,data1,data2,data3

But now the requirement is to include CSV option in the output file, to enclose all text by quote and to escape the quote or any special character within the fields.

If I turn on this CSV option with the current job the output looks like

Input format ->>>>>>> Output format

test1,test2,”data1,data2,data3”. ->>>>>>> “test1”,”test2”,”data1,data2,data3”

The string is represented as a single column in the output csv.

but what is expected is

Input format ->>>>>>> Output format

test1,test2,”data1,data2,data3”. ->>>>>>> “test1”,”test2”,”data1”,”data2,”data3”

I tried using the tExtractDelimitedfield component , but For that we need to know what is the number of variable columns expected. We can add additional fields and add to field in the output only if it is not null. But this will limit the number of variable columns that can be added in the string.

Any suggestion on how this can be achieved without having to limit the columns in the string.

my current job structure is

DBOuput ->>>> tMap ->>>>

tFileOutputDelimited

csv option turned off in the

tFileOutputDelimited.

Thank you in advance.

Re: How to divide the comma separate string into columns

manodwhb — Mon, 24 May 2021 02:58:55 GMT

@manohar anitha ,check the tNormalize component may be works for you.

https://www.tutorialgateway.org/talend-tnormalize/

Thanks,

Manohar

Re: How to divide the comma separate string into columns

anitha_manohar — Mon, 24 May 2021 06:21:04 GMT

Hi,

I tried to Normalize but that creates multiple entries in the output file.

so tried to write the normalized string to a intermediate output file and then de-normalize the split record based on id to a the finale Output with csv option.

But that causes the same output again with quotes around the de-normalized data

Input format ->>>>>>> Output format

test1,test2,”data1,data2,data3”. ->>>>>>> “test1”,”test2”,”data1”,”data2,”data3”

Is there an option to add csv option after creating a csv file.