Generic Reformat to Trim all strings

paulhbartosik

• November 30, 2017 00:00

• Answered

Follow

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.

<?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

  • December 03, 2017 01:04

  0

  Comment actions Permalink

  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

  • December 05, 2017 20:14

  0

  Comment actions Permalink

  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.

Please sign in to leave a comment.