Generic Reformat to Trim all strings

How-to(s), solutions to common problems, pearls of wisdom

Paulhbartosik
Posts: 9
Joined: Wed Sep 06, 2017 4:14 pm

Generic Reformat to Trim all strings

Postby Paulhbartosik » Thu Nov 30, 2017 10:21 pm

Our accounting system often pads strings with spaces. I have to trim() strings all of the time. Usually I realize it when my graph fails.
Using Record Functions, I created a very simple Reformat component that trims all of the strings in the input data. It is generic, and works with any metadata. It saves me a lot of time and trouble.



Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<Graph author="phb05" created="Thu Nov 30 08:26:24 EST 2017" guiVersion="4.1.0" id="1512056374486" licenseCode="CLP1DHEALT49098262BY" licenseType="Commercial" modified="Thu Nov 30 08:26:24 EST 2017" modifiedBy="phb05" name="Default graph name" revision="1.0" showComponentDetails="false">
<Global>
<GraphParameters>
<GraphParameter name="__CLOVER_OriginalGraphName" value="/Work in Progress/graph/trimstrings.grf"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node guiName="Trim Strings" guiX="269" guiY="318" id="TRIM_STRINGS" type="REFORMAT">
<attr name="transform"><![CDATA[//#CTL2

//#CTL2
// Transforms input record into output record.
integer columncounter;

function integer transform() {
   /* first copy all fields */
   copyByPosition($out.0,$in.0);
   /* Then trim the strings */
   for(columncounter = 0; columncounter < length($in.0); columncounter++)
   {
   if (getFieldType($in.0, columncounter) == "string") 
      {
      setStringValue($out.0,columncounter, trim(getStringValue($in.0, columncounter)));  /* set to the trimmed string value */
      }
   } // end for next
   return ALL;
}

]]></attr>
<attr name="guiDescription"><![CDATA[Find strings in the metadata and trim them]]></attr>
</Node>
</Phase>
</Graph>

the_goat
Posts: 21
Joined: Fri Dec 01, 2017 6:16 am

Re: Generic Reformat to Trim all strings

Postby the_goat » Sun Dec 03, 2017 2:04 am

Or, alternatively it is possible to use trimming function embedded in reader components. Just enable "Trim" attribute on the field(s) you'd like to have trimmed: http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/details-pane.html. If this level of granularity is undesirable and FlatFileReader component is used, then simply turning on input trimming can be used: http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/flatfilereader.html#trimming-data.

It is completely valid solution but may be a problem with time and/or performance sensitive applications.

Paulhbartosik
Posts: 9
Joined: Wed Sep 06, 2017 4:14 pm

Re: Generic Reformat to Trim all strings

Postby Paulhbartosik » Tue Dec 05, 2017 9:14 pm

Thanks for the suggestion. However, setting Trim to "true" did not seem to make any change in my data. The trailing spaces remain.

Even if it did work, it would take a lot of time to set Trim to "true" for hundreds of fields in my metadata.
I appreciate the comment about performance. If I see performance problems, I can shift my trim statements to the SQL:

Select trim(fieldname) as fieldname, trim(fieldname2) as fieldname2
from tablename

I am using DBInputTable against a MS-SQL database.