Inventory Price File Layout

This text file is used in the Load External Price File (5.6.2.3) program.

The file is typically prepared in Excel and saved as a tab delimited text file with a .TXT extension.

All of the fields (if present) will be added to new part numbers and/or new units of measure. For existing part number/units of measure, only prices will be loaded (unless optional fields are selected in field 16).

The only exception is UPC Code, which will always be updated if the UPC does not exist in the INVENTORY,BAR.CODE file. And, it will become the primary UPC for the part number unit of measure being updated.

Costs and prices may be in any order; during the import you will specify which price codes to update for Price 1 through 6. You do not have to fill all 6 price fields in the file data.

Prices may contain decimals, but if absent, the program will assume 2 decimal places (MR2). Examples:

Raw	Converted
12586	125.86
125.86	125.86

Unit Weight will assume 4 decimal places (MR4). Examples:

Raw	Converted
17682	1.7682
25.4	25.4000

Important Note: the Part Number (field 1 below) should not contain the Mfr Code unless you are using the Multiple MFR option for one vendor. The Mfr Code will be entered in the Load External Price program.

The text file must contain TAB-delimited fields in the following order:

Inventory Price File Layout			Load External Price File (5.6.2.3)

#	Spreadsheet Column	Field	Note
1	A	Part Number	20 characters max including the Mfr Code entered in the Load External Price program and a period. Must be formatted as Text column type in Excel.
2	B	Unit of Measure	BLANK will default to EACH
3	C	Description	40 characters max
4	D	Inventory Notes	50 characters max
5	E	Standard Pack	Whole number with no decimal point
6	F	UPC Code	12 digit UPC bar code or other code.
7	G	Unit Weight	MR4. (ex. 1.2345)
8	H	Price 1	MR2. (ex. 6.78)
9	I	Price 2	MR2
10	J	Price 3	MR2
11	K	Price 4	MR2
12	L	Price 5	MR2
13	M	Price 6	MR2
14	N	VOC	MR4 - amount must be provided by unit, not lbs/gal
15	O	Haz Mat Code	ex. NA1234
16	P	Custom Type	E=End Product, C=Component, does not write to the file, this is for information only on the exception report.
17	Q	Group Code	Group Code 1. 10 characters max
18	R	Group Code 2	10 characters max
19	S	Group Code 3	10 characters max
20	T	Group Code 4	10 characters max
21	U	Group Code 5	10 characters max
22	V	Tax Indicator	Y or N
23	W	Status	A, I or O
24	X	Bar Code Case Flag	Y or N
25	Y	Bar Code Case Quantity	99999 max
26	Z	Is Bar Code	Y or N
27	AA	Vendor Part Number	Recommend 20 characters max
28	AB	Key Words	Comma separate multiple key words
29	AC	Vendor Code	Vendor
30	AD	Bar Code 2 (UPC Code)	12 digit UPC bar code or other code.
31	AE	Bar Code 2 Case Flag	Y or N
32	AF	Bar Code 2 Case Quantity	99999 max
33	AG	Bar Code 2 Is Bar Code	Y or N
34	AH	Exclude from Web	Y or N (leave blank by default)
35	AI	Alternate Part Numbers	Comma separate multiple part numbers
36	AJ	Alternate Parts Exclude from Web	Y or N (leave blank by default) - comma separated to match parts
37 - 51	AK - AY	User Defined Field 1 - 15	50 characters max
52	AZ	Minimum Sale Qty	1 - 99999 max

7/19/2021 - The following changed in 5.6.2.3 Load External Price File.

Field 1 Mfr or Vendor Code

There is a new option to enter an asterisk * in field 1 when updating parts for multiple manufacturers and vendors.
This allows the load program to update parts from multiple manufacturers and multiple vendors in one load. This new function is possible because the Vendor Code is being added to the spreadsheet.
When creating new parts or updating vendor part numbers via 5.6.2.3, the program checks for a default vendor code in field 15 Vendor Code – new parts. If field 15 is blank, it checks field 1 for the manufacturer code. If field 1 is an asterisk, there is no default vendor code. If the vendor code is missing in the spreadsheet and there is no default vendor code, the part will be skipped and added to the error report.

Field 16 Optional Fields

There are new options in field 16 to specify which fields to update in bulk from the spreadsheet:
X: Exclude from Web
A: Alt Part No/Alt Exclude from Web
U: User defined fields
M: Minimum Sale Qty
When updating optional fields, the program may handle blank values differently.
Exclude from Web: a blank field in the spreadsheet will cause any existing content to be cleared.
Alt Part No, Alt Part Exclude from Web and User Defined fields: a blank field in the spreadsheet will not clear existing content.

Field 18 Mfr - The manufacturers in field 18 will be arranged alphabetically.

Clearing barcode information

Currently, when you enter C to Convert the Price File, you are prompted to overwrite the existing bar code information.
If you enter Y for Yes, a new prompt asks if you want to clear the existing information if the data in the spreadsheet is blank. This new setting only applies to the first set of barcode fields in the spreadsheet.
NOTE: If you enter Y to clear the existing bar code, the program only looks at the first set of barcode fields in the spreadsheet to determine whether to clear existing content. If the first set of barcode fields are blank and the second set has data, answering Y means the program will clear the existing data and then upload the new data from the second set of fields.