How can I enclose values with double quotes in PDI?

Question

I want to create a csv file as output,but there are some NULL in the stream. I set the Text file output config like this:

content tab:

    separator --> ,

    enclosure: --> "

    force the enclosure around fields? --> yes

fields tab：

    Null --> ""

I use the Kettle 6.0 version, using the above config, the Null values are getting replaced by """""" string, if I don't set the default value "" for the Null in the fields tab, the Null values will not be enclosed by the double quotes in the output csv file. How can I generate a CSV file with the correct output?

Thanks in advance.

What are you expecting in the output file? Are you expecting "Null"? — Nikhil, Oct 19 '16 at 14:03
I wish expecting "",but the default value "" set by manual is no effect,the output file lose the double quotes. — Greg Anna, Oct 20 '16 at 02:29

score 0 · Accepted Answer · answered Oct 20 '16 at 20:41

Here's your solution:

Don't force the enclosure in the Text file output
Remove "" from fields --> Null
Use my example. It will do what you want:

<?xml version="1.0" encoding="UTF-8"?>
<transformation-steps>
<steps>
  <step>
    <name>Data Grid</name>
    <type>DataGrid</type>
    <description/>
    <distribute>Y</distribute>
    <custom_distribution/>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
    <fields>
      <field>
        <name>Something</name>
        <type>String</type>
        <format/>
        <currency/>
        <decimal/>
        <group/>
        <length>-1</length>
        <precision>-1</precision>
        <set_empty_string>N</set_empty_string>
      </field>
    </fields>
    <data>
      <line> <item>asda</item> </line>
      <line> <item/> </line>
      <line> <item>sadasd</item> </line>
      <line> <item/> </line>
      <line> <item>asdads</item> </line>
    </data>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>159</xloc>
      <yloc>125</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

  <step>
    <name>Text file output</name>
    <type>TextFileOutput</type>
    <description/>
    <distribute>Y</distribute>
    <custom_distribution/>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
    <separator>&#x3b;</separator>
    <enclosure/>
    <enclosure_forced>N</enclosure_forced>
    <enclosure_fix_disabled>N</enclosure_fix_disabled>
    <header>Y</header>
    <footer>N</footer>
    <format>DOS</format>
    <compression>None</compression>
    <encoding/>
    <endedLine/>
    <fileNameInField>N</fileNameInField>
    <fileNameField/>
    <create_parent_folder>Y</create_parent_folder>
    <file>
      <name>&#x24;&#x7b;Internal.Transformation.Filename.Directory&#x7d;&#x5c;file.txt</name>
      <is_command>N</is_command>
      <servlet_output>N</servlet_output>
      <do_not_open_new_file_init>N</do_not_open_new_file_init>
      <extention>csv</extention>
      <append>N</append>
      <split>N</split>
      <haspartno>N</haspartno>
      <add_date>N</add_date>
      <add_time>N</add_time>
      <SpecifyFormat>N</SpecifyFormat>
      <date_time_format/>
      <add_to_result_filenames>Y</add_to_result_filenames>
      <pad>N</pad>
      <fast_dump>N</fast_dump>
      <splitevery>0</splitevery>
    </file>
    <fields>
      <field>
        <name>quotes_bounded_something</name>
        <type>String</type>
        <format/>
        <currency/>
        <decimal/>
        <group/>
        <nullif/>
        <trim_type>none</trim_type>
        <length>-1</length>
        <precision>-1</precision>
      </field>
    </fields>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>547</xloc>
      <yloc>126</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

  <step>
    <name>Calculator</name>
    <type>Calculator</type>
    <description/>
    <distribute>Y</distribute>
    <custom_distribution/>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
       <calculation><field_name>quotes</field_name>
<calc_type>CONSTANT</calc_type>
<field_a>&#x22;</field_a>
<field_b/>
<field_c/>
<value_type>String</value_type>
<value_length>-1</value_length>
<value_precision>-1</value_precision>
<remove>N</remove>
<conversion_mask/>
<decimal_symbol/>
<grouping_symbol/>
<currency_symbol/>
</calculation>
       <calculation><field_name>prefixed_field</field_name>
<calc_type>ADD</calc_type>
<field_a>quotes</field_a>
<field_b>Something</field_b>
<field_c/>
<value_type>String</value_type>
<value_length>-1</value_length>
<value_precision>-1</value_precision>
<remove>N</remove>
<conversion_mask/>
<decimal_symbol/>
<grouping_symbol/>
<currency_symbol/>
</calculation>
       <calculation><field_name>quotes_bounded_something</field_name>
<calc_type>ADD</calc_type>
<field_a>prefixed_field</field_a>
<field_b>quotes</field_b>
<field_c/>
<value_type>String</value_type>
<value_length>-1</value_length>
<value_precision>-1</value_precision>
<remove>N</remove>
<conversion_mask/>
<decimal_symbol/>
<grouping_symbol/>
<currency_symbol/>
</calculation>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>279</xloc>
      <yloc>125</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

  <step>
    <name>If field value is null</name>
    <type>IfNull</type>
    <description/>
    <distribute>Y</distribute>
    <custom_distribution/>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
      <replaceAllByValue/>
      <replaceAllMask/>
      <selectFields>Y</selectFields>
      <selectValuesType>N</selectValuesType>
      <setEmptyStringAll>N</setEmptyStringAll>
    <valuetypes>
      </valuetypes>
    <fields>
      <field>
        <name>quotes_bounded_something</name>
        <value>&#x22;&#x22;</value>
        <mask/>
        <set_empty_string>N</set_empty_string>
        </field>
      </fields>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>399</xloc>
      <yloc>125</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

</steps>
<order>
  <hop> <from>Data Grid</from><to>Calculator</to><enabled>Y</enabled> </hop>
  <hop> <from>Calculator</from><to>If field value is null</to><enabled>Y</enabled> </hop>
  <hop> <from>If field value is null</from><to>Text file output</to><enabled>Y</enabled> </hop>
</order>
<notepads>
</notepads>
<step_error_handling>
</step_error_handling>
</transformation-steps>

How can I enclose values with double quotes in PDI?

1 Answers1