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 choose "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 probably not useful for Excel imports.

Use Pricing Cost Factor

This is not needed for this Module. The Cost Price is available or there is no cost price at all. The cost will be picked up from the ERP database.

Rule for Selling Price

This is set to "Use factored pricing from data". There is probably no selling price. This 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.

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.

The solution is to map the Saberis Excel Template to the Excel Spreadsheet. Once you understand the process this is a 30 second task.

First there is the 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 will download in a zip file. You must extract the XML file from the zip and 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:
- Click the File tab.
- Click Options.
- Click Customize Ribbon.
- Under Customize the Ribbon and under Main Tabs, select the Developer check box.
- Select OK to save the new setting.

Now, for each spreadsheet that you want uploaded, complete the following steps:

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.
Select the Add button in the XML Maps window.
Find the SaberisExcelTemplate.xml document that you downloaded and select it.
You will get this warning. Just Click OK.
Click on OK again.
The XML Source Window will now show this:
Make sure you have a non-data row above the columns you want to export. This is where you will place the mapping "tags".Data will be exported starting from the row after the one with the mapping "tags".
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.
- Never skip a column. If you don't want the data that is in a column, or the column is blank, you must either delete the column or drag a "column" tag to it.
In this example, drag the Quantity map from the XML Source window to the A1 column.
Now drag 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.

Now that the columns are mapped, you may assume that you can export the data to an XML document that SaberisConnect can process. WRONG!

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.

We are going to explain three ways to do this.
- 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 thinks it is smart and stops at the first empty cell. That is why the above example only highlighted the first row and stopped.
  
  To fool Excel 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 is fine if the first column is Quantity. If it is Product SKU, you can't do it.
- Another way that may be the best way, is to insert a new column in front of the first column, and do the Find blank and replace with 0 or anything. Don't forget to map the new column with a Column map. Notice that all the rows automatically get 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.