Excel spreadsheet formatting for purchase orders

You can use a Microsoft Excel spreadsheet to create purchase orders in Purchase Order Entry. Create and format the spreadsheet, specify the required header and line item information, and then import this spreadsheet. An example of the spreadsheet is attached to KB article 1902953 on the Infor Support Portal.

Each spreadsheet in the Excel workbook represents a purchase order. All header-level columns and rows must be included in each sheet, even if the content is identical.

Note: To import data into Purchase Order Entry that was exported by the Product Surplus Stock Report, you must save the CSV file as a Microsoft Excel Workbook. Before you save the file as a Microsoft document, verify that the data in the CSV file is delimited, with comma delimiters. All cells should be unlocked. Save the CSV file as a Excel workbook with a .xls, .xlsm, or .xlsx extension.

Cross-reference logic

This standard cross-reference logic is supported when line items are imported:

Vendor product
Barcode
Supersede
Interchange
Auto pricing
Manufacturer’s product

Cross references to the manufacturer's product are set up in Product Warehouse Product Setup. The other cross references in the list are set up in Product Extended Product Cross Reference Setup.

Cell formatting

Cells that do not contain data but precede a cell with data must be unlocked. For example, if you specify a value in columns A and D in a row, you must unlock the cells in columns B and C. To unlock a cell, select the Protection tab in the Format Cells window. You can unlock multiple cells simultaneously, but do not unlock all the cells in the spreadsheet. This can cause an import error.

Check the number formatting for the cells. We recommend that you select Text because the data is displayed and imported as entered. If a cell uses General or Number formatting and you enter a product number that begins with zero, the zero is eliminated. This might cause an import error, or cause the incorrect product to be included on the order.

Header formatting

The header information is contained in columns A and B, rows 1-9 of the spreadsheet.

This table shows the cells in the spreadsheet where you specify the header data.


Row	Column A	Column B
1	Primary Key	Secondary Key
2	Vendor #	Ship Form
3	Warehouse	Order Type
4	Vendor Invoice #	Buyer
5	Ship Via	Terms
6	Reference	Due Date
7	Total PO Amount	Related OE #
8	Addon Amount	Addon Description
9	Whole Order Discount	Discount Type $ or %

You must specify Primary Key and Secondary Key in the first row of each spreadsheet. An invalid import occurs without these values.

Vendor # and Warehouse are also required values. This data is validated. If the data is invalid, the import does not fail. If the Vendor # is invalid, **invalid** is displayed in the Vendor # field on the Import From Excel window. If the Warehouse is invalid, *I is displayed in the Warehouse field on the Import From Excel window.

Valid order types for the import are Purchase Order (PO), Quote (QU), and Return Merchandise (RM). If an order type is not specified, the order type is PO by default. If you do not specify values in the remaining header cells, default values, based on the vendor, are added when the purchase order is created.

Line item formatting

Line item detail is contained in columns A through R, rows 10-999.

This table shows the cells in the spreadsheet where you can specify the line item data.


Row	Column A	Column B	Column C	Column D	Column E
10-999	Vendor Product	Distributor Product	Product Description	Quantity	Unit


Row	Column F	Column G	Column H	Column I	Column J
10-999	Price, per unit cost to distributor				Net Amount


Row	Column K	Column L	Column M	Column N	Column O
10-999	Substitute Product Flag	Supersede Product Flag	Expected Ship Date	Unavailable Reason	Product Category


Row	Column P	Column Q	Column R
10-999	Product Line	Require Flag	Print Flag

A product value is required for each line item. You can specify the vendor product or the distributor product. If both values are imported, the distributor product is used. If only the vendor product is specified, it will be used to cross reference to the distributor's product.

If the order type is Return Merchandise (RM), the Reason Unavailable is required. If the RM is for a product in a TWL warehouse and the return reason is valid in Distribution SX.e but not TWL, the RM is created. You can change to the purchase order if required, or let the purchase order process normally. All other fields are optional. If data is not specified in the remaining line item cells, some default values are added when the purchase order is created. If a quantity is not specified, the default value is one.

If you specify Yes in the column K, the Substitute Product Flag cell, the Sub field in the header is selected. Any substituted products are used when the purchase order is created.

If you specify Yes in column L, the Supersede Product Flag cell, the Allow Substitutes field in the header is selected. Superseded products are used when the purchase order is created.

Product Description uses description 1 and 2, if that data is provided. Only stocked products are allowed. If the product is found, the Product Setup description is used. If a price/cost is designated on the spreadsheet, that value is used and the cost/price override flags are set. The whole order discount type is either amount or percent based on the Discount Type field.

Notes and comments

To add notes to an order, specify NOTE in column A, the Product cell. Specify the note text in column B, the Description cell. The note can contain 1024 characters. To make the note required, specify Yes in column E, the Required Note cell. To print the note on the document, specify Yes in column R, the Print Note cell. If you add a note, ***NOTE*** is displayed in the Name field on the Import From Excel window. This indicates that the imported line is not a product line item.

To add a comment to a line item, specify COMMENT in column A, the Product cell, in the line directly below the product line. Specify the comment text in column B, the Description cell. The comment can contain 1024 characters. You can add two comments per line item. Only the first comment is printable. To indicate that a comment is required, specify Yes in column Q, the Require Flag cell. To indicate that a comment is printable, specify Yes in column R, the Print Flag cell. If you add a comment, ***COMMENT*** is in the Vendor Name column on the Import From Excel window. This indicates that the imported line is not a product line item.