Excel

Data from any Excel Spreadsheet can be processed by SaberisConnect. You must use the Saberis Excel XML Template. Please see the Excel Vendor Process page.

Export Filename Source

The file name can consist of up to 4 variables. The default is shown below with:

User Name
Export Default Name (Default is EXCEL)
Project Name
Date plus time (EX: 20170308 plus 1127)

You can delete any of the variables or change their order. If you are saving the export files to a desktop, you don't need the User Name.

Please see your specific ERP/POS system for more suggestions.

Export Default Name

This can be anything you want. You may want to use "EXCEL".

Export Filename Extension Setting

This is used for the Catalyst/Falcon system where the import list can be filtered using a different extension for each Vendor Module.

Use Customer Number

This is not needed by most ERP systems. The BisTrack integration requires this to be populated if you have not set a "company default customer" value in the BisTrack system. If you have multiple locations, you can add default customer numbers by location in your Location Settings.

Sales Type

This is used by the BisTrack ERP System. By default, BisTrack creates a new sales order. If you want BisTrack to create a new Quote instead, then set this to quote.

Default SKU

A value is required in this field in order to pass the quantity, cost and sell values for each item in the document. This value should match the "special order" or non-stock SKU values created for this vendor in your POS/ERP system.

Use Phrase mapping

This can reduce the description by at least 50%. It is basically a "find and replace" tool that allows you to abbreviate or blank out unwanted text. See the Phrase Mapping instructions. Once you do this mapping, all orders will be processed using the mapping data you entered.

Phrase Mapping is less useful for Excel imports which already have short description lengths. Some ERP systems also override the description for stock sku items.

Use Pricing Cost Factor

This can be disabled. Typically, the cost price is available or there is no cost price at all. The ERP system will also use cost pricing from their database for items with a stock SKU.

Rule for Selling Price

This is set to "Use factored pricing from data". If list price is given in the data, you can set a factor for selling price. If the data does not contain the list or selling pric, it can be set in the ERP system as a markup on cost, gross margin, or even a bottom line amount.

Many estimating programs store or export data in Excel spreadsheets, or you may have your own Excel estimating Worksheet setup. The import solution is mapping the Saberis Excel Template to the Excel Spreadsheet. Once you understand the process this is a 30 second task.

This section will show the step-by-step process to get the data from an Excel Spreadsheet to an XML document in a folder where you can upload it to SaberisConnect.

One-time Setup

Download and save the Saberis Excel Template XML document to a convenient folder. This document will be loaded into every spreadsheet. It will allow for the mapping of your columns to the required XML export for SaberisConnect.

You must extract the XML template file from the zip file. Then save it to a convenient folder. You will need it for every spreadsheet that you want to upload to SaberisConnect.

To show the Developer Tab in your Excel:

Select the File tab.
Click on Options.
Click on Customize Ribbon.
Under Main Tabs, click on the Developer check box.
Select OK to save the new setting.

Import Process

You need to complete the following steps for each spreadsheet that you want uploaded.

Select the Developer Tab.
Click on the Source icon.
On the right hand side, the XML Source window will appear. Click on the XML Maps button.
Click on "Add" in the XML Maps window.
Find the SaberisExcelTemplate.xml document that you downloaded and select it. You will receive this informational message. Click OK.
Click on OK again to confirm the selection.
The XML Source Window will now show the elements for XML mappings.
Make sure you have a non-data row above the columns you want to export. This is where you will place the mapping elements or "tags" that will build an XML document for import.
You are now ready to do the mapping from the Saberis Excel Template. Follow these rules:
- Start at the first required column. In this example column A. Continue applying the mappings from left to right.
- Before exporting, each column must be mapped. If you have a blank column or one filled with unnecessary information that you don't want carried over to the ERP system, either delete the column or use the Column 1 through Column 5 mappings. Start by dragging the Column 1 element to the first column of unnecessary information. Then repeat this process with the Column 2 element and so on until each blank column or one containing information for removal is mapped.
In this example, drag the Quantity map from the XML Source window to the A1 column.

You can also click on the A1 cell and then double click on the Quantity element in the XML Source window. This second method will automatically apply the mapping to the entire column containing data as long as there are no blank cells.
Now drag or double click the other map names. UOM followed by ProductSKU. Column D is blank, and you don't want it in the data, but it must be mapped. So, use the Column1 map name. Now add the two descriptions.

You may not want to import UOM into your ERP system. Check with their support to see what happens if the UOM in the Excel data is different for the SKU in the ERP system EX: Ea vs. Each.

The file is almost ready for exporting. If you export now, only the first line which happens to be a "comment" line will be exported. You must make sure all rows and columns that are to be included in the data are highlighted.

Here are three ways to highlight the cells:
- Click on the corner of the right bottom of the highlighted row(s), and drag the highlighted window down to the bottom of the data rows. This is quick and easy unless you have hundreds of rows.
- For a large number of rows, the template is designed to automatically highlight them all. However, for this to work, the first mapped column cannot have a blank cell. Excel will stop highlighting at the first empty cell. That is why the above example only highlighted the first row and stopped.
  
  For Excel to continue highlighting data, you can do a find and replace for column A. Highlight column A by selecting it. Now click on the Home Tab, and at the far right should be the Find & Select command.
  
  This must be done before you map the columns.
  
  Select the Replace command and enter a zero "0" in the "Replace with" box. Leave the "Find What" box empty.
  
  Now click on Replace All.
  
  This method works if the first column has "Quantity" data, since a quantity of zero is equivalent to a blank cell. If the first column has Product SKU data, then you can't replace blank cells with 0.
- Another way is inserting a new column in front of the first column, and using the find and replace function with a zero replacing blank spaces. Don't forget to map the new column with a Column 1-5 element. Notice that all the rows automatically are selected.
The final step in Excel is to export the data under the Developer Tab. Save it to your folder to upload it to SaberisConnect.

You have now saved a lot of time by not having to manually enter the data.

Tips

SaberisConnect only uses the XML document that is produced by Excel with the export function from developer tab. We do not need the Excel document. If you save a copy of an Excel document with the XML mappings loaded, but with no data, it can be used as a template and starting point for each quote or order that you want to generate.

When you export the Excel file with data and the mapped columns, you will be prompted to save the export XML. Then if you wish to have a copy of the original order information but don't need both the Excel and XML file, you can discard your changes when you close the Excel file. This allows you to keep a copy of the XML file for processing in SaberisConnect and also reuse the Excel "template" file with the xml source elements loaded. For each new order or quote, you can copy and paste your data into the "template" file.

Changing an XML Mapping

To remove an XML mapping, go to the XML Source workbook. Then right click on the corresponding element and click "Remove element".
Before you can change the XML mapping, you have to delete all the mappings from the rightmost column to the column that requires changes.
To add an XML mapping, drag an element from the XML Source onto the corresponding column in row 1. Repeat this until each column is mapped.

Stock vs Special Order Items

At a minimum, stock items require the quantity, SKU and UOM values to be populated. While stock item descriptions may be ignored by the ERP system, including them can be useful for cross referencing. Pricing is typically controlled by the ERP system based on the Stock SKU.

Special order items require at a minimum the quantity, description, cost, and selling price. ERP systems typically assume that there will be custom pricing for special order items.