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
16 April, 2024

In the space of modern industrial operations and manufacturing, the quest for efficient CAD data management, streamlined BOM management, and…

12 April, 2024

Culture eats strategy for breakfast, technology for lunch, and stays hungry for dinner. The manufacturing industry is getting more complex,…

12 April, 2024

Choosing the right contract manufacturer (CM) is an important decision for anyone launching a new product. The quality of your…

11 April, 2024

In the world of woodworking, but not only, you can face fractional units. When working with Autodesk Fusion 360, integrating…

10 April, 2024

Ease of use and friendly user experience are becoming super critical for any software environment. If you’re familiar with older…

9 April, 2024

In today’s digital age, data management holds paramount importance, especially for engineering and manufacturing organizations. As everything transitions into the…

5 April, 2024

I’m super excited to share that OpenBOM is going to attend the Digital Transformation Forum in Boston early in May….

5 April, 2024

Preparing for mass production is stressful. There are usually years of hard work that went into development, creation of prototypes,…

4 April, 2024

Manufacturers of all sizes are looking to improve their processes. Digital transformation is a big topic in all organizations today….

To the top