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.
The simplest way to transform the data is to use Replace transformation function. It works very well for small narrow files.
For very wide files we recommend using field values transformation. It converts source data into pairs [Field Name]/[Field Value]
Pivot converts pairs [Field Name]/[Field Value] back to the original format
Tip: Populating pivot keys can be very time-consuming. It is much faster to prepare a list of fields in excel and insert it.
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|