Configuration of tFileInputDelimited

[idembel2]

Hello All,

I have another chalenge, so may somebody help me please.

I have a csv file which look like that

ID	Name	Comment	Poste
1	toto	tre;gtt	director
2	titi	dftgrtg	ceo
3	tata	dfgtg	technician
4	tutut	ytr;nhhc	ttt
5	lolo	dcvdrf	ttt
6	lala	dfdf	ttt

I configure tFileInputDelimited component like in attached file. 

My problem is that in comment field, sometime user can put symbol ; in there comment and this impact my output.

So i think the option "Dynamic setting" of this component can help me to solve it, but i don't know how to do it.

Or if somebody can advice me other solution.

Thanks,

[TRF]

Can you have Comment as the las field?
In this it should be possible without dynamic schema.

[idembel2]

Hello TRF,

Make pleasure to read you.

No i can't, this is the input source i got, i have not hand on the structure.

Thanks,

[Sid3]

Question 1: Why your CSV file data in TAB delimited format?

Question 2:if ; is present in your COMMNT field, what you want to do ? do you need to replace this character with blank?

If yes use below function
row1.comment.replaceAll(";","")

if No please help us to understand your query?

Thanks,
Sid
-Mark as solution if this resolved your issue

[idembel2]

Hello Si4U,

Response1: No it is not TAB delimited, it is in semi-column delimited file. please check my attached file in my first post

Response2: The situation is not to replace or delete data.

I get this CSV file like input and i should split it to many files regards the Poste type (last column)

So my job looks like that:

tFileInputDelimited  ---- > tMap  ------ > tFileOutputDelimited (x)     === > (x) to say 1,2,3 files

I get wrong output regards of semi_column ";" present in comment column

Thanks,

[cterenzi]

If you have delimiters in a data value and the value is not wrapped in quotes and the delimiter is not escaped somehow, you have malformed data.

Your options are to have the delimiter changed, get quotes around values or have delimiters within a value escaped somehow.

[Irshad1]

Hello 

Please check the data in data viewer for proper formatting before loading into target.you will get clear picture where is it mis-matching

Thanks!

[TRF]

Hi,

Place the following code into a tJavaRow and connect it to a tFileOutputDelimited with "@" (for example) as a separator, you'll get what you expect regarding the comment field.

Edit: I missed to tell you to read the input file using tFileInputFullRow.

String line = input_row.line;
output_row.id = line.substring(0, line.indexOf(';'));
line = line.substring(line.indexOf(';')+1);
output_row.name = line.substring(0, line.indexOf(';'));
output_row.poste = input_row.line.substring(input_row.line.lastIndexOf(';')+1);
line = line.substring(line.indexOf(';')+1);
output_row.comment = line.replace(";" + output_row.poste, "");

Edit: you can now retrieve your file using a classical tFileInputDelimited (with "@" as a separator) with no problem due to the ";" included in comment field.

The 2nd part of the chalenge (1 file per poste value) is an other story.

This post https://community.talend.com/t5/Design-and-Development/Splitting-up-into-multiple-CSV-files-based-on... and @rhall_2_0 post may help for this.

[idembel2]

Hello All,

Sorry for all of this time, i mean it is very important to let you know about this case.

@TRF

Your suggestion works, i test it but the structure i put in my post is just an exemple

So in reality, my source file contains after field comment at least 10 fields.

And it is alos possible that in this field, somebody insert during its comment twice ';' , i have not control on it

I decide to inform about file source formated, i am so in attempt of response.

Thanks,

[TRF · Accepted solution]

You can have 10 or more fields after comment field, the impact is just a few more Java lines in the tJavaRow component.
Also, having more than one ";" in the comment field should not be a problem as we remove all fields arround comment before to get its content as is.
From my point of view it should works.