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
29 November, 2022

Welcome to the last set of videos from OpenBOM Video Demo Series. In this article today, I’m going to cover…

25 November, 2022

There are multiple holidays you need to work around as a manufacturing company. However, there is one holiday that heavily…

24 November, 2022

Change is the most important and permanent thing when it comes to design, product engineering, manufacturing, and procurement. OpenBOM gives…

23 November, 2022

Welcome to another article featuring the OpenBOM introductory video demo series. Check out our earlier articles from our series below. …

23 November, 2022

To continue the new OpenBOM video demo series today we are going to talk about how to perform calculations. Making…

22 November, 2022

Are you using Onshape and looking for an effective way to streamline your CAD downstream process? If so, then OpenBOM…

18 November, 2022

Our VP of Products, Steve Hess sat down with Tomcar’s Chief Engineer Jonathan Drori to talk about Tomcar’s recent developments…

18 November, 2022

Another exciting OpenBOM release is out and in today’s article, I want to take you through the highlights of the…

18 November, 2022

Manufacturers are always looking for ways to improve their overall bottom line and grow. In order to do this, some…

To the top