M3 Data Import
M3 Data Import allows you to import data located in stream files into the M3 database through APIs and function programs. The use of APIs and function programs ensures that the M3 business logic is utilized for validations, that data is inserted in the proper locations, and that it is done with the proper authorization controls.
The source files are copied into the M3 File Transfer area where one of the two programs is executed to read the files, format the proper API and function program transactions, and call the relevant API or function program. The programs are executed in the background, thereby giving the system a better control over the execution.
Limitations
- .csv (Comma Separated Values) text files having UTF-8 encoding.
- Microsoft Excel files in XML format having the .xlsx extension.
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
- The .csv files must contain the names of the transaction input fields as a header, the first line in the file.
- Only the fields that you require need to be available, for example, key fields and the data to import.
- 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.
- Field columns that are empty must be present so that the number of separators is the same in every line.
- Thousand separators are not supported.
- Dates must be specified as 'yyyymmdd'.
- 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 formatThe 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:
- 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'
- 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.
- The first worksheet contains the name of the API or function program, and
transaction, and if both should be active.
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 - Scripts are not supported.
- Similar to the .csv files, only date format 'yyyymmdd' is supported.
- Thousand separators are not supported.
- Numeric decimal separator must 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:
- Select the relevant file.
- Select related option 20='Import'.
The correct program is selected based upon the file extension.
- .csv File - EVS101 (Importing .csv format files)
- Excel File - EVS102 (Importing Excel format files).
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
- Severe errors that canceled the execution
- Errors that are returned from the API business logic.
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.