M3 Data Import

Limitations

To ensure that error logs are generated properly, the recommended file name should not be more than 50 characters for .csv files and 30 characters for Excel files including file extensions.

Source file name and format

There are several rules as to how the source files are named and formatted:

.csv file

• File name

  The correct naming of a .csv file is crucial. It is the naming that tells the import program what API and transaction should be used.

  The format for an API is: 'FreeName-MIProgram.Transaction.csv'.

  For example: 'NewCustomers-CRS610MI.Add.csv'.

  The format for a function program is: “FreeName-FncProgram.pList.MaintainMode.csv”.

  For example: 'Test-MMS015Fnc.INmaintain.ADD.csv'.

• Data format
  1. The .csv files must contain the names of the transaction input fields as a header, the first line in the file.
  2. Only the fields that you require need to be available, for example, key fields and the data to import.
  3. The header, as well as the whole file content parts must be separated by either a comma (,) or a semicolon (;). These are the only separators supported.
  4. Field columns that are empty must be present so that the number of separators is the same in every line.
  5. Thousand separators are not supported.
  6. Dates must be specified as 'yyyymmdd'.
  7. Numeric decimal separator should always be a decimal point (.).
     This table shows a simple example of the data for a .csv file for an API. Column headings A, B, C represent the spreadsheet columns, and 1, 2, 3, represent the spreadsheet rows:

     	A	B	C
     1	MMSTAT;MMITNO;MMITDS
     2	20;Item123;Test
     3	20;Item234;Test

     This table shows a simple example of the data for a .csv file for a function program. Column headings A, B, C represent the spreadsheet columns, and 1, 2, 3, represent the spreadsheet rows:

     	A	B	C
     1	ITNO;AUTP;ALUN;COFA;DMCF
     2	!MAT001;2;G;1;1
     3	!MAT001;2;BAG;1;1

Excel file

• File name

  The naming of the Excel file is free since the API or function program, and transaction, are both specified in the workbook.

• Data format
  The Excel files must adhere to the format specified for the Smart Data tool. That is, it should contain at a minimum of two worksheets where:

  1. The first worksheet contains the name of the API or function program, and transaction, and if both should be active.

     Naming between an API and function program are different:

     Example for an API:

     'API_MMS200MI_AddItmBasic'

     Example for a function program:

     'FNC_MMS015Fnc_INmaintain_ADD'

  2. The second tab contains the data for importing.

  In the first sheet, program names and transactions are specified in the first column starting at the second line. The first indicated field refers to the second worksheet in the workbook. The succeeding fields follow the second worksheet based on the arrangement of the fields in the first sheet.

  This table shows an example. Column headings A, B, C represent the spreadsheet columns, and 1, 2, represent the spreadsheet rows. The API and transaction shown in column A line 2 refer to the second worksheet in the workbook:

  	A	B	C
  1	Worksheet	Description	Data
  2	API_MMS200MI_AddltmBasic		x

  More worksheets are created which specify other APIs to be executed, each specified on a new line. In the second column, a description may be provided but it is not used by the API. The third column specifies if the tab in question should be active or not at runtime.

  	A	B	C
  1	Worksheet	Description	Data
  2	API_MMS200MI_AddltmBasic	API_MMS200MI_AddltmBasic	x
  3	API_CRS610MI_Add	API_CRS610MI_Add	x

  Refer to the list below for the possible values that can be specified on the 'Data' column and their respective meanings:

  • The letter 'x' signifies that the data should be active.
  • A hyphen '-' indicates that a tab cannot be processed.
  • A blank 'space' character signals that from hereon, the processing is stopped completely.

API transaction data worksheet format

The data sheets should have field names for the transaction in the first line similar to the .csv file.

Rules for formatting the data on a worksheet are:

• On the first line of the first column, specify a field with an exact name called MESSAGE according to the Smart Data specification.
• The field names must begin in the first line of the second column.
• Specify the field descriptions on the second line.
• On the third line, each field is identified whether to be processed or not. This is specified with either 'yes' or 'no'.
• Starting from the fourth line, specify the data.

  This table shows an example. Column headings A, B, C, D represent the spreadsheet columns, and 1, 2, 3, 4, 5, 6 represent the spreadsheet rows.

  	A	B	C	D
  1	MESSAGE	STAT	ITNO	ITDS
  2	Message	Status	Item number	Item description
  3	no	yes	yes	yes
  4		20	Test	Test
  5		20	Test2	Test2
  6		20	Test3	Test3

• All data fields in the workbook should be formatted as Text only. Formatting fields in other formats might cause Excel to convert decimal values to an inexact representation of the actual specified value. This in turn could cause the row not to be imported or filled with another value in M3.
• Scripts are not supported.
• Similar to the .csv files, only date format 'yyyymmdd' is supported.
• Thousand separators are not supported.
• Numeric decimal separator should always use a decimal point '.'.

Execution

The source files must be placed on the M3 File Transfer Disk, in the FileImport directory. To do this, use the M3 Business Engine Files app in the Administration Tools menu.

When it is time to run the import, this is done in 'M3 Data Import. Open' (EVS100). The program lists all available files in the FileImport directory.

Follow these steps:

1. Select the relevant file.
2. Select related option 20='Import'.

The correct program is selected based upon the file extension.

You can view the import logs history for each data import file in 'M3 Data Import Log. Open' (EVS110) or you can get redirected from (EVS100) using related option 21='View Import Logs'. A log is created for each data import transaction or per worksheet. It includes details such as the API program and its completed transaction, start and end of execution in date and time, processed lines (successful and failed), any failed actual lines, and error log file for failed imports.

You can also check data import runs in other ways. EVS101 and EVS102 are assigned to the job queue using 'Job. Connect Job Queue' (MNS310). You can monitor the jobs in 'Job. Display History' (MNS320) after executing (EVS100).

012 Message Type messages are seen in 'Application Message. Open' (CRS420) that shows information related to the execution. The message shows the number of records that were read, processed, and failed.

Error handling

When severe errors occur, a pulse message is sent to the user that initiated the import. The message is sent to the Infor Inbox and is also written in the job log.

API errors can be different depending on the mistakes found in the input data. These errors are recorded in a result file, together with the input data string. Successful transactions are not included in the result file. The result file is immediately executable after having corrected the data for the failing transactions.

Concept logging is available for problem investigation. The import programs act on the concept 'mvx.import.debug' that can be turned on in the job log. This concept logging can also be used to filter out only these lines for better readability.