Importing a file

Import File allows partial import from other data systems to an existing M3 DMP dataset for multiple data types or accumulators. You can import the data according to a specific year, year and period, time mark, transaction type, and budget.

For the file import to be successful, you must meet these general conditions:

  • The file should have the .csv extension.
  • The file should be in the correct format.
  • The file should have *FRF as the first column. The value of *FRF for all rows must be blank.

    The part *Content at the first row of the file means a comment. If you add this, you can put comments or notes here.

  • The lists in the file should have a separator, preferably comma (,) separated.
  • For options that asks for column names, the column name in the file should match the column name that you specify in the Import File tab.
  • The file should contain DMP column names that are consistent to the selected Transaction Type in the Import File tab. For example, Transaction Type 34 (Demand/Calculated sales forecast) normally has the accumulators UCDEMA, MFCFOR, and MFMADJ.
  • You must not have duplicate entries, which are two rows that are exactly the same, including Year and Period.
  • You must not have duplicate column names.

Example 1

The file format supported is CSV. This is a simple, comma-separated text file format. You can use a comma or another symbol as the separator. You can edit the file in a simple text editor, for example, Notepad or Microsoft Excel.

Note: Microsoft Excel transforms the datatypes that can cause errors.

*FRF;UCITNO;OKDIGC;Year;Period;UCIVQT
;LIQEXTRACT    ;;2012;8;1178.2
;LIQEXTRACT    ;;2012;9;2356.4
;LIQEXTRACT    ;;2012;10;2356.4
;R004          ;;2012;9;900
;R007          ;;2012;8;635

The Year column is optional. If the column is in the CSV file, DMP will use the year selected in the Import File tab.

The Period column must have that exact name with a capital P.

The first column *FRF is always empty, and the row is where DMP looks for the Key and Accumulator field names.

The columns after *FRF and before the Year/Period columns are Key column in the M3/DMP data set. You can find these keys in the Settings – Format dialog box.

The values after the Period column are M3 dataset accumulator fields. You can find valid fields in the DMP table PosOssel, and matches the M3 dataset accumulators.

DMP can import multiple accumulators. It can also import any new accumulators, even if they where not previously in the dataset. Some examples include UCDEMA, MFMFOR, MFCFOR, MFMADJ, UCDMA2, UCIVQT, UCSAAM, UCUCOS, UCORQT, and so on.

Note: The *TXT fields are ignored.

Example 2: Node, Warehouse, Item


*FRF;N3NDID;UCWHLO;UCITNO;Year;Period;MFCFOR;UCDEMA
;NW;1;100;2011;1;8;0
;NW;1;100;2011;12;30;
;NW;1;100;2012;1;10;0
;NW;1;100;2012;2;0;
;NW;1;100;2012;3;15;
;NW;1;100;2012;4;;
;NW;1;200;2011;1;20;
;NW;1;200;2011;2;10;
;NW;1;200;2011;3;;
;NW;1;200;2011;4;;
;NW;1;200;2011;5;;
;NW;1;200;2011;6;;
;NW;1;200;2011;7;;
;NW;1;200;2011;8;60;
;NW;1;200;2011;9;;
;NW;1;200;2011;10;;
;NW;1;200;2011;11;;
;NW;1;200;2011;12;120;
;SE;2;100;2011;1;;0
;SE;2;100;2011;2;;
;SE;2;100;2011;3;10;5
;SE;2;200;2011;1;0;
;SE;2;200;2011;12;8;0

Example 3: Simple way to create a new season

This file has the Main Product, Season, and Planning Entity as style-color. It only creates one entry per item to create the new structure in season S3_2012.

*FRF;UCHDPR;UCSEA1;NDCCIT;PERIOD;MFMFOR
;Top;S3_2012;Y301-B1;5;130
;Trousers;S3_2012;Y303-B1;5;80
;Top;S3_2012;Y111-R1;5;55
;Top;S3_2012;Y121-C1;5;70
;Shirt;S3_2012;Y124-B1;5;50
;Shirt;S3_2012;Y124-C1;5;120
;Dress;S3_2012;Y302-D1;5;100
;Dress;S3_2012;Y302-D2;5;100

Example 4


*FRF;IISUCL;UCCSCD;UCHDPR;UCIITN;UCITCL;UCSUNO;Period;UCIVQT
;BBB;UK;BUTTER;125;LIC;A01;1;10972
;BBB;UK;CHEESE;135;BVM;A01;1;5145
;BBB;UK;CREAM;9;LIC;A01;1;116448

Example 5: *TXT fields are ignored (not required)


*FRF;UCATV1;*TXT;Period;UCIVQT
;A;A01;1;10972
;B;B;1;5145

Example 6: Import File horizontally

You can import this type of CSV file with periods horizontally, which requires you to select a data type. If a YEAR column exists in the file, the selected year is ignored. DMP automatically adds the data type to the data set if it is missing.


*FRD;Budget= ;Selector=MFCFOR;Transaction=34
*FRF;N3NDID;UCWHLO;UCITNO;Product Group;Category;Supplier;Inv. Class;Key
State;YEAR;P1;P2;P3;P4;P5;P6;P7;P8;P9;P10;P11;P12;MFCFOR
;NW;1;100;Electrical Tools;Home;Power Corp;;New C-Items;2011;45;43;27;60;56;44;30;36;39;45;82;109
;NW;1;200;Electrical Tools;NewFloor;Power Corp;;B-Items;2011;45;43;0;0;56;44;0;36;39;45;82;109
;SE;2;100;Electrical Tools;Home;Power Corp;;A-Items;2011;;;23;60;56;44;;;;;;
;SE;2;200;Electrical Tools;NewFloor;Power Corp;;D-Items;2011;0;;50;60;56;44;30;;40;44;82;7
;NW;1;500;Electrical Tools;NewFloor;HMan;;Normal;2011;12;12;15;17;17;12;10;10;12;12;25;32
;NW;1;502;Electrical Tools;NewFloor;Power Corp;;Normal;2011;45;43;0;0;56;44;0;36;39;45;82;109
;SE;2;500;Electrical Tools;NewFloor;HMan;;Normal;2011;4;4;5;5;5;4;3;3;4;4;8;10
;SE;2;502;Electrical Tools;NewFloor;Power Corp;;Normal;2011;4;4;6;6;6;4;4;4;4;4;8;10
;NW;1;501;Electrical Tools;NewFloor;HMan;;Normal;2011;169;160;187;224;207;165;112;135;147;167;307;409