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 other formulas set up within EBMS. The Export/Import process is an alternative batch process to update product catalog information. This export/import process not only delays the update until a specific date but gives the user the ability to use a spreadsheet to calculate unique pricing formulas.
Scenario #1: The product catalog for a paint and decorating supply center contains many SKU codes for the various paint, wallpaper, and decor 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 base costs of the 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 price changes. The supply center’s customers rely on relative price stability so they can quote projects to their customers. The export/import process within EBMS gives this supply center the flexibility to efficiently adjust inventory prices at a specific date and time.
Scenario #2: The product catalog for an equipment manufacturing company contains many SKU codes for the various models of equipment they make and 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 in a spreadsheet using flexible formulas. The adjusted pricing can be imported back into the EBMS product catalog on the date that the new pricing is due. This client duplicates the process but with different factors for the multiple groups of products. 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 overwhelming 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 steps allow the company to plan and implement their product catalog changes quickly without confusing their sales staff with a disorganized catalog during the update period.
This batch process involves 3 steps.
A. Export from EBMS product catalog into a spreadsheet document.
B. Convert the data from CSV file to an EBMS vendor catalog file.
C. Import new product pricing from the vendor catalog.
(Click the links to jump to each set of steps.) Complete the following steps to configure this process. Reference the end of this section if the configuration is already completed.
A: Set up export from EBMS Product Catalog to spreadsheet
-
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. To select or hide columns, right-click on a table line and click on Show/Hide Columns in the context menu to decide which columns to display. For more details on this feature, go to Column, Tab, and Dialog Appearance.
-
Include a Price column in this list by selecting a View Level option within the Inventory > Options > Price Levels tab and clicking OK to save changes.
In the example below, because the View Level is set to Retail, the retail price level will be displayed as the Price column in the product catalog table and in sales documents. If the View Level was changed to Wholesale, the wholesale price level would be displayed as the Price column.
-
Going back to the Product Catalog, query the inventory list to select product items to export: Select All on the top left corner of the page to query all products within the catalog.
-
Select a product category on the left pane to select a specific category of products. Enable the Search Subcategories option to include subfolders. (This option is a checkbox at the bottom of the page.)
-
Use the Find button in the lower left-hand corner to query a group of products within the category list. Click the Advanced button to use the advanced query options:
Review Advanced Query Options section for advanced query options.
-
-
Export the product list as a spreadsheet by right-clicking on the list as shown below and selecting Export List from the context menu:
Review Export and Copy CSV for more exporting details and options. -
Open the saved CSV file with Excel or another spreadsheet software 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 properly for 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 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 to EBMS vendor catalog
The contents of the spreadsheet that was exported from EBMS in Section A and manipulated using Excel or another spreadsheet software must be converted from a CSV file to a vendor catalog file before being imported back into the EBMS product catalog.
Complete the following steps to configure this spreadsheet to vendor catalog 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 a previously configured conversion from spreadsheet to EBMS file.
- Enter a Description to identify this import/export mapping record as shown below:
-
Select the spreadsheet by clicking on the Browse button next to the Text File field 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 automatically map the CSV file as shown below. The default settings should map the spreadsheet file to a new EBMS vendor catalog file. Review Importing Text Files into EBMS 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 the Create File button at the bottom of the page 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 tab from the main EBMS menu.
-
Click on the New button on the right-hand side of the page to create a new Vendor Catalog configuration record or highlight a listed file and click Properties to open an existing catalog.
-
On the Properties page, click the look up button (...) to the right of the Vendor Catalog field to view the available EBMS files.
-
Select a Vendor Catalog file from the open file drop-down list and click OK.
-
Set a Description that defines the purpose of the vendor file.
-
For Key Type, select the Inventory ID setting.
-
Enter the ID into the ID entry as shown above.
-
Leave all Source settings blank and click OK to save.
-
If this is a new vendor catalog, 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. In the example above, 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 the 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 of these options: Search Catalog when entering a product ID or UPC and the Update from additional Vendor Catalog when importing options. Click Next to continue.
-
Enable all the columns that require sorting. The only important Field that requires sorting is the ID Field. Click Next to continue.
-
Put a checkmark in the Update column for all fields that should be overwritten in the EBMS product catalog by the data from the new vendor catalog. The important column to Update in the example above is the NEWPRICE to BASE price column from the calculated pricing within the spreadsheet. Since Update is checked for this column mapping, BASE will be overwritten by NEWPRICE. 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.
-
From the Inventory > Options > Vendor Catalogs tab, 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 setup is required. Follow these steps to export an EBMS product list to a CSV, convert the CSV to a vendor catalog file, and import the vendor catalog to EBMS.
-
Export from the EBMS product catalog to a spreadsheet: Open the inventory list and query to the product group that requires an update. Right-click on the queried list, select Export List from the context menu, and export as 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 on the right-hand side of the page. This will open an Import Executive window. Put a checkmark in the Replace column to replace the product records or a checkmark in the Update column 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 the Update button on the right-hand side of the Vendor Catalogs tab 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