A common need for companies with large product catalogs is to update product information such as pricing at a given time. EBMS contains tools to calculate pricing from costs, from base pricing such as SRP or list pricing, or from some other formulas setup within EBMS. The Export/Import process is a alternative batch process to update the product catalog. This export/import process not only delays the update untill a specific date but gives the user the ability to use MS Excel to calculate unique pricing formulas.
Scenario #1: The product catalog for a paint and decorating supply center contains many SKU# for the various paint, wallpaper, and other decorating products that are sold to contractors, interior designers, and homeowners. Many of the paint and decorating product prices are based on supplier pricing. Prices require updates as the cost of the base paint, products, and supplies change. The sales manager reviews and changes pricing grids and formulas periodically rather than changing pricing immediately when a cost or supplier’s suggested pricing changes. This supply center’s customers rely on relative price stability so they can quote projects to their customers. This export/update/import process gives this supply center the flexibility to efficiently adjust prices at a specific date and time.
Scenario #2: The product catalog for an equipment manufacturing company contains many SKU# for the various models of the equipment they sell. This company uses dealers and salespersons to sell their equipment based on a published price catalog. The costs of the various parts change throughout the year but the manufacturer targets to update pricing annually when they republish their printed catalog. The export process in EBMS gives the manager the ability to accurately and efficiently evaluate the price margin given the current pricing of raw materials and parts. The user can adjust pricing within a spreadsheet using flexible formulas. The adjusted pricing can efficiently be imported back into the EBMS product catalog on the date the new pricing takes effect. This client duplicates the process but with different factors for the multiple groups of product. Managing price changes and margins within the budgeted goals of the company is key to the success of this equipment manufacturer.
Scenario #3: A distribution company has a large product catalog that requires a revision. This update requires many changes to product descriptions as well as the catalog category folders. The large task of updating tens of thousands of products individually is not practical because of the size of their product catalog. This company exports their existing product information, edits and adds information using Excel and other tools, and then imports the updated information into the EBMS product catalog using the vendor catalog tools. These distinct steps allowed the company to plan and implement their changes quickly without confusing their sales staff with a disorganized catalog during the update period.
This batch process involves 3 steps:
Export from EBMS product catalog to a MS Excel spreadsheet:
Convert data from CSV file to an EBMS vendor catalog file:
Import product pricing from the vendor catalog:
Complete the following steps to configure this process. Reference the end of this section of the configuration is already completed.
Open the Inventory > Product
Catalog from the main EBMS menu to open the following product
Show the product information to export and hide the columns of the information that should not be included in the Excel spreadsheet.
Include a Price column
in this list by selecting a View
Level within the Inventory
> Options > Price Levels tab as shown below:
Query the list to select the product items to export: Select All on the top left corner of the list to query all products within the catalog.
Select a product category on the left pane to only select a specific catagory of products. Enable the Search Sub-catagories option to include sub-folders.
Use the Find
button to query a group of products within the category
list. Click Adanced
use the advanced query options:
Review [Main] Features > Advanced Query Options section for advanced query options.
Export the product list to a MS Excel spreadsheet by right clicking
on the list as shown below:
Review [Main] Features > Exporting to CSV Files for more exporting details and options.
Open the saved CSV file with MS excel to view the exported list.
Format the spreadsheet to match the data: For example...
Format column A (Id) as text to format this import key column for the import.
Insert a new column to recalculate the price or to manipulate any other product information as shown above. Do not use these steps to change the ID. Review Product Catalog > Changing Ids or Merging Products for instructions to change the ID in a separate process.
Continue with the next step to convert the product list with the new updated information such as the price to the EBMS vendor catalog file.
The contents of the spreadsheet that was exported from EBMS in step A and manipulated using MS Excel must be converted to a vendor catalog file before being imported back into the EBMS product catalog. Complete the following steps to configure this conversion:
Select File > Utilities > Text Import/Export Mappings from the main EBMS menu
Select the spreadsheet by clicking on the Browse button and selecting the .csv file that was created in the previous export step.
Click Open to open the spreadsheet file.
Click on the From Text File
to auto map the CSV file as shown below. The default
settings should map the spreadsheet file to a new EBMS vendor catalog
file. Review [Main]
Utilities > Text Data Import and Export Utility > Overview
for instructions to use the mapping tool.
Name the export Table that will be used as the import vendor catalog such as 00ProductList.DBF file shown above.
Click Create File to
create the file and review the result summary message:
Open the vendor catalog configuration list by selecting Inventory > Options > Vendor Catalogs
from the main EBMS menu.
Click on he New button to create a new Vendor Catalog configuration record or click Properties to open an existing record.
Click on the look up button to view the available EBMS files.
Select a Vendor Catalog file from the open file drop down list as shown above and click OK:
Set a description that defines the purpose of the vendor file.
Select the Inventory ID Key Type setting.
Enter the ID into the ID entry as shown above.
Ignore the Source settings and click OK to save.
Click Next to continue
to next wizard page as shown below:
Link the Vendor Catalog Expressions to the EBMS Product Fields as shown above.
The ID expression must be linked to the ID product field since this link is the main query key to identify the proper product record.
Link to all the Product Fields that contains changed information from the Excel spreadsheet. For example, the NEWPRICE column contains the new product pricing that will be coped to the Base price field. Fields do not need to be linked if data does not require an update or copy. Review Multi-Vendor Catalog > Create Relationship with EBMS Product Catalog for more details on the mapping process and tools.
Disable both the Search Catalog
when entering a product ID or UPC and the Update
from additional Vendor Catalog when importing options and click
Enable all the columns that require sorting. The
only important Field that
requires sorting is the ID Field.
Enable the Update button
for all fields that should be overwritten within the EBMS product
catalog with the data within the vendor catalog. The important
column to Update in the example
above is the Base price column
from the calculated pricing within the spreadsheet. Click
Next to continue.
The Folder Structure settings
are only needed if the category location within the EBMS inventory
folder structure is being changed. This step can
be ignored for price updates or other data focused changes. Click
Finish to complete the creation
of the file import mapping record.
Select the vendor catalog mapping that contains the proper changes and click Update to complete the import. Review Multi-Vendor Catalog > Overview for more details to import information including pricing from other vendor catalogs.
Updating product information can be completed in three short steps as listed below if the update is being repeated and no configuration is required.
Export from EBMS product catalog to a MS Excel spreadsheet:
Open the inventory list with the product queried that
requires update right click and export to a CSV file.
Convert data from CSV file to EBMS vendor catalog file: Open
the Vendor Catalogs list by
selecting Inventory > Options
> Vendor Catalogs tab as shown below:
Click on the Import Text to DBF button, select Replace to replace the product records or Update to update information without deleting the existing records. Click OK to copy the information from the spreadsheet to the vendor catalog.
Import the product catalog pricing from the vendor catalog: Click Update to copy the information into the EBMS Product catalog.
Review the configuration steps for more details on these powerful utilities.