Excel Import

General

With the use of Excel import, you can easily create (multiple) documents at the same time. If you are looking for Excel Import File Format you can create a template for the Excel import here. Once you have created a template, you can use it to copy it for another template or import Excel documents so that the creation of the documents is triggered.

Set up

When you create a template, enter the column/cell values that represent the fields in the Value field. You can also choose to use default values. These are then filled by default. For example, you can set the status with a default value. So when creating the document from the Excel import it always gets this particular status. In addition, it is also possible in this case to create multiple receipts by reading one Excel document. It is important here what makes each receipt unique. In the example below, External document no. is the unique number, which is also what the checkmark Split document stands for. So if the external document number changes, this is the trigger that a new entry has to be created. When filling in the skip row filter the system can identify for which value he needs to skip. For example in the screenshot below, when identifying customer item ART00517. The system will not create a document line for this customer item.

When importing the Excel document, you can also set the mapping. So if value X is read in, it must be translated into value Y. With value Y, the system can then create the document correctly. Therefore, you can use the Mapping code. Within the mapping code, you can set up a whole table with internal values and external values to be mapped while importing the Excel file. You can also use the Custom format Func. Set with example the function code STRING to convert values to some default other values with the function set.

In the end, you can also add the Excel import document directly in the attachment of the created document. In the Add file as record link field, enter the object ID you need.

Example Excel

With the Excel file below, he can read out the data based on what we showed in the earlier screenshot. Two entries have been created with this Excel file based on the two different External document nos.

Unknown customer items

When you import an Excel file with a file format whose Application is WMS and whose Transaction Type is Create, Update or Update Extended, 3PL Dynamics first checks the item value of every line row in the file before any document is created or updated. Each value is looked up as the No. and the External No. of a customer item of the customer, and as an item No.; the customer item finder that is also used on document lines is tried last.

If one or more values cannot be matched, the standard Error Messages page opens with one line per unknown item, including the Excel row in which it was found:

Item X-123 (Excel row 12) is unknown and could not be imported.

The import is then cancelled and nothing is created or changed. Create the missing customer items (for example with Import Master Data) or correct the values in the file, and import the file again. Because all unknown items are listed at once, you can request the missing item data from your customer in one go instead of finding the items one by one.

Note

For a file format with transaction type Create, only rows with line type Cust. Item are checked, and the check is skipped when the file format has no line fields. The customer of the row, the document header or the file format is used to find the customer item. Empty rows and rows without an item value are skipped.

Note

The check applies only to WMS file formats; TMS, FMS and other applications are not affected. When the import runs without a user interface (for example from the job queue or a web service), the list is not shown and the import stops at the first line whose item cannot be found.