For Each / Enumerating All Columns?

rkm

• February 11, 2015 00:00

• Answered

Follow

I would like to run a transform function on all columns. To give you a quick idea in pseudo-code, it would be something like this:

$out.0.* = replace($in.0.*, "bad text", "good text");

Wildcards, used in my pseudo-code, won't work for this, but I figured there would be some way to enumerate columns. Something like:

foreach(column eachCol: $in.0) {
    loopCount = loopCount + 1
    $out.0.loopCount = replace(eachCol, "bad text", "goodtext");
}

However, CloverETL does not offer a datatype for a "column" and I can't find any way to enumerate the metadata despite hours of searching.

I do realize that I could hard-code the list; however, in this case I need to support a large range of metadata, many of which have over 100 columns. Having to hard-code the list for each of them is not practical in my case.

Thank you in advance for any ideas or workarounds!
rkm

• 

  dpavlis

  • February 12, 2015 22:50

  0

  Comment actions Permalink

  Hi,
  following is an excerpt from CloverETL documentation on for-each loop:
  
  The foreach statement is executed on all fields of the same data type within a container. Its syntax is as follows:
  
  

   foreach (<data type> myVariable : iterableVariable) Statement

  
  
  All elements of the same data type (data type is declared in this statement) are searched in the iterableVariable container. The iterableVariable can be a list or a record. For each variable of the same data type, specified Statement is executed. It can be either a simple statement or a block of statements.
  
  There are also functions which allows you to get the column type or set value of column where the column name is set dynamically - like getFieldType() or setStringValue() - see this reference for more.
  
  So, basically you can do something like this in CTL:
  
  

  for(integer i=0;i<length($in.0);i++){
    if (getFieldType($in.0, i)=="string"){
        setStringValue($out.0, i, replace(getStringValue($in.0, i), "bad text", "good text"));
     }
  }

  
  The example above iterates through all string fields of input, replaces "bad text" with "good text" and stores the value into output record, to field with corresponding order number - i.e. assumes that input and output have the same structure .
  
  Hope this helps...
• 

  rkm

  • February 16, 2015 08:35

  0

  Comment actions Permalink

  @David,
  Thank you so much, this helped immensely. I was using the wrong keywords and didn't locate these functions in searching.
  
  The trick was the dynamic method of referring to the metadata collection, getFieldType() along with setStringValue().
  
  In testing this, I realized that Clover attempts to auto-classify the datatypes of columns in CSV imports, rather than leaving them all declared as string (or at least leaving any quoted columns as strings). The problem is that many times these columns are not strings and it seems that Clover might not preview enough rows to realize this.
  
  Is there a way to indicate the number of rows to be used to determine the datatypes when reading CSV's, or if not, a method to force all fields imported to be treated as string and/or change the datatypes later in the process?
  
  Thanks again for the excellent assist on enumerating all columns in the metadata, great solution!
  RKM
• 

  dpavlis

  • February 16, 2015 23:12

  0

  Comment actions Permalink

  Well, if you are talking about the Designer's CSV metadata wizard, then it is quite simple to do a mass update on all fields and set them to string. The process is manual anyway so this is just one extra step where you select all fields and then set them to string data type.
  
  Of course, you can also create the metadata XML definition directly as the structure is relatively simple.

Please sign in to leave a comment.