BOM to csv Best Practice
Last updated
Last updated
Accurate data input is crucial for generating reliable lifecycle assessments. This application note provides best practice guidelines for converting your Bill of Materials (BOM) into a CSV format that can be efficiently processed by the Emvide platform. Following these guidelines will help ensure that your data is accurately represented and that your LCA outputs are both reliable and actionable.
This application note is written for users of EmVide who want to find out more about using the Bill of Materials (BOM) data entry method with CSV files to produce a Product Carbon Footprint (PCF) analysis.
Source Information - CSV Checks
CSV Relationship Rules
Entering CSV Data
Nodal Relationships (Example)
Accounting Transport and Waste
Good BOM Content
EmVide’s AI interface is extremely powerful and capable of automatically assessing basic to complex BOMs with no user intervention. Users will find it an invaluable method to achieve results very quickly. With complicated or confusing BOMs though, it is sometimes appropriate for the user to make sure the BOM becomes ‘well structured’ to help EmVide’s AI to make its best judgements and so determine the best possible analysis outcome.
This application note gives an overview of ‘best practice’ setup of a BOM intended to be processed by EmVide and methods to achieve this. While user editing is often not necessary, identifying and correcting potential BOM issues before a full EmVide analysis can help reduce wasted time and effort. It can result in optimum use of EmVide credits and clarity of input information used in the analysis.
CSV stands for ‘Comma Separated Values’. It is a text file format that uses commas to separate field (column) values, and newlines to separate records (rows) within a data structure. A CSV file stores tabular data in plain text, where each line of the file typically represents one data record. EmVide uses this format to import data into a project. In addition to manual input of data (explained elsewhere), there are three main methods for users to enter product information into EmVide digitally.
Assisted AI Fusion: This method is used when a BOM contains poor, complex or missing information. It makes full use of EmVide’s co-pilot AI and is the recommended method.
Manual Consolidation: Used where a BOM is constructed in an ordered fashion and contains good data. This is still AI assisted to produce an enriched CSV for EmVide analysis.
Manual Construction: This method relies on the user’s knowledge of the Product BOM and abilities to work with Excel.
This method can also be adopted when the user wishes to gain a rapid emission assessment by utilising knowledge gained through AI processing and mining for related unknown information. The AI is instructed to break down all available data and make judgements on resources and processes used to make the product. It complements available data and is an automated step resulting in a Source CSV file containing a fusion of actual and AI-generated data derived from the BOM. This consolidation of data is an extremely powerful use of EmVide’s AI-driven Co-Pilot functionality and can save a significant amount of human R&D analysis time.
The method is initiated by the user manually examining the client BOM for clarity and suitability. It is useful to broadly identify anomalies, missing or superficial information. With complex, ambiguous or multi-level BOMs, a number of manual edits are advised. Removal of superficial information helps the AI pin-point and synthesise the right information to construct the Generated CSV file used for EmVide’s ongoing emission analysis.
Key User Actions:
Remove duplicated or erroneous header records. Only have ONE single header record (row) that describes the fields (columns) in the BOM (frequently built in Excel form).
Delete fields (columns) that are irrelevant (e.g. SKU, Rev No., Cat No. etc.). Retain all fields (columns) that are considered relevant to the record (row). These will include description – sometimes in multiple fields, weight, unit, waste, transport, geography and level (where relevant). This will assist the AI to ‘clean up’ the BOM.
Save the resultant Excel file as a Source CSV file.
The resulting Source CSV file is then used together with a product textual description (text file) to create a compatible Generated CSV file for EmVide to analyse the product. It is done by clicking the CSV FROM BOM button, entering a product name, copying (from product literature) and pasting a textual product description, and uploading the Source CSV into EmVide’s dialogue box and clicking GENERATE CSV button.
After a short while (a few seconds depending on BOM size), the AI will respond with a Generated CSV file downloaded to the user’s email address. That CSV file will contain ‘enriched’ information (process parameters etc.) and be 100% fully formatted for entry into EmVide for executing the product analysis.
At this point, it is useful for the user to save and examine the Generated CSV and when considered appropriate, make manual ‘last minute’ adjustments to relevant parameters before running the product analysis by clicking NEW PRODUCT and uploading the Generated CSV alongside relevant project information. See section Format of the Simplified BOM File for more information on CSV structure.
Where a BOM is constructed in an ordered fashion and contains good data, a user may decide to use capability in Excel to manually consolidate BOM data into a format compatible with EmVide’s CSV FROM BOM converter.
The CSV FROM BOM converter uses AI to both format a Generated CSV and add any unknown process information it considers relevant to make the product. It is important that the user adopts the field structure and naming convention shown below. If possible, the user should ensure the record information is as clear as possible and in particular, have an obvious name and good description for each BOM item (record) in the Excel Filtered BOM. (See section BOM Content Guidelines for more info.)
Users can adopt this method if they are satisfied that the client’s BOM is well laid out and contains all relevant information. The client’s BOM is manually ‘filtered’ by the user into a Filtered BOM typically using Excel and then ‘Saved As’ a Source CSV file with an appropriate filename. With a good Product BOM, the process of entering BOM records should be straightforward. Alternatively, Method 1 could still be used if the input information becomes confusing to the user. The structure of the required Filtered BOM Excel file contains only 4 fields with the following headings:
Name: What the material, resource or component item is.
Description: A best description of what the named item is.
Quantity: The numerical amount of the named item.
UOM: Unit of Measurement (kg, m, litre etc.)
Once exported, the Source CSV file is used together with a product textual description to create a compatible Generated CSV file for EmVide to analyse the product. It is done by clicking the CSV FROM BOM button, entering a product name, copying (from product literature) and pasting a textual product description, and uploading the Source CSV into EmVide’s dialogue box and clicking GENERATE CSV button. After a few seconds, the AI will respond with a Generated CSV file downloaded to the user’s email address.
That Generated CSV file will contain ‘enriched’ information (additional process parameters, expanded descriptions etc.) and be 100% fully formatted for entry into EmVide for executing the product analysis. At this point, the Generated CSV can be saved, optionally examined and where the user considers relevant, any appropriate adjustments made to the parameters. An EmVide product analysis can then be initiated by clicking the NEW PRODUCT button and uploading the Generated CSV alongside other relevant product information for the project.
It can be adopted to test emissions of new designs or to analyse existing products when clear product and process information is available.
Sometimes preferred by the OEM when their detailed knowledge of the product is known, it is often executed when the user is confident of source BOM data and processing methods to make the product. It can take some time (typically a few hours) to construct the Generated CSV but the upside is the user can gain a high degree of confidence in the source data which can easily be retained as an asset or adjusted for future or comparative analyses. Sensitivity analysis can be undertaken by an OEM on new designs or product revisions using historical versions of the Simplified BOM file.
It is important that the field structure and naming convention is used when constructing a Simplified BOM from the original Product BOM. By clicking the DOWNLOAD CSV TEMPLATE button, an example Excel file is downloaded to the user’s email address. It contains the approved field structure already formatted and can be manually modified by adding records from the Product BOM.
The table in section Format of the Simplified BOM File summarises the fields and nodal relationships required in the Generated CSV (produced from the Simplified BOM) and what they should contain.
The current 'Generated CSV' used to import data into EmVide has 15 fields, 7 of which are mandatory. The field names MUST conform exactly to the field sequence in the following table:
Use Microsoft Excel to construct a spreadsheet with these field names in horizontal cells. Rows can then be added from the client's BOM for all the resources/materials used, the individual processes and the type of energy used for each process. The content of each field must conform to specific rules to build up the final CSV file and are summarised in sections Source Information - CSV Checks to Accounting Transport and Waste.
TIP: Keep a blank version of the Excel structure as a template for your future analyses.
A particularly useful check may be made of the ‘’quality index’ data. The AI rates its analysis of each record (row) with a number between 0 and 1 of its assessment of quality relevance to the particular record item. Low numbers (less than say 3 or 2) should be scrutinised and rejected from the Source CSV file if deemed to have an irrelevant description. This double check is useful to ‘weed out’ rogue information that could disrupt EmVide’s analysis.
Users may also wish to add transport information as processes if transport has not been accounted for in the source data. Records relating to transport can be manually added to the Source CSV as processes and contain the appropriate transport mode and distances involved. More about transport is described in a separate Application Note.
Name: The record must have UNIQUE text in the name field. The ONLY EXCEPTION to this is the ‘node_type’ ‘process’ which can have the same name text but often it may be different to help distinguish a specific process. Where the same resource (or material) is used several times, try and add the ‘units’ together before inserting the resource into just one record to make an overall quantity to specify in the ‘units’ field.
Description: Give the best description you can of the ‘name’ field in the record. A good textual description will help the AI produce a more accurate assessment. For processes, you can put the process name in this field.
Unit of Measurement: Insert the type as kWh, kg, km, m or any other relevant descriptor. Note, ‘processes’ specified in the name field do not need any units specifying.
Units: The total numerical quantity of the used ‘name’ field for the record.
Node Type: The record must be one of three choices. Choose only one of the following:
Resource – what the physical item is in the record’s ‘name’ field.
Process – specify this if it is a process that’s in the record’s ‘name’ field.
Process Resource – specify this if the record’s ‘name’ field is a resource used in a process.
Parent Node: For ‘resource’, it is usually an input to or precedent to a ‘process’. A ‘process’ is the recipient of all relevant antecedent resources. Field name is always a following process. Left blank if at ending. For ‘process resource’, assigned to a particular process name.
Geography: Country Code (EN, FR, NL, CN etc.) or ‘Global’ identified by the AI. Can be left blank.
Quality Index: An indicator deduced by the AI on the item validity. 0 lowest -1 highest.
Quality Index Notes: Summary comments made by the AI of the ‘quality index’ value.
Linked Facility: Name of facility. This only applies to node_type process_resource. This must not be applied together with a linked_asset. Can be left blank.
Linked Asset: Name of asset (equipment or work cell used for a process). This only applies to node_type: process_resource. This must not be applied together with a linked_facility. Can be left blank.
Waste Ratio: A number from 0 to 1 to 3D.P. (related to a percentage) of waste happening in a process. If waste % is known, this should only be set in a process type node.
Notes: Optional textual notes for this particular record.
Enable Decompose: Set to TRUE or FALSE. Set to True for complex materials. When TRUE (default), the Emvide Copilot will decompose the material into its fundamental components, then evaluate the CO2e for each and then sum them all. Set FALSE when any PROCESS is a manual one (no energy consumed) or the material is basic.
Guide: Left blank.
NOTE: Records can be added in any order in the CSV so long as the relationships between records are valid and correct. EmVide will signal an ERROR if an invalid CSV file is uploaded.
When your Excel spreadsheet is finished, it must be saved as a CSV (comma delimited) file – not the UTF08 variant.
Your CSV file often starts off in an Excel spreadsheet in the format described previously. It should have a filename relevant to your project and if necessary, embody a version number. How the CSV is constructed is illustrated in the simple process map example shown below.
Here, three resources (A, B and C) are fed into OP1 which uses an asset consuming energy E1. OP2 then handles the output of OP1 (without use of energy) and passes its output to OP4. In parallel, two other resources (X and Y) are used in OP3 whose asset to process consumes energy E2. The output of OP3 then combines with the output of OP2 in OP4 to finalise the manufacturing process.
To reach this point, it can be useful to sketch out a basic process map if you do not have access to an automated process mapping tool, route cards or manufacturing schedule. Resources and their associated parametric information such as weight can be extracted from a Bill of Materials (BOM). Energy used for individual processes can be derived from asset power and utilisation time with any appropriate duty cycle information. The resulting data can then be placed into your Excel spreadsheet.
Building relationships between the main elements is crucial to a successful CSV file import into EmVide. In this example, we show 11 records for the 3 key fields in the relationships necessary to make the CSV from the process map shown above. They conform to the CSV Relationship Rules listed above. Particular attention should be paid to the precedent and antecedent of the ‘parent_node’ when building the structure.
This is what the completed Excel spreadsheet looks like (contains hypothetical numerical data):
This is how the exported Generated CSV file looks in textual form when ‘Saved As’ from Excel:
Accounting for Transport and Waste in source data is explained in more detail in another Application Note. Here is some introductory information.
Transport is regarded as a ‘PROCESS’ in their model and assign distances (default km) and mode of transport (car, van, HGV etc.) and fuel type (EV, petrol, diesel, LPG etc.) accordingly. Base information can be added to a Generated CSV as a PROCESS with description identifying these parameters. How this is done is explained in more detail elsewhere.
Waste is assigned to a specific RESOURCE/MATERIAL as a figure between 0 (default ‘blank’) and 1 (100%) within each record (row) of the Source CSV or Filtered BOM.
Clarity of source information and how it is compiled into a Product BOM or Source CSV is important and should not be underestimated. Here are some guidelines for producing good BOM content that will help EmVide determine the best possible result. Serious consideration of these points should be made when preparing BOM information for EmVide's CSV FROM BOM generator.
Make the resource/material textual name meaningfully obvious. No product codes.
Clarity of resource/material description is important. To avoid AI confusion, use of product codes or values in the description is strongly discouraged unless it adds useful information to the particular item. Include composition or raw material information if possible. The co-pilot will consolidate description fields into one composite description and will 'edit out' bogus wording.
Description levels within the BOM are permitted but clarity of how levels are set up is important to avoid ‘double counting’ of BOM items.
No blank cells.
No abbreviations.
Default language EN (but the AI will make best translations in the Generated CSV file).
Where resources are specified with dimensions or volume, add material type (e.g. chromium steel) so that the AI can estimate the resource weight for that particular record item.
Try to consolidate multiple use of the same resources (e.g. washers or screws). While the AI will attempt to aggregate repeated resources, consolidating them into one record is best practice.
Never leave a quantity blank.
Make UOM units MKS standard (kg, l, m2, mm, km etc). Multipliers are permitted – e.g. tonnes, m3.
Always specify quantity as a numeric value. If 5 off items of 1kg are used, specify 5kg.
No negative values.
Try not to vary the text of names of exactly the same resources.
Where sub-assemblies are included, give a good name, description and weight.
Do not include tools or tooling used to make the product. The AI will trap out resources with words like ‘jig’, ‘press’ and ‘tool’ by assigning a ‘low quality’ index.
With length items such as cabling, specify the lengths and describe the grade/composition.
Avoid the use of merged Excel cells if possible.
The goal is to produce a contiguous ‘clean’ file of good quality information that EmVide can interpret to a high degree of confidence and produce a meaningful analysis result for the user.
BOM Levels: The CSV FROM BOM Generator has this capability: BOM Inventory levels (up to level 4). If the input CSV has columns like "Level" and "Parent Item Name", it will consolidate all level 2+ information into level 1's description.
You just need to start a new product analysis by clicking menu ‘Product’ and pressing the NEW PRODUCT button at the top right of your screen. That opens up a new product window. After filling in your new project details, simply click the CSV UPLOAD button to upload your Generated CSV file from the saved location on your computer, then click SAVE to run the emissions analysis.
EmVide will then activate its Co-Pilot to analyse your project and automatically construct a Canvas.
