Importing file by Date Time

The Date Time period mapping option can only be used when the dataset’s period type is 1-Months, 2-Weeks, or 9-Weeks. It cannot be used for 3-Quarters or other period types. When a weeks period type is selected, DMP automatically converts the date into the corresponding week number.

If you have the date format MM?DD?YYYY, the question mark represents the separator between the month (MM), day (DD), and year (YYYY). The separator can be any character, special character, or null value. However, it cannot be a number, and it must not be the same as the file’s list separator.

If no separator is used (null), the month and day should always be in two-digit format. Single-digit months and days must have a leading zero (0).

This table shows the dates that are acceptable for the listed formats for the date September 8, 2017:

dd/MM/yyyy MM/dd/yyyy yyyy/MM/dd
08/09/2017 09/08/2017 2017/09/08
8/9/2017 9/8/2017 2017/9/8
08092017 09082017 20170908

This table shows the formats that are not acceptable for the same example date (including but not limited to):

dd/MM/yyyy MM/dd/yyyy yyyy/MM/dd
892017 982017 201798

Before importing, verify that these conditions are met:

  • The file must contain a column for the date with similar date format selected from the Formats field.
  • The date can contain time but the date must come first before the time. For example, 20/11/2017 T23:59:59.000.
  • The column name input in the Import File tab must match the column name in the file.
  • The date must be in any of these formats:
    • dd?mm?yyyy
    • mm?dd?yyyy
    • yyyy?mm?dd
  • There must be no duplicate lines. Two rows are considered duplicates if they share the same values in all other fields and their dates fall within the same period, meaning the same month for a monthly period type or the same week for a weekly period type, even if the exact dates are different.

If the date and time format is dd/mm/yyyy and the period type is month, the first two lines in this example are considered duplicate since they are on the same month:

*FRF,UCWHLO,UCITNO,DateTime,UCDEMA
,1,LALEN01,20/09/2012,5
,1,LALEN01,25/09/2012,5
,1,LALEN02,20/10/2012,5
,1,LALEN02,20/11/2012,5
,1,LALEN03,20/10/2012,5
,1,LALEN03,20/11/2012,5

Here’s a sample CSV file for Date Time:

,001,0001CGHAI,08/01/2017,500
,001,0001CGHAI,08/02/2017,620
,001,0001CGHAI,08/03/2017,480
,001,0002CGHAI,08/01/2017,300
,001,0002CGHAI,08/02/2017,310
,001,0003CGHAI,08/01/2017,150
,001,0003CGHAI,08/02/2017,200
  1. From the menu, select Action > Import File.
  2. In Configuration, specify this information:
    Name
    Specify the configuration name.
  3. In Options, specify the character separator.
  4. In Import Parameters, specify this information:
    Dataset
    Select a dataset from the list.
    Transaction Type
    Specify the transaction type.
    Period Mapping
    Select Date Time.
    DateTime column name
    Specify the name of the column that contains date and time data.
    Formats
    Select a format from the combo box.
  5. Specify a folder path where the file to be imported into DMP is located.
  6. Click Import.
    When the progress bar reaches 100%, the import is complete and the system updates the data in the dataset. In the Favorite View, the grid displays details from the imported file based on its content.

    To review the log, click View log file.