An Ultimate Guide To Import Your Spreadsheet Data To OpenBOM

Spreadsheets (or Excel) is the second best software to manage your data. I have a love-hate relationship with Excel and I can see how engineers and manufacturing companies may feel the same way. Excel is powerful and messy at the same time. 

A spreadsheet can be a quick remedy for your problem but in the long run, you always lose because you cannot manage data using Excel properly. Check out these 6 points before starting BOM in Excel. 

Every company we work with usually has tons of information already managed using Excel – Bill of Materials with many additional pieces of information added to this spreadsheet to manage cost, purchasing, and many other reasons. OpenBOM gives you multiple options to import information – via spreadsheet, via CAD add-in, or using REST API. You can learn more about it here. 

In today’s article, I will give you an outline of how you can import data from spreadsheets to OpenBOM. There are 3 steps in the process – (1) understanding the data; (2) Importing the catalog data; (3) importing the BOM data. Let’s take it step by step. 

OpenBOM Data Model

Before jumping into the import process, please spend a few minutes freshening up on the core OpenBOM data model. Check out this reference guide and this article. 

The data about BOM (Product Structure) in OpenBOM can be divided between two elements – Item and Instance. In a nutshell, every part or assembly is an item and it has a set of properties – together with Part Number, they are defined in the catalog. Once the item with a Part Number is defined in the catalog, it can be used in BOM (product structure), which has instance properties. A simple example of this model is in the picture below. 

Part Number is used to uniquely identify the item in both catalogs and BOMs. Each item is defined with 3 properties – Cost, Description, and Manufacturer. Item instance (BOM) has one property – Quantity.Understanding and Preparing The Data 

Excel is not a good tool to manage structured data. A typical excel or spreadsheet file will include a flat table of data, which will mix the data about items and instances. So, when you want to import Excel, you have data that needs to be analyzed. Here is an example: 

In the spreadsheet above you will find BOM records combined with part numbers, quantities, and other properties. Some of these properties belong to an item and some to a BOM. What you will need to do is two steps: 

1- Take all columns with the data belonging to a catalog and save it in a separate spreadsheet. This spreadsheet must look like a flat list of all items with part numbers and any other properties. 

2- Take all columns with the data belonging to a BOM and save them separately in a spreadsheet. This spreadsheet in most cases will include 2 columns – Part Number and Quantity. In some situations, it might have some other instance properties (eg. Reference designators), but in most of the situations, you will have Part Number and Quantity. 

Import Catalog 

Use the spreadsheet with the catalog data you saved and use the Import catalog command to import data to the catalog. You can use the Import catalog from the dashboard as well as the import catalog within the catalog. The import process can be done as many times as you need and you can import data in steps. What is important is to import data together with Part Numbers and additional properties. OpenBOM import catalog function will merge this data together.Import BOM 

Take the spreadsheet with BOM properties (Part Number and Quantity) and use the Import BOM command to create a BOM. OpenBOM will create a single BOM from each Excel file you imported. If you need to import a multi-level structure, add the “Level” property in Excel as it is described in the documentation. 

Once BOM is imported, use the Part and Catalogs command in the BOM settings to connect catalogs you imported to the BOM. As a result, OpenBOM will connect the information and will display a product structure with all the appropriate information.  

Conclusion

OpenBOM provides a robust and flexible data model to manage product structure. To import data from a spreadsheet, you need to split the data into two spreadsheets – catalog data and BOM data and then import it to OpenBOM. The process allows you to import as much data as you need from multiple sources to create catalog(s) with different items and then import BOMs using separate files or a single file with the “Level” property. 

If you haven’t already, check out what OpenBOM can do for you – REGISTER FOR FREE and start your 14-day trial today! 

Best, Oleg