Automatic Mapping for Excel Files

If your "bill of materials" (BOM) files are in Excel format, you can use the automatic mapping method to easily build a template based on the column headings.

In the following example, you can follow along by using a copy of your own BOM file or by using the sample file that is shipped with the software.

Sample BOM files are available in the example database installed with the software (called "Lambda_Predict20_Examples.rsgz20"). To access this database, choose File > Help, click Open Examples Folder, then browse for the file in the Lambda Predict sub-folder.

The name of the project is "Import Bill of Materials." Note that the examples were designed for the MIL-217 prediction standard. If that standard is not included in your software license you can use any other standard available to you, but keep in mind that only the first eight data fields in the file can be mapped correctly. The rest of the fields may not have appropriate matches in the standard you are working with.

Creating an Import template via Automatic Mapping

1. Examine your BOM file and take note of the name of the Excel worksheet that contains the data and the row number where the column header names are stored.
2. Create an import template by choosing Home > Templates > Import Templates. In the Import Templates window, use the Standard drop-down list to specify which prediction standard you want to associate with the template. The Available Fields area will display the properties used in the selected standard. (If you are creating a template from within the Import BOM Wizard, the standard type will already be selected.)
3. Click the Automatic Mapping icon. In the Automatic Mapping window, click the Browse button to search for the BOM file, and then use the information from Step 1 to populate the Worksheet and Column Header Row fields. If you are working with the example BOM file, choose Sheet1 and then enter 1 for the column header row, as shown in the following example. Click OK.

4. Lambda Predict will attempt to match the column header names found in the Excel file to the fields used in the selected prediction standard. The results will be displayed in the Selected Fields area. Any column header names that do not have a match will be displayed in red text with an Info icon next to it.For example, the following picture shows a template where the column name "location control number" is not recognized. To fix this, click and drag the corresponding field name from the Available Fields area (in this case, "LCN") and drop it on top of the unrecognized column name to create a match; otherwise, the data in the "location control number" column will be excluded from the import process.

5. Click Save or Save As to save your template so you can use it to import any BOM file that has the same structure.