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.
A: Setup export from product catalog to Excel
-
Open the Inventory > Product Catalog from the main EBMS menu to open the following product list:
-
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.
B: Convert data from CSV file to EBMS vendor catalog
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
- Click on the New button to create a new Import/Export Mappings configuration or select Properties to use the previously configured spreadsheet to EBMS file conversion.
- Enter a Description to identify this import/export mapping record as shown below:
-
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:
C: Import product pricing from the vendor catalog
-
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 Next.
-
Enable all the columns that require sorting. The only important Field that requires sorting is the ID Field. Click Next to continue.
-
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.
Steps to Export and Import to Change Pricing without Setup
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.
Related Videos and Content
Videos