Import Data from Spreadsheet

There are three main ways of importing data to OpenBOM: from spreadsheets, using integrations or through REST APIs. In this article, you’ll learn how to get your spreadsheet’s data into OpenBOM.

OpenBOM has a variety of features to help you import a spreadsheet to an OpenBOM BOM or catalog. This section will take you through:

The process for importing a BOM or catalog is almost identical. A small amount of preparation can greatly improve this experience. Fortunately, OpenBOM can process either a formatted or unformatted spreadsheet.

Before importing data, it is highly recommend to review basics about how OpenBOM manages data, catalogs, items and product structures (BOMs). Check Get Started section of training library and more specifically these two articles:

Understanding Bill of Materials

Ultimate Guide to Import Spreadsheet Data to OpenBOM

BOM Import Basics:

To import a BOM, you first need to ensure that you have imported or created items in the catalog(s). You can import catalog items from spreadsheets or your favorite CAD system.

If you have already imported or created items in catalogs, you can import a BOM from a spreadsheet. You need to import only BOM (instance) properties. You can import a BOM using fragments (each level), or you can bring them all together using the multi-level BOM import option.

Note that the OpenBOM Add-in can automate the entire process and import data (catalog items and BOMs) with a single click.

Preparing a Spreadsheet for Import into a New BOM or Catalog:

Format your 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 column (property) names in your organization.
You must have a “Part Number” column or something which will serve as part number (selectable on import). Note: Part Number column must have a value. OpenBOM requires a unique value for each Part Numbers when importing data to catalogs.
Be sure there are no duplicate column headers (properties).

A few more things to consider when importing from a spreadsheet:

OpenBOM ignores justification, font, size, case, formulas, merged cells and other advanced spreadsheet formatting.
OpenBOM imports from the first sheet only.
The column titles (headers) will become the OpenBOM properties, so spelling counts.
Images will be imported as long as they are bound to a cell.
Don’t duplicate property names or property numbers.
There are certain requirements for importing links.
More help on multi-level import

Importing a Spreadsheet to a New BOM or Catalog

From the OpenBOM dashboard, click the “Create” button and select “Import BOM” (or “Import Catalog”) from the drop-down menu.

Select your spreadsheet file as shown below by selecting your source file from Excel or Google Drive.

For a formatted spreadsheet, the defaults below will work:

Once the dialog is complete, click “Import” and your BOM or catalog will be created. Finished!

Note: Dashboard imports using the “Create” button -> Import Catalog operation will always create a new catalog. If you import an Excel named “Hadware.xls” you will get a new OpenBOM catalog called “Hardware.xls”. Later if you import another (different or the same) Excel named “Hardware.xls” you will get an OpenBOM catalog called “Hardware.xls-1” (note the “-1”).

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

Importing a Revised Excel into an Existing BOM or Catalog

Once you have created a new BOM or catalog from an existing spreadsheet you may have a need to update the original spreadsheet (for example, if it is the output from an ERP system or likewise). Of course, you will also want these changes to the original spreadsheet to be reflected in your OpenBOM BOM or catalog.

The update process involves re-importing the revised spreadsheet into OpenBOM. The rules of the import is different for BOMs and catalogs.

1- Importing Excel (or spreadsheet) into existing BOM.

When you import data into existing BOM, OpenBOM will attempt to “sync” data between Excel and BOM in OpenBOM. Which means the following rules: (1)- All new items from Excel will be added; (2)- All existing items from Excel will be updated; (3)- If the item, doesn’t exist in Excel, it will be deleted from the BOM. The “sync” nature keeps BOM in synch with external data source.

2- Importing Excel (or spreadsheet) into existing catalog.

Importing data into existing catalog is using different rules than BOM. In the case of the catalog, our goal is to accumulate all data, so new excel will become a source of data – (1) New items added; (2) updated items changed, but (3) if items doesn’t exist in Excel, the data item won’t be deleted as it was with the case with BOM.

It is important to understand exactly what merging does for catalog(s). OpenBOM applies the following rules to determine what to do with data during the merge:

Any item (part number) which exists in both OpenBOM and the spreadsheet will be updated, that is, changes in the spreadsheet for that item (part number) will be brought into OpenBOM. Example, if part number 1234 exists in both OpenBOM AND the spreadsheet, and “Description” for 1234 is different in each, then the value of “Description” for 1234 will be brought from the import.
Any item (part number) which exists in the spreadsheet but not in OpenBOM will be added.
Any item (part number) which exists in OpenBOM and not in the spreadsheet will be ignored.

The button for importing into existing BOM is here:

Multiple Imports:

Please note that there is a difference between re-importing a spreadsheet to a catalog than to a BOM. If you are re-importing a spreadsheet to a catalog, OpenBOM will read the spreadsheet and “append” the data from the spreadsheet to the existing catalog. This means that OpenBOM will not delete any existing items in the catalog.

On the other hand, for BOMs, OpenBOM will remember what was imported before and will try to delete data from your catalog or BOM if data doesn’t exist in Excel. The best approach in this case is to export the data to Excel if you want to import it again and you’re afraid that data is not in sync.

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

What If I Have Problems Importing?

If OpenBOM encounters any formatting issues the following message will be displayed. In this example below, our input spreadsheet header was on row 6 and our part number property is named “PN”.

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

Importing Multi Level BOM

OpenBOM supports the import of a multi-level BOM spreadsheet as long as the spreadsheet has the following conditions:

You must have a column called “Level”.
The levels must be defined as 1, 1.1, 1.2, 2, 2.1, 2,2….
You can import an unlimited number of levels. But you need to keep them in the right sequence.
Example:
1.1
1.2
1.2.1
1.2.1.1
1.2.1.2
1.3
1.3.1

Example of a spreadsheet which will create a multi level BOM on Import:

Level	Part Number	Description
1	HA-100	Housing Assy
1.1	H-101	Housing Casting
1.2	B-101	Bearing Large
2	SA-100	Shaft Assy
2.1	S-100	Shaft
2.2	B-102	Bearing small

Here is a short video of how to import multi-level BOMs.

Download a pre-formatted Excel template:

Excel Template for Import to OpenBOM

Importing different data types – Currency, Date, Text, Numbers

OpenBOM import follows a few rules about data types and formatting. The data in Excel can be formatted differently and it can be sometimes very confusing. Therefore the following rules applied.

1. By default, all data is imported as “Text” and if there is no property with a specific type defined, a new public property will be created with the type “Text”.

2. If the property with the name matching the Excel column exists, OpenBOM will enter the text value from the cell as a property value. (we are planning to improve the mechanism – stay tuned)

3. To import data in currency property, you need to strip currency signs (eg. $, etc.) and replace comas (in European format) with dots.

4. URL must be formed in Excel (don’t use plan link text, because the result will be sub-optimal)

5. If both private and public properties exist with the same name, the private property will be taken a priority

Contact OpenBOM support if you have questions.