Design Office
Import BoM Requirements
You can save a lot of time by using this very simple routine for importing your Assembly Definitions and Bills of Materials from an Excel® (XLS) spreadsheet in the format generated by the OrCad® CAD package. You can, of course, use any Excel spreadsheet which conforms strictly to this layout, which is defined below. Obviously you will need to know exactly where to find this file on your PC, and it is strongly recommended that you save it (or copy it) to your \merwin\import (Standard/Server Edition) or \merwinT\import directory (WorkStation Edition).
OrCad will name this spreadsheet file with your assembly number and an XLS extension. The example assembly number is XK400 and the file name is XK400.XLS. If you are using an XLS file from another source, it is recommended you follow this convention.
If you are using a different CAD package, you can forward us a sample output file (as an e-mail attachment) and we will look at incorporating an import routine to match the format.
Above is a screenshot of the OrCad Bill of Materials export dialog, showing the correct settings. (Please consult your OrCad User Guide for more information about this feature.)
From this dialog, the OrCad package generates a spreadsheet with four columns headed A, B, C and D. These columns should contain the following data:-
A is called "Item" and contains the sequence number for each item in the list.
B is called "Quantity" and contains the quantity of the line item in a single assembly, and has a maximum width of 8 digits.
C is called "Reference" and contains the circuit board reference(s) for the line item, and has a maximum width of 30 characters.
D is called "Part" and contains the part number of the line item, which must not exceed 15 characters.If for any items in the list there is more data for the C column than can be fitted within its width, addition rows are generated for these line items. Where such additional rows are generated, the fields in columns A, B, and D must be blank in these rows.
An example of how this spreadsheet should look is shown below.
It cannot be stressed enough that you are responsible for ensuring that you are attempting to import appropriate data. It is most important that you visually check the conformity of your spreadsheet before you attempt to run the Merlin Import BoM routine. Plainly, if the data you import is inappropriate, your new BoM will be useless.
Given that you have a suitable spreadsheet as described above, Merlin will apply a strict validation sequence before attempting the import, and if any of these tests is failed, the import will be rejected:-
- The assembly must be present on the Resources File (i.e. it must have its own Resource Record Card).
- The BoM must NOT already exist, nor the assembly be already defined, within Merlin Assembly Library.
- All materials listed in the BoM must be present on the Resources File (i.e. they must each have their own Resource Record Card).
If tests 1 or 2 fail, you will be informed by a message box, and the import will be aborted. If test 3 is failed you will be presented with a list of the items which are absent from the Resources File. You can print this list so that you can make the necessary entries.
Additionally, no duplicate part numbers should exist in the incoming list. However, if this rule is broken the import will still succeed, but without the duplicate entries. The details of any duplicates found will be reported to you (with a printable list) so that you can take the appropriate action, which is likely to be the amendment of the entries in question (in BoM Editor) to take into account the data contained in the duplicate (not imported) items.
Import BoM Procedure
Enter Import BoM from Design Office and you will see this screen.
The first step is to click the button 1: Select Import File. This will invoke a file picking dialog which will enable you to navigate to your saved import file. Highlight the file in the usual way, and click the Select button.
The screen should now look as shown above, with the import file path displayed and the file name prompted as the Assembly No. If you wish to change the assembly number you should do this now, then click 2: Confirm Assembly No. If this assembly number is not validated correctly as specified above, the import will be declined.
If the assembly is accepted, the screen will look as shown above, with the assembly name/description displayed to the right of the Assembly No. You can still change the assembly number at this point if you wish to.
When you are ready, click 3: Fetch Data & View.
The data you are proposing to import is brought into Merlin (but NOT yet formally imported) and displayed for your inspection in a grid on the Proposed BoM List tab.
It is crucially important that you check that each column contains the correct type of data! i.e., the Part No. column does indeed contain the part numbers of the materials on the BoM, and the Qty. column does contain the quantity of each part in the BoM. The notes column should contain the PCB information, which will be appended to the Item Notes for each BoM entry.
When you are entirely happy that you are proposing to import valid data, click
4: Import Data. If all of the material items listed in the BoM are found to be present on the Resources File, the data will be imported. The button legend will change to
5: Print Report, which will enable you to print a listing of the imported data if you wish.Please Note: If multiple entries for the same part no. are found on the import list, the import will succeed, but only the first entry for each part no. will be imported into the new BoM. A warning will be issued, and the additional instances of the part no(s). involved will be displayed in a list on the
Duplicate Items tab. This list can be printed, and the entries for these items in the new BoM should be checked and amended if necessary (as per next paragraph).The job is now done, and you should go to Assembly Library, check the assembly and BoM data for this assembly, and set a Build Time in days.
The import will fail if all the materials listed in the incoming BoM are not present on the Resources File, in which case you will see the screen shown below.
The grid on the Missing Resources tab will be activated, showing a list of the items which need to be added to the Resources File before this BoM can be imported into Merlin. Click 5: Print Report to print a hard-copy listing of these items.
A Record Card should be created on the Resources File for each of these missing items, and the Import BoM process can then be run again.
