Excel Integration Process

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:

  1. 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.
    TIP  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.
  2. To show the Developer Tab in your Excel.
    1. Click the File tab.
    2. Click Options.
    3. Click Customize Ribbon.
    4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.
    5. Select OK to save the new setting.

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

  1. Select the Developer Tab.

  2. Click on the Source icon.

  3. On the right hand side, the XML Source window will appear. Click on the XML Maps button.     

  4. Select the Add button in the XML Maps window.      

  5. Find the SaberisExcelTemplate.xml document that you downloaded and select it.     

  6. You will get this warning. Just Click OK.

  7. Click on OK again.

  8. The XML Source Window will now show this:    

  9. 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".        

  10. You are now ready to do the mapping from the Saberis Excel Template. Follow these rules:    

    1. Start at the first required column. In this example column A.
    2. 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.

  11. 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.

  12. TIP  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.
  13. 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. 

    1. 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.

    2. 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.

    3. 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.

  14. 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.