Query about preprocessing inbound file

Hi

We have just started to look at ETLing a CSV file from a new source which 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 performinng any type of downstream transformation that may for example format a date as YYYY-MM-DD.

I know in a date transformation I can catch the error and change to NULL. But this is problematic if there really is a problem with the date (eg, they all of a sudden change from one format to another). Also, I have found them put 'withheld' in a Currency Column. Which is 8 characters long and Currency Codes are typically 3. Therefore, when it writes to the database it will exceed the column length.

Therefore, short of conditioning every one of the offending columns which would create a lot of diagrammatic 'noise' I am not sure of a concise way of doing this short of having some type of pre-package step to run something like SED and convert everything to a null before getting into the transformer.

BTW, I can do the external call to something like SED or GAWK but it just generates more management overhead with temporary files etc.

Cheers

Hi Bruce

I have created a working example for you
www.etl-tools.com/automation/0021-replac...s-in-all-fields.html

I hope you will find it useful

Thanks mate.

Hi as a followup on this I was wondering if someone could explain how to Copy&Paste the Pivot keys.

Do I need the copied data to be the No, PivotKey, OutputKey or just the PivotKey where do I highlight to do the paste?

I have tried several different ways and I generally get an error message that the PivotKey needs a value which is what I am trying to do

thanks in advance

Here are some screenshots for you