Importing Price Lists Tips and Tricks

Tips and Tricks for Importing Price Lists into BrokerForce™

Have you read the BrokerForce "Catalog_Excel" help topic? If not, press <F1> inside the program, click <Index> and select the "Catalog_Excel" help topic.

Have you read the FAQ for how manufacturers should supply your price lists? This provides the fundamentals of how a list must be formatted to be imported. see: How to supply prices lists for your reps

Price list is a PDF? One can try copying and pasting directly into Excel or Word and then format it into columns. On occasion this will work. If it doesn't, you will need a full copy of Adobe and not just the reader or a conversion utility like Smart PDF Converter. This latter tool is an inexpensive, efficient way to convert PDF files to other formats like Excel or Word.

This tip from Microsoft can save a lot of frustration for using search and replace tools in Excel. How to find and replace tildes and wildcard (asterisk *) characters in Excel

One of the most common problems that users contact support for is that some or all of the items do not import in a large list of products. This happens when the list of item numbers has some items with letters and numbers as their item number and other items with only numbers in their item code. To fix this in Excel, click the 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 retry the import.

When UPCs don't import and you have mapped that column this is typically a data format issue. To fix this in Excel, click the 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 retry the import.

fomat UPCs or Zip Codes with leading zeroes If the UPC, Item number, or a zip code is preceded by a zero: Click <Format>, <Cells>, <Custom>, and enter the <Type> format as shown at right. For UPCs enter 12 zeroes, for Zip codes enter 5, or 5 followed by a dash and 4 more zeroes for a Zip plus 4

Item numbers still not formatting correctly? Try putting an apostrophe in front of the first number on the first 5 rows of data. BrokerForce averages the data type of the first few rows of an import so, this will force it to text. Since BrokerForce removes anything other than letters, numbers, or dashes in item numbers, this will work well.

When the item number is separated into more than one column, you will need to use a formula to combine cells in multiple columns into one cell. To do this, see the "Concatenate" help topic in Excel.

If more than once piece of information is combined in one cell and needs to be separated, this requires more advanced skills in Excel. See "Left", "Right", "Mid", and "Replace" function help topics in Excel. If there is a pattern such as the first 4 characters (including spaces) are the quantity and the last three are the unit, you may be able to separate this information using these functions.

To remove spaces in front of an item number or use = Trim(A2) as the formula to remove leading or trailing spaces from text. It will not remove spaces in between text.

Return to FAQ's

Home Order BrokerForce™ FAQs Data Request Download Demos Account Maintenance Contact