topic Re: Remove quotes from comma delimited file in Talend Studio

Remove quotes from comma delimited file

Anonymous — Sat, 16 Nov 2024 09:09:53 GMT

Hello,

I have the following workflow:

tFileInputDelimited -> tmap -> databaseOutput

The program that created the csv file (and will do so daily) is adding quotes to the strings and dates, however, I need to remove these quotes prior to it being sent to tmap. How can I do this?

Re: Remove quotes from comma delimited file

TRF — Thu, 19 Oct 2017 08:23:22 GMT

Hi,

You may add a tJavaRow before the tMap component to remove the quotes from the concerned fields:

output_row.yourField = input_row.yourField.replaceAll("\"", "");

Re: Remove quotes from comma delimited file

Anonymous — Thu, 19 Oct 2017 11:43:24 GMT

When you say yourfield, what does this represent? The quotes show up in several places in the row, but not always the same place.

Re: Remove quotes from comma delimited file

Anonymous — Thu, 19 Oct 2017 12:47:21 GMT

I played with tjavarow and figured out how to use it.

Thanks

Re: Remove quotes from comma delimited file

Anonymous — Thu, 19 Oct 2017 13:00:57 GMT

I have one error I cannot get by. The csv has a date in quotes. I am receiving an error replaceAll undefined for date. How can I get past this?

Re: Remove quotes from comma delimited file

TRF — Thu, 19 Oct 2017 13:13:50 GMT

You're welcome

Re: Remove quotes from comma delimited file

cterenzi — Thu, 19 Oct 2017 13:42:18 GMT

Unless I'm misunderstanding, you can configure tFileInputDelimited to remove quotes around incoming values by checking CSV Options and setting Text Enclosure to "\""

Re: Remove quotes from comma delimited file

Anonymous — Mon, 13 Nov 2017 14:47:01 GMT

Thanks cterenzi. I hadn't considered selecting csv options for my tab delimited file to get rid of the double quotes universally throughout the tfileinputdelimited. Solved a lot of ad hoc changes I was making later on.

Re: Remove quotes from comma delimited file

Anonymous — Fri, 09 Nov 2018 19:56:01 GMT

I like this method.. Simple since built into the "File Delimited" meta data creation. You can also see result in the preview window (after refresh preview)

Re: Remove quotes from comma delimited file

vaibs_parab — Mon, 03 Jun 2019 13:39:07 GMT

As mentioned by @cterenzi, use CSV option checked in tFileInputDelimited component.

It will removes the starting and ending double quotes. No need to use tJavaRow or any other logic before passing to the tMap.

In other case, suppose a column book_name in which a data is coming as below:

id | book_name
---|----------------------------------
15 | "The Alchemist by "Pauolo" Cohello"

In such case, when you use CSV option checked, the output is:

The Alchemist by

As the CSV option is checked in input component, it finds for the ending (i.e. next double quote) double quote, and it will trim the reminder data.

To overcome this issue, use java's replace method in tMap as shown below:

row1.book_name.replace("\"","")

This will provide you desired output.

Give kudos if this works for you.

Please share any other way around apart from this if anyone has.

Regards,

Vaibhav