Importing from Excel to OpenBOM

Overview

The ugly fact is that most companies have BOMs and/or Catalogs (inventories) stored in Excel.  OpenBOM has a variety of features to help you import and export this information directly to OpenBOM.  This section will take you through:

1. Importing an existing Excel to a new OpenBOM or Catalog
2. Importing a revised Excel into an existing BOM or Catalog

1. Importing an Existing Excel to a New OpenBOM or Catalog

The process for creating a BOM or Catalog from an existing Excel is almost identical. A small amount of preparation can greatly improve this experience.  Fortunately, OpenBOM can process either a formatted or unformatted Excel.

Here’s a quick video that walks you through the process. 

A few things to consider when importing from Excel:

• A little preparation will greatly improve the importing experience
• OpenBOM ignores justification, font, size, case, etc.
  OpenBOM imports from the first sheet only
• The Column titles (headers) will become the OpenBOM properties so spelling counts

Preparing an Excel for Import into a New BOM or Catalog:

• Format your Excel spreadsheet so that all information starts on row nine (9).  Rows 1-8 can be blank or contain other information which will not be considered during the Import process.
• Row nine (9) is your column header row, these are the “Properties” which will be created in OpenBOM.  This is a good time to standardize on column (property) names in your organization.
• If you have a “Part Number” or “Quantity” column be sure they are spelled exactly as shown.  
• Be sure there are no duplicate column headers (properties).

Actually Importing your Excel into OpenBOM as a New BOM or Catalog

From the OpenBOM Dashboard, click the “Import” button and select “Import BOM from spreadsheet” (or import catalog)  from the drop-down menu.  

Select your spreadsheet file as shown below by clicking “Select File” and choosing your .xls or .xlsx file.

For a properly formatted Excel, the defaults below will work.

Here is what a properly formatted Excel file looks like to create a BOM.

Once the dialog is complete – Click “Import” and your BOM or Catalog will be created.

What if I Have Problems Importing?

If OpenBOM encounters and formatting issues the following message will be displayed.  In this example, our Excel file header was on tow 6 and our part number property is named “PN”.

Correct the values for property row or part number name as needed and try again:

Download a pre-formatted Excel template below:
Excel Template for Import to openBoM

2. Importing a Revised Excel into an Existing BOM or Catalog

NOTE: please read this carefully, it is possible to delete existing data which cannot be recovered using this command.

Once you have created a new BOM or Catalog (often referred to as documents in OpenBOM)  from an existing Excel spreadsheet you may have a need to update the original Excel (for example, if it is the output from an ERP system or likewise).  Of course, you will any changes to the Excel to be reflected in your OpenBOM document.

The update process involves re-importing the revised Excel into where OpenBOM will automatically update your BOM or Catalog with any changes made to the Excel. This process is formerly called “Merging”.

It is important to understand exactly what Merging does. OpenBOM using the following rules to determine which data is preserved during the merge:

1. Any item (Part number)) which exists in both OpenBOM and the Excel will be preserved
2. Any changes made to an item in Excel will be made to the OpenBOM document

CAUTION: Importing to an existing BOM will overwrite the existing BOM with the imported BOM.  Be careful – item #1 just above states that an item will be DELETED if it does not exist in both the OBM ad the new import.

The UI for importing into existing BOM is here:

Here is a short video on merging changes from an existing Excel into an existing OpenBOM catalog.

Sharing is caring!