topic Re: Escaping double quotes in Talend Studio

Escaping double quotes

rishind — Fri, 15 Nov 2024 22:07:07 GMT

Hi,

I am reading from input file which is separated by "~"

I have a field/column which has below structure

first_name"~"number

First name contains values like DOS COOPERATIVA "DE" INDIA

I want exact name (with quotes) in output (

DOS COOPERATIVA "DE" INDIA)

I have used text enclosure "\""

Escape char """

But I am getting o/p as only

DOS COOPERATIVA or sometimes it fails.

What else settings do I need to change?

Re: Escaping double quotes

Anonymous — Thu, 09 Feb 2023 08:47:19 GMT

Can you show us an example file with more data for testing? all fields are around with double quotes?

Regards

Shong

Re: Escaping double quotes

jlolling — Thu, 09 Feb 2023 08:55:12 GMT

If your field delimiter is ~ you do not need to escape the " (double quotes) in your file content.

Could you show us the settings of the tFileInputDelimited?

Re: Escaping double quotes

rishind — Thu, 09 Feb 2023 09:13:41 GMT

hi, below is the screenshot of settings

Re: Escaping double quotes

rishind — Thu, 09 Feb 2023 09:19:19 GMT

Hi Shong,

File has below data format

"0000495195ININS"~""~""~""~""~""~"GEN_11"~"ACTIVE"~"2021-05-15"~""~""~"PARTY_5"~"DOS COOPERATIVA "DE" INDIA"~"ABC"

Re: Escaping double quotes

rishind — Thu, 09 Feb 2023 09:42:46 GMT

Also this record is getting rejected as it couldnt find ~ after EEE"

"0000495195ININS"~""~""~""~""~""~"GEN_11"~"ACTIVE"~"2021-05-15"~""~""~"PARTY_5"~"DOS COOPERATIVA DE EEE""~"ABC"

Re: Escaping double quotes

jlolling — Thu, 09 Feb 2023 10:17:35 GMT

OK, Now I see the problem. The text enclosure will be recognised to early. Actually a failure because the text enclosure could only be valid if is direct before a field separator or a line feed.

I would raise a Talend support ticket, to force Talend to fix that. But for the mean time I would preprocess the file and replace all <"~"> with <@~@> and read this file. The @ is only a suggestion, you need a char which does not appears in your content.

Re: Escaping double quotes

rishind — Thu, 09 Feb 2023 11:15:23 GMT

Thanks @Jan Lolling

I cannot preprocess the file.

need to find some alternate solution.

Re: Escaping double quotes

jlolling — Thu, 09 Feb 2023 16:08:06 GMT

Ok, in this case you need to read the whole line (use tFileInputDelimited and set here for field delimiter "") and have only one column (here line). In this example I use the pipe als field delimiter.

Then process the line in a tJavaRow with

output_row.line = input_row.line.replace("\"~\"", "|");

the next component is tExtractDelimitedFields and setup field delimiter with "|" and setup its output schema according to the actually expected schema for this file.

Re: Escaping double quotes

rishind — Fri, 10 Feb 2023 13:16:03 GMT

Hi @Jan Lolling

I found an alternative

1)change Text enclosure to ""

it will result in " around string like "DOS COOPERATIVA DE EEE""

2)use substring function

row1.first_name.substring(1, row1.first_name.length()-1)

to remove quotes.

It works but is very long process as I have 200 columns in my file

Talend should provide some alternate solution/functionality to deal this.