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.

BE VERY CAREFUL WITH IMPORT.  DO NOT (RE)IMPORT AN EXCEL INTO EXISTING BOM, YOU MAY OVERWRITE YOUR BOM.  See the help section on Synchronizing Excel changes to OpenBOM.

(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.

Conclusion.

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 July, 2024

Welcome to the OpenBOM What’s New July 2024 release! I hope you’re having a fantastic summer. It’s scorching here in…

15 July, 2024

OpenBOM is excited to announce significant enhancements for our customers with the introduction of the ability to manage xBOM (BOM…

12 July, 2024

Non-recurring engineering (NRE) costs are critical, one-time expenses incurred during the design, development, and testing of a new product before…

12 July, 2024

In the world of cloud-based PLM solutions, “multi-tenancy” is a frequently mentioned term. It’s often presented, especially by major PLM…

10 July, 2024

Talking to many companies, it’s not unusual to hear a familiar refrain: “We don’t have time to solve data management….

9 July, 2024

Product Data Management (PDM) has been around for a long time, but its core functionality has remained largely unchanged. Initially…

5 July, 2024

It is July 4th in the US—a time to relax, reflect, and think about the future. Today, I want to…

4 July, 2024

In the fast-paced world of electronics design, managing content and integrating it with existing data is critical for efficiency and…

3 July, 2024

In today’s highly competitive market, seamless communication and data sharing between departments are crucial for success. One of the common…

To the top