This example demonstrates how to replace values in all fields.

Question from the customer

We have just started to look at ETLing a CSV file from a new source that has over 50 columns and a mixture of data types (numeric, date, alpha).
The problem is that they have filled some values with the text 'withheld'. This includes columns that may have dates or numbers.
What I was wondering if there was 'neat'/'concise' way of just converting 'withheld' to NULL BEFORE performing any type of downstream transformation that may for example format a date as YYYY-MM-DD.

Data example

Source Data Example

Original forum post

The solution

The simplest way to transform the data is to use Replace transformation function. It works very well for small narrow files.  

Simple ETL

Replace string transformation function

For very wide files we recommend using field values transformation. It converts source data into pairs [Field Name]/[Field Value] 

ETL data flow

 Transformation

Data transformation

Transformation result

Data transformation result

Pivot

Pivot converts pairs [Field Name]/[Field Value] back to the original format

Pivot transformation

Pivot keys

Pivot transformation - Pivot keys

 Tip: Populating pivot keys can be very time-consuming. It is much faster to prepare a list of fields in Excel and insert it.

Pivot transformation tip

 To view the example follow the steps below

  • Download and install Advanced ETL Processor [Link]
  • Download and Unzip example[Link]
  • Create a new transformation and open the .ats file
  • Double-click on the Reader object and amend the source file path
  • Double-click on the Writer object and amend the target file path
  • Run the transformation by pressing the green arrow.

 

Please contact us if you need help with transforming the data

Visit ETL Tools Forum