Importing file by date and time mapping

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 or special character or null. It cannot be a number. The separator cannot be similar to the list separator of the file. When the date should have no separator (null), the days and months should always contain two digits. The single digit days and months should contain zero (0) at the beginning.

If you have this date September 8, 2017, these dates are acceptable for the listed formats:

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

Given the same example date, these formats are not acceptable (including but not limited to):

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

Before importing, make sure these conditions are met:

  • The file should contain a column for the date with similar date format chosen from the Formats combo box.
  • The Date Time period mapping option can only be used when the data set's period type is month or 1-Months, 2-Weeks, or 9-Weeks.

    When the weeks period is chosen, M3 DMP converts the date (which is in DateTime format) into weeks. This cannot be used for 3-Quarters or other period types.

  • The date can contain time but the date should come first before the time. For example, 20/11/2017 T23:59:59.000.
  • The column name input in the Import File tab should match the column name in the file.
  • The date should be in any of these formats:
    • dd?mm?yyyy
    • mm?dd?yyyy
    • yyyy?mm?dd
  • There should be no duplicate lines, meaning there should be no two lines having the exact information on all fields, including the date. If the lines are the same in all other fields but different in date, they are still considered duplicate if the dates have the same month (period type: month) or same week (period type: weeks).

If the date/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
  1. Select Action > Import File.
  2. Specify the configuration name.
  3. Specify the separator character in Options.
  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.