Best practices for importing a BOM or Catalog into OpenBOM from an existing spreadsheet

Oleg Shilovitsky
Oleg Shilovitsky
18 August, 2019 | 4 min for reading
Best practices for importing a BOM or Catalog into OpenBOM from an existing spreadsheet

Best practices of Import BOM or Catalog to OpenBOM from your existing spreadsheet 

The things are rarely happening in the vacuum. Usually, you have already a bunch of data in spreadsheets you want to bring to OpenBOM to keep managing or using this data in the future. What are the most typical use cases? Over the course of working with our users and customers we found 3 main use cases: 

1- You have a central spreadsheet that manages all the components you have in the company. If this is the case, you can import it to OpenBOM to start your work. 

2- You have Bill of Materials you’re getting from design contractors with Bill of Materials prepared in CAD systems. 

3- You have Excels (or online spreadsheets) with a master data about specific project/product developed by the company.

You can use OpenBOM import from the spreadsheet function to bring data to OpenBOM. 

The fact is that most companies have BOMs and/or Catalogs (sometimes called part catalogs or inventories) stored in Excel.  OpenBOM provides a quick and easy way to import your existing BOMs and get to working on them in OpenBOM right away.

The process for importing a BOM or Part Catalog from Excel is essentially identical. The key to a successful import is a properly formatted Excel.  Fortunately, formatting is easy and has some flexibility.


(1) Begin by ensuring your Excel meets some basic formatting requirements:

  • Attention:  If you are importing from Excel as a multi-level BOM the title of the column must be “Level” and the levels must start be defined similar to 1, 1.1, 1.2, 2, 2.1, 2,2….  etc.
  • Property ROW defaults to Row 9 (you may specify on import)
  • MUST have some property which will act as Part Number (again, can specify on import)
  • OpenBOM ignores all formulas
  • OpenBOM ignores formatting, fonts, justification
  • OpenBOM imports from the first sheet
  • No duplicate property names
  • No duplicate part numbers
  • No “merged cells” or another advanced formatting in Excel

There are certain requirements for importing Links

(2) Next, perform the OpenBOM import. In the OpenBOM Dashboard, click the “Import” button and select “Import BOM from spreadsheet” from the drop-down menu.

Now choose your desired spreadsheet from your PC or Mac when prompted.

The OpenBOM import dialog appears with two fields set to defaults: Row Number and Part Number property name.  If you have pre-formatted your Excel to use row number 9 and “Part Number” then nothing to do here. Otherwise, set the appropriate values and import.

(3) Click Import

(4) If the Excel file was properly formatted the imported Excel will open as a Bill od Material in OpenBOM.   That’s it! You can begin to work

Return to the dashboard and see your import listed there as an OpenBOM BIll of Material.

View and download a pre-formatted Excel template by clicking, here.

Multiple Imports:

Keep in mind that OpenBOM can keep data in sync with an external Excel spreadsheet by importing data multiple times. In such case, 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 is to export data to Excel if you want to import it again and you’re afraid that data is not in sync.


OpenBOM import mechanism is simple and straightforward. OpenBOM will be using existing properties available in OpenBOM or create a new one in OpenBOM. So, you don’t need to worry about creating and mapping properties. There are several import limitations we are working to remove such as import images and checkboxes. We are also working on Google Sheet import coming soon in the next release.

If you have any questions, please reach out support @ openbom dot com.

Best, Oleg @ openbom dot com.

Let’s get to know each other better. If you live in the Greater Boston area, I invite you for a coffee together (coffee is on me). If not nearby, let’s have a virtual coffee session — I will figure out how to send you a real coffee.

Want to learn more about PLM? Check out my Beyond PLM blog and PLM Book website

Related Posts

Also on OpenBOM

4 6
22 March, 2023

Welcome to the March 2023 edition of “What’s new in OpenBOM”! We’re excited to share the latest updates and improvements…

21 March, 2023

The demand for innovation and speed in manufacturing has never been higher as it is these days. For the last…

17 March, 2023

These days everyone wants to accelerate their activities. The speed of the business is growing. We need to have a…

17 March, 2023

Manufacturers have always been looking for ways to gain a competitive advantage. In the past, they have looked to hire…

16 March, 2023

The world of product development has undergone a significant transformation in recent years, with digital technologies revolutionizing how businesses design,…

15 March, 2023

OpenBOM for Altium Add-in is a powerful tool that can streamline the process of creating and managing bills of materials…

14 March, 2023

Product Lifecycle Management (PLM) software was traditionally only used by large enterprises due to its complexity and high cost. However,…

10 March, 2023

You might have been in a position where you have seen a quality problem and no matter how insignificant this…

10 March, 2023

Releasing a new product into the market can be an exciting, yet nerve-wracking experience. A lot of time, effort, and…

To the top