Data Tools only work for Windows XP. Click Here for a solution

Jul 28

Written by:
7/28/2011 7:53 AM 

Question:

I have a source text file that contains a field in which there are dates stored in five different character string date formats. How can I standardize the dates to a single format?

Answer:

Using Map Designer in any of the Pervasive DataTools Data Extractors or Data Loaders, you can transform a variety of date formats into one standardized date format in your target file or table.

Objective

There is one objective in this sample transformation map, as follows:

• Use the Rapid Flow Integration Language (RIFL) to write an expression in a target data field to transform a variety of date formats from the source file into a one standardized date format in the target data file.

Skill Level

• Basic

Skill Set

• Map Designer
• Basic RIFL Scripting
• Basic understanding of date formats and the importance of storing dates in a single, standard format

Design Considerations

The principal design consideration is to choose the date format that fits your business process requirements. Following this decision, the task is to write a RIFL expression that converts any date in the source file to the chosen standardized target date format.

The DataTools product used in this sample transformation map is the Data Loader to CSV Text.

The selected standardized date format in this sample is mm/dd/yyyy.

Procedure

Launch any of the DataTools Data Loader to CSV Text. Create a new transformation map, connect to the source and target data files, and perform the data field mapping and expressions, as follows:

Source Connection

The Source Connection tab is where you specify the source connector, select the source data file or table, and specify any special properties about your source data file. For this sample select the following:

• Source Connector: ASCII (Delimited)
• Source File/URI: src_standard.txt
• Description: The source is a simple CSV text file with sample dates in various standard and non-standard formats. NOTE: The sample CSV text file was not installed with DataTools. You can download the sample source file using the link below and save it to any folder to which you have access/permissions.

Target Connection

The Target Connection tab is where you specify the target connector, enter the location and filename of the target data file or table, and specify any special properties about your target data file. For this sample select the following:

• Target Connector: ASCII (Delimited)
• Output Mode: Replace File/Table
• Target File: trg_standard_date_format.asc
• Description: The target is another CSV text file with one field/column. You may write the target file to any folder to which you have access/permissions.

Data Field Mapping and Expressions

This is where you create the schema of the target data file and write expressions to transform data on the fly when the transformation map is executed. In this sample there is only one source field and there will be only one target field when the target file is created.

1. On the Map Fields tab, map the source field to the target schema by dragging and dropping either the asterisk or the source field name (Field1) from the source grid on the left to the topmost empty row in the target grid on the right.
2. Still on the Map Fields tab, click in the Target Field Expression cell for the Field1 target field to open the RIFL Expression Builder.
3. Write the following expression on multiple lines in the Expression Builder window for the target field. Each of these is used to transform one of the five specific date formats/masks in the source data file to a standardized date format in the target file.



a = DateValMask(Fields(""Field1""), ""m/dd/YY1900"")

b = DateValMask(Fields(""Field1""), ""mm/d/YY1900"")

c = DateValMask(Fields(""Field1""), ""mm/dd/YY1900"")

d = DateValMask(Fields(""Field1""), ""m/d/YY1900"")

e = DateValMask(Fields(""Field1""), ""mmddYY1900"")



4. Continue writing the following expressions on subsequent lines in the same Expression Builder window. Each of these expressions evaluates the source date and calls the specific DateValMask expression (above) to convert the date to the standardized date format.



If Mid(Fields(""Field1""), 2, 1) Like ""/"" And Mid(Fields(""Field1""), 5, 1) Like ""/"" Then a

ElseIf Mid(Fields(""Field1""), 3, 1) Like ""/"" And Mid(Fields(""Field1""), 5, 1) Like ""/"" Then b

ElseIf Mid(Fields(""Field1""), 3, 1) Like ""/"" And Mid(Fields(""Field1""), 6, 1) Like ""/"" Then c

ElseIf Mid(Fields(""Field1""), 2, 1) Like ""/"" And Mid(Fields(""Field1""), 4, 1) Like ""/"" Then d

ElseIf Mid(Fields(""Field1""), 3, 1) Like ""[0-9]"" Then e

End If



5. Click OK to close the RIFL Expression Builder window.

Save Your Map

As with any work you do, we recommend that you save your work periodically. So click on the Save Map button in the button bar and save the transformation map. You can accept the default filename, or name it something like: My_Date_Standardizing_Sample.map.xml

Just be sure to retain the .map.xml file extension.

Validate Your Map

To verify that the expressions you entered in the map are syntactically correct, click on the Validate Map button in the button bar. If the resulting message indicates that the map is valid, proceed to the Run Your Map section. But if the resulting message indicates that the map is not valid, go back to steps 3 and 4 above where you entered the expressions and correct any mistakes, typographical errors, etc. that would cause the error. If you made any changes, save your map again.

Run Your Map

Finally, you get to run your map, create the target file, and see the results of your work. Click on the Run Map button in the button bar. When the map has finished running, proceed to View Your Target Data.

View Your Target Data

If all worked as intended, you just created a target data file into which 13 records were inserted, each of which contains the source date but in a single standardized format. To view the contents of the target data file without having to exit the Data Loader, click on the Target Data Browser button in the button bar. You can verify that the data is accurate and complete by also opening the Source Data Browser and placing the source and target browser windows side by side on your workstation's desktop.

More Detailed Information

Date conversion is one of the most common and difficult tasks in almost any data project. The most important thing to remember about the DateValMask function is that the mask references the source date format, not the desired target date format. For example, a source date of 7/7/78 is represented by the mask m/d/yy. The field expression to convert this source date to a mm/dd/yyyy format is...

DateValMask(Fields(""sourcedate""), ""mm/dd/YY1900"")

...where ""sourcedate"" in this expression is replaced by the field name of the source data field that contains the dates.

Refer to the help topic entitled DateValMask Function in the Rapid Flow Integration Language section of the product documentation for more details and additional examples.