topic [resolved] how to replace in a field when the row separator is also in Talend Studio

[resolved] how to replace in a field when the row separator is also

Anonymous — Sat, 16 Nov 2024 12:33:57 GMT

Hello,
I have a delimited file (field separator "\t", row separator "\n") looking like :
1 \tblue \tapple \n
2 \tred \n
\tpeer \n
tFileInputDelimited will fail on line 2 ...
So my idea was to replace the row separator so I will be able to replace \n by "" in the field. Bbut I didn't found a component for that

regards

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 15:39:07 GMT

You want to replace the \n of the second line, the one in the middle?

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 15:41:54 GMT

You want to replace the \n of the second line, the one in the middle?

I have modified line 2 in my post to reflect what's real

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 15:58:13 GMT

So actually it's not really a tFileDelimited. Do you have only 2 columns?

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:00:04 GMT

no the file have 15 columns

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:04:18 GMT

But I don't really get it. Could you post a part of what you have and what you want?

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:06:15 GMT

in my example the expected result will be :
1\tblue\tapple\n
2\tred\tpeer\n

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:19:11 GMT

But why the first line has the right format?

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:30:14 GMT

Because it has been entered correctly in the frontend application I guess
In line 2 the operator hit the return key by accident

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 16:42:16 GMT

So sometimes you want to replace the \n, sometimes not?

Re: [resolved] how to replace in a field when the row separator is also

janhess — Thu, 17 Nov 2011 16:49:06 GMT

You neet to replace \n\t with \t anywhere in the file.

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Thu, 17 Nov 2011 19:16:10 GMT

there's no \n\t in the file. Only "string\t..." and "...string\n"
and BTW there could be more than one \n (...string\n\n\n) at a time because the field has been designed as multi line in the front end app ...
Regards
Didier

Re: [resolved] how to replace in a field when the row separator is also

janhess — Fri, 18 Nov 2011 09:06:34 GMT

There's a \n\t in your example.

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Fri, 18 Nov 2011 09:43:40 GMT

janhess,
I agree with you.
I think I haven't gave enough explanations to understand my problem.
So :
actually the file I'm trying to load is handled by Microsoft bcp utility. bcp doesn't care of misplaced CRLF. What I have understood is bcp seams to ignore the row separator so a row can be on several lines in the file, bcp loads the data using the field delimiter until the en of the file.

What I need is to reproduce this with Talend

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Fri, 18 Nov 2011 09:45:28 GMT

Upload somewhere the file and post http link to it. Would be best option look at the real data.
Ladislav

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Fri, 18 Nov 2011 10:09:56 GMT

See screenshot.
At line 34543 the field 10 (3 - Tchaïkovski: Lac des cygnes...) contains multiple CRLF. the real row separator is at line 34549. The schema contains 16 columns.

Hope that helps

Re: [resolved] how to replace in a field when the row separator is also

janhess — Fri, 18 Nov 2011 11:23:02 GMT

Can you make your line break the NULCRLF?

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Fri, 18 Nov 2011 11:44:48 GMT

I see.. there is also the NULL character. So in content of file it seems there is always only CRLF, but end of line is always defined as NULCRLF sequence. You schould first read whole file to a String variable and do something like in tJavaRow:

// Make custom end of lines with char sequence "~$"
context.sWholeFile = context.sWholeFile.replace("\0\r\n","~$" );
// Remove CRLF from content of file
context.sWholeFile = context.sWholeFile.replace("\r\n","" );
// Create Windows standard end of line
context.sWholeFile = context.sWholeFile.replace("~$","\r\n" );

Ladislav

Re: [resolved] how to replace in a field when the row separator is also

janhess — Fri, 18 Nov 2011 11:52:48 GMT

Or you could make the separator \0\r\n and replace the \r\n in a tMap on the field.

Re: [resolved] how to replace in a field when the row separator is also

Anonymous — Fri, 18 Nov 2011 12:34:57 GMT

sorry but the end of row is not still NULL CR LF