Extract a date from a filename

A common scenario is that an important date is contained within a filename or tab name of an upload. This needs to be extracted and written to a column. Sometimes, the dates in the filename are in multiple formats.

In the import stage (usually the first stage) configure these operations:

1. Append Metadata: Stage input name
   Use the Append Metadata operation to add the Stage Input Name. This will write the filename to a column.
2. Date Cleanse
   Use the Date Cleanse operation to identify and extract dates within the filename column. Because the operation is searching for a date within a string, a specific date format should be specified – ‘Auto’ and ‘Auto US’ options won’t work in this use case. Output the extracted date to a new column.

Dealing with multiple date formats

If there is more than one date format present in the filenames, perform the above operations, then:

1. Date Cleanse In the Date Cleanse operation, add a new group of input columns for each date format. Output each format to a different new column. Disable the validation report for this operation, since we’d always expect some rows to not match the format and trigger a warning.

2. Calculate
   Combine the two new date columns into a single column using a conditional statement in Calculate:
   

   IF('Date full US'="",'Date short US','Date full US’)

3. Map Column Headers
   Now add the import stage Map Column Headers to map all the incoming files to the target schema. The additional columns created in the Date Cleanse above can be dropped, along with the filename column if no longer needed.
   
   
4. Date Cleanse (general)
   Perform a general Date Cleanse to check other incoming date columns, with validation enabled. The new column of extracted dates should be added here. This will report if a new date format has been encountered in the filenames which results in blanks in this column.