How To Supply Price Lists Electronically

Some of the benefits

What is needed

If one of your reps has requested that you send them your price lists to import into their software program, the following guidelines will help you to collaborate with all of your trading partners.  BrokerForce™ has a data import feature that automates the import of your data in a number of common file formats.

No matter what computer system your business uses, you should be able to export your price list in at least one of the following formats:

• Microsoft Excel  
• Comma Separated Values (csv) 
• ASCII columnar format 

Any of these file types can be opened by a program such as Microsoft Excel and may be imported by BrokerForce™.  DO NOT send an order guide that was intended for printing with graphics (PDF) and multiple headers or a word processing document.  Send the merge data source instead. 

If you are using Excel or ASCII columnar formats, your data should just be organized into uninterrupted rows and columns without any headers other than the optional first row that indicates what is in each column.  Each row is 1 record and each column is 1 field.

Use the two letter "in" abbreviation for inches, and "lb" for pound.  Do not use asterisks, quote, or question marks. Extended characters other than numbers and letters produce unpredictable behavior in different databases and may result in data loss. 

Sub-headers for product categories should be added as an additional column with the category name repeated on each row.

Like this: Uninterrupted columns and rows

Click here for a commented example in Microsoft Excel. Use only the first worksheet in an Excel workbook.

If you do not have data export capability, click here to download an Excel spreadsheet with pre-formatted columns and rows that you can fill out.

NOT like this: Order guides designed for printing

Acme the Best Bet in Widgets
Enter the quantity and your order total will be calculated
This Month's specials are included with the Super Widget promotion
Item#	Description	Price	Minimum
1b	Widget	$100.50	Buy 3 get one free
2a	Smaller Widget	$50.25	24
3c	Large Widget	$200	6
	Special Category
27***	Plastic "Widget"	4 for $1.00	Less than a passel
!3&#%?	Unusual Characters	2	Numbers?

What information to include
The following fields and data types are desirable for each record - text lengths are maximum lengths. Only two fields are required however, every field should be considered in order to import as much data as possible as smoothly as possible. 

ItemNumber/StyleNumber - Text up to 14 characters - required and must be unique. Options such as color and size do not require individual item numbers.  If you have letters in your item numbers, they must be upper case. Shorter is better! Only numbers, letters and hyphens are supported and avoid hyphens.  Spaces are not allowed.
UPC - up to 14 digits, must be unique for each item. Include check digits if your packaging labels include them.   Format this column as text.  See "How to format UPC codes" below.
ProductName or Description* - Text 40 characters - required, should be unique
Units/cs - number of units per case if using a case price
Pack - Text 10 characters (e.g. '12x4x4') for inner/outer pack description.  'Set3' also ok.
Size - number - used in combination with Unit (see below)
Unit - 2 to 4 letter standard text abbreviations for unit size (e.g. oz, lb, kg, floz)
Category - Text 20 characters
MinQty - Numbers only, no text. If you have text, put it into a separate column.
MinUnit - 2 or 3 letter abbreviation for unit
BillingQty - Number used as adjustment for products ordered in one unit and billed in another; for example, a wheel of cheese may be ordered as 2 but billed as 20 lbs.  BillingQty = 20
Note - Text 40 characters 
BeginDate - Date product is first available. Only date formats like mm/dd/yy are acceptable.  Do not use text. Use the current date instead of "New", or leave it blank.
EndDate - Date product is no longer available, blank if n/a
Price columns - Number, up to 6 columns (fields) for different prices. Price should be consistent with unit that product is ordinarily ordered in e.g. by the each or by the case Note: Price columns should be formatted as number columns. If formatted as text, do not add symbols such as dollar signs. Use only numbers.
Price Quantity - Up to 6 columns for minimum quantity required for that price.  e.g. Price Level 3, minimum quantity of 50.
Hyperlink - URL for additional product information e.g. picture on the Internet
Multimedia Link - text path linking the record to a multimedia file such as a picture or sound file
CommFactor - factor that base rate commission is factored by for this item
InStock - Quantity on hand if inventory updates are provided
OnOrder - Quantity on order if inventory updates are provided
Accrual - Percentage allowance
Cube - volume measurement used for shipping or shelf space.  Numbers only
Weight - Shipping weight.  Numbers only
Rank - Relative rank in sales as a whole number without decimal  e.g. 25 if it's the 25th best selling item or, 10, 20, or 80 if it's in the top 10%, 20%, or 80% of sales.  Helps reps suggest what to buy.

Pictures
Use GIF or JPG file formats and ideally these should use the item number of the product as the file name followed by the file extension.  Add "sm" to the file name to differentiate it's size if you have more than one version of same product image.  If it would not be used directly as-is for a web page due to file size, it's probably too large and shouldn't be used.

How to format UPC codes or Numeric Item numbers:

Item numbers without letters and UPC columns have to be specially formatted in Excel as text. To correctly format those columns, click the letter at the top of the column to highlight the entire column. Click <Data>, <Text to Columns>, <Next>, <Next>, place a dot in <Text> and click <Finish>. Save your changes.  If you have a recent version of Excel, you will see a little green triangle in the upper left of each cell.  If your UPC has a leading zero, put a space after the zero.  This will cause Excel to accept the UPC as text and and not as a number.  BrokerForce will remove the space during the import.  If in doubt, format the column as text using this method.

How to send it
The resulting file may be e-mailed, sent on disk, or made available for download from your web site. It is helpful for your reps if updates are in the same format including the field sequence.  BrokerForce™ can import and use substantially more information but, that is beyond the scope of this FAQ.  If you have any questions, please do not hesitate to contact us.  

Home  Order BrokerForce™  FAQs  Data Request  Download  Demos  Support  Contact

Last updated: 07/21/08