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