Data360 Analyze

I have imported some .txt files with the "Fixed Format File" node. The .txt files contain a field 'date of birth' with the characteristics "yyyymmdd" with no "/" of "-" between the year, month and day. I guess that is the reason why I cannot select type "date" for this field in the Field Positions.

What would be the easiest way to convert this string into a date? Data analyzer does not recognize the field as a date (it becomes "int"). The custom node "convert to date" does not recognize it either.

Any suggestions?

Hi Daan,

General purpose data type conversion nodes such as the Data Converter and Data Analyzer will convert strings such as '19011225' to an integer since they are not aware of the semantic type of the string value (a date) and they are valid numeric values. Similarly, with nodes that recognize multiple date string formats it can risky to convert values to dates if the string does not have the delimiters to provide a clue to the format. 

In this case it is probably best to use a custom script in a Transform node to convert the string using a single format string. Attached is an example that will convert only values that are valid to dates (NULL and blank/whitespace values become NULL date values). Any records that contain invalid date strings are output to a second 'errors' output pin.

Regards,

Adrian

Attached files

Convert CCYYMMDD to Date - 10 Apr 2018.lna

Data3Sixty Analyze v.3.2.7 introduced the Modify Fields node that allows you to modify field metadata without scripting.

You can now use this node to convert strings with the above format to date values by configuring the 'Type' attribute to a date and specifying the input format in the Format attribute: