How to automate the checking of column format dynamically?

[bsharbo]

Hello everyone.  I have an etl process that reads a file with 200+ Fields.  Each of these fields has a specific format that they are supposed to be in.  Aka field #1 should be text, #10 should be numeric, #13 should be a date in the format of MM/DD/YYYY   while  #22 should be a date in the format YYYYMMDD.

I have all of this metadata stored in a sql database table.  I would like AlterYX to be able to read in this metadata, and attempt to apply the correct 'conversions' on this Csv input file and be able to grab errors on any fields that are mistaken. 

ALteryx defaults to read in all csv file fields as Text, so it's first step acts like a staging step of an ETL.  I now want the tool to attempt to convert each field using the correct input data-type. I also want errors to be output to an independent table.  I do not want to have to write a formula to convert each field as a check as that would be 200+ formulas.   Also I do not want the entire module to FAIL on a incorrect format.  I want that row to be noted with the column that caused the error, but the system to continue it's processing.

Does anyone know if this is possible? I have struggled to find a way to do this thus far.  Thanks!

 

[Ned · Accepted solution]

First, you should check out the AutoField tool.  It scans a set of string fields and automatically detects what type they probably are.

If fields fall into a few different types that you want to convert, you can use a Dynamic Select for each type of conversion and then a multi-field formula to do the conversion.

If you want a more custom approach, you will need to convert all of your conversions into formulas that Alteryx understands (as strings) and then use the Dynamic Replace tool to dynamically execute them.