A common problem with importing any file into IDEA is dealing with date fields. The import assistant reads the field and does its best to determine what kind of data is being imported and what type of field to create. If there is anything in the field that doesn’t look like a date, it will either create a Numeric or Character field. Fortunately, there are ways to either correct this at the time of import or in the database after the file has been imported.
Correcting the Date Field When the File is Imported
Certain types of imports allow users to specify field types during the import process. For example, when you import a text file, the field is created and the data type is assigned. In the example below, the Import Assistant wanted to import this date field as a Character field.
To correct that, select the field; then use the drop down next to “Type:” to change it to a Date field. When the type is changed to Date, you will be prompted to enter a date mask.
In this case, the mask “YYYYMMDD” was used because it matches the format of the data in the file.
Report Reader imports also allow you to change the field type during the import process. When importing the same file as above, Report Reader identified that field as a Numeric Field.
To change the type to a date field, select the field and use the drop down next to “Type” to change it to a Date field. As before, you will be prompted to enter a matching mask for the field. Update the mask and save the layer.
It’s important to note that some import types do not allow you to specify the field type. Most commonly, Excel imports read the data in the field and make a best guess as to what it is that you are trying to import. Even if the cell is listed as Date in Excel, the Import Assistant may read the incoming data as a Character or Numeric field. In those cases, it’s usually best to go ahead and import the file and change the field type in the resulting database.
Changing the Field Type in IDEA
If you’ve already imported the file and find that your date field is not using the Date field type, you can still make that correction in IDEA. First, click the Data tab then click Modify.
The Modify Field Window will open. Use the drop down next to “Field type:” to change the field to Date. Enter the appropriate date mask in the Parameter field.
Then click the OK button to make the update
In all cases, the data must be formatted consistently to use the method of changing the data type to date. For example, in some files, dates can be listed as 1/15/2017 or 1/4/2017 or 10/15/2017. The “mm/dd/yyyy” format is not consistent. In this situation, the field should be imported as a character field and reformatted inside IDEA using @functions.
Reformatting Dates in IDEA
Date masks have been mentioned multiple times in this article. It’s very important to note that the date mask you enter must exactly match the format of all the dates in your field for the update to work correctly. If you update the field and you see red errors listed instead of a date, the most likely cause is that the mask doesn’t match what is in the field.
The mask will need to be updated. Make sure to include any slashes, dashes, spaces or any other special characters in your date.
It’s not uncommon for a month to be listed as an abbreviation instead of a number (JAN for January, MAR for March, etc…). In these cases, you can use three M’s for the month. For example, the mask for 17 JUL 2016 would be “DD MMM YYYY”. In this example, IDEA is smart enough to know that JUL is July.
When dates are not formatted consistently in the same file, a single date mask will not work for all of the dates. For example, the date mask for 10/20/2015 is “MM/DD/YYYY”. This same mask won’t work for a record with a date of 3/10/2015. The problem is that the second date only has a single digit for the month instead of two indicated by the mask. If you had both of these dates in a field, that mask would work on the first one, but would return an error on the second. To format dates in this case, the best thing to do is append a new Date field and write an equation to convert the date. The following equation would test the length of the first and second segments of the date and make updates as necessary.
Note that this equation will work in this instance, but may need to be edited to account for variations in your date fields.
If you have questions about this or other IDEA functions, please give our help desk a call at:
888-641-2800 Option 4
Or email us at firstname.lastname@example.org