Four Stars

Data from database, write to CSV file some column values in quotation marks

Hi,

I have to achieve the following in Talend Open Studio for Data Integration:
Read data from a database (SQLServer), write the rows into a CSV file (value delimiter is ";").
The additional feature shall be, that the values of some columns shall be within quotation marks, also the headers (column names), in the CSV file.
How to achieve this? Is the component tFileOutputDelimited suitable for that?

Using a tMSSqlInput to get the data from the database, connect that component with a tFileOutputDelimited.
The output in CSV file will contain all data rows, but without the desired quotation marks for some columns.

Example to achieve:
"Column name 1";"Column name 2";"Column name 3";"Column name 4";"Column name 5"
"Value of type String";1;2017-7-11;"Value of type String";"Value of type String"
"Value of type String";2;2017-7-10;"Value of type String";"Value of type String"
...

In fact, text values (in database as varchar) have to be in quotation marks, perhaps in the future of some other data types as well, but for no just the String values.
When CSV file is written, it will mainly be opened in Excel.

Cheers

  • Data Integration
2 REPLIES
Ten Stars

Re: Data from database, write to CSV file some column values in quotation marks

On the Advanced Settings tab of your tFileOutputDelimited component, there's a checkbox labeled CSV Options. Enabling that will reveal some more settings, one of which is Text Enclosure. The default value should wrap Strings in quotation marks. It does not wrap header names in quotes.

If you need to customize your header, there are a few different ways you can create a static row of string values that you then write to your target file before filling it with data. Configure the header tFileOutputDelimited and the data tFileOutputDelimited with the same File Name, and make sure to check Append on the data output component so that data is added below the header.
Four Stars

Re: Data from database, write to CSV file some column values in quotation marks

In 6.4.1 I see under "CSV options" on the tFileOutputDelimited:

Escape char: """

Text enclosure: """

 

When I leave it with this default settings, every value is put in "".

 

What I described is, that only the values which are of type String (text values) shall be in "", not every other value type (int, BigDecimal, etc.), according to the schema.

 

Perhaps change those default settings in the "CSV options"?