Remove quotes from comma delimited file

[Anonymous]

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?

[TRF · Accepted solution]

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("\"", "");

[Anonymous]

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

[Anonymous]

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

Thanks

[Anonymous]

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?

[TRF]

You're welcome

[cterenzi]

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

[Anonymous]

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.

[Anonymous]

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)

[vaibs_parab]

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