To import a Price List or Catalog into BrokerForce®

Catalog information organized in Microsoft Excel, Comma Separated Values (csv), tab delimited, or ASCII columnar format may be imported using the 'Catalog_Excel' import profile included in BrokerForce.

Most manufacturers’ have the capability to export data in one of the ‘flat file’ formats described above. Although they may not have a filtering process to remove selected information, fields containing sensitive data are easily removed by opening the exported file in Microsoft Excel, right-clicking on the column to be removed and selecting <Delete>. This can be done before making the data available to their trading partners. DataForce, Inc. provides a manufacturer support section on their web site at How to supply prices lists for your reps regarding how manufacturers can integrate their information systems with their reps and customers to increase efficiency and reduce costs.

Catalog imports enable you to import data from your manufacturers and return orders to them with line item details using methods that are superior to hand written, faxed orders.

Benefits include

- The ability to export orders with line item details to the manufacturer in batch. Easily read or imported, a big savings versus hand written faxes.

- Expanded reporting capabilities to work smarter, not harder

- Create customer order guides with barcodes

- Export price lists to your customers filtered for their specific buying relationship with the manufacturer that can be imported into their point of sale (POS) systems.

Importing a Catalog

The following fields and data types are desirable for each record; only 2 fields are required: Item number and description. Text lengths are maximum lengths. These parameters are based on efficient normalized database design. Every field should be considered in order to import the most information as smoothly as possible. The field names suggested are used in BrokerForce; however, the data type that fills the field is the important factor. The Begin Date column can easily be labeled as New instead (both refer to an introduction date), but the data type should be a simple date, mm/dd/yy, for the most seamless exchangeability. Arrangement of the field order, columns, is optional.

Note: Price columns should be formatted as number columns. If formatted as text, do not add currency symbols such as dollar signs. Use only numbers.

Item Number (VendorItem)- Text 14 characters - required

UPC - up to 14 digits

ProductName or Description* - Text 40 characters - required

Units/cs - number

Pack - Text 10 characters (e.g. '12x4x4')

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 - Number

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.

Note - Text 40 characters

BeginDate - Date product is first available, blank if n/a

EndDate - Date product is no longer available, blank if n/a

Price columns - Up to 6 columns (fields). Will default to zero if not provided (if prices are provided and the column defaults to zeros, check the formatting for the column being imported and the column being imported to.

Price Quantity - Up to 6 columns for minimum quantity required for that price

Hyperlink - URL for additional product information

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

Weight - Shipping weight

To Import a Catalog with the Catalog_Excel Import Profile:

1) Select the 'Catalog_Excel' entry from the Import drop list on the EDI form
2) Select the vendor that the catalog will be imported for
3) Enter the path and file name to the file to be imported or use the <Open Folders> button to locate the file. This method will import ASCII comma delimited *.csv, *.prn, or Excel *.xls files.
4) Click the <Import File> button. After the file has been imported, the Catalog Field Map dialog will open.
5) Follow the directions on this form for assigning vendor catalog columns (fields) to corresponding fields names in BrokerForce. Up to 15 columns of available data can be mapped. Note that you can add or edit BrokerForce price lists on this form. Check the data to import for proper formatting.
6) Edit or delete vendor catalog records on the subform before they are imported. Use the record navigation buttons or scroll bars to review and proof the data before importing.
7) After BrokerForce field names have been assigned to columns, click the <Close> button on the toolbar. These assignments will be saved for future data imports.
8) Follow the remaining directions and prompts on the EDI form for removing discontinued items, adding new items, and posting other catalog changes.

Troubleshooting
Error Message "File isn't in expected format" - When the 'Locate file' dialog window opens, click the 'Files of Type' drop list to select alternate file types. If a file is a tab delimited (columnar) file or it is not in one of the listed formats, try opening and saving the file with Microsoft Excel® as an Excel file and then retry. Microsoft Excel will recognize a columnar (tab delimited) file and you will be prompted to select column widths. If so, follow the Excel directions for doing so. Save the file with Microsoft Excel® as an Excel file format and then retry importing the new *.xls file. Check your column formats before performing the import to make sure that number columns are formatted as numbers or that text columns with numbers in them contain only numbers and not additional symbols such as dollar signs (period or decimal is OK)

Junk - After BrokerForce opens the temporary table for you to preview; you may see a lot of information in inappropriate columns. This often occurs when an order guide style intended for a print layout is provided as a substitute for an import file. Try opening the file in Excel and edit the information. Use cut and pastes to improve the file's quality. Get rid of formatting and sub-headers in the middle of the document. Organize the data into consistent columns and rows. Each column should be just one field and each row should be just one record. Streamline the data; take out all window dressing such as bold.

Missing Information - Open the file in Excel and delete all but the 15 most important fields (columns). Remove blank rows and edit or remove incomplete rows. The Catalog_Excel import feature is limited to 15 fields.

Item number or UPC doesn't Import - The most common cause of this problem occurs when an attempt is made to import the item number as a number and not as a text string. To allow the greatest diversity, vendor item numbers are stored as text and not as numbers. If the first few item numbers do not have a letter in them, the data will be interpreted as a number column and item numbers with letters will not import. To correct this in an Excel file, open the file in Excel, click the column letter at the top of the column, click <Data>, <Text to columns>, <Next>, <Next>, place a dot in <Text>, and click <Finish>.  Save your changes and close Excel.

Prices do not import - The most common cause of this problem is when an attempt is made to import the price as a string and not as a number. This can happen when the column header contains field names. To correct this problem, open the file in Excel or otherwise edit the file to put a number in the first row, price column e.g. 'Price' --> '0'. Do not add symbols such as dollar signs, or remove them if they are present. If the column is formatted as text, remove the $ signs.

If necessary, a file containing complicated formatting can be saved as a tab delimited or CSV format and then imported into a clean Excel spreadsheet for review.

Microsoft Excel is a trademark of Microsoft Corporation
 

Return to FAQ's