Preprocessing a text file bill of materials for import

Normally, these types of preprocessing tasks are easily handled within PDXpert's built-in XML/XSLT data transformation process. (In fact, an example OrCAD importer is included in the default PDXpert configuration.) We've created this example only to show how to preprocess data if you need to go beyond PDXpert's built-in XML/XSLT capability.

PDXpert product lifecycle management software uses XML/XSLT to provide a very flexible means for importing bill of materials (BOM) files formatted as XML, CSV, Microsoft® Excel®, or other structured text. Most CAD applications can export a bill of materials directly, or by "printing" to a text file. Data is frequently exported in a convenient neutral format called "comma-separated values" (CSV).

In CSV files, each data record is contained on its own line of text, and data fields within the record are separated by commas. Data fields that could be incorrectly interpreted as a numeric value are enclosed in double quotes; this ensures that text values, such as a part number "00123", are not reformatted as the numeric value 123. In addition, fields that already contain a comma (such as a reference designator "R1, R7") must also be enclosed on quotes; other rules apply to data containing quotes.

But not all CAD applications can export a structured CSV file, and an intermediate step is sometimes required to format the "almost CSV" export to a true comma-delimited format.

We'll show a general approach to converting a Cadence® OrCAD® (or similar) bill of materials file into a standard CSV file.

What needs to be changed in the Bill of Materials export file?

After creating a schematic and layout in the OrCAD electronic design program, the design's parts list can be exported into a file for transfer to bill of materials software, such as a PLM or MRP system. However, the OrCAD BOM file also contains irrelevant information including title, page number, dates, header/detail separator line and empty lines.

We'll need to make some changes to the default OrCAD export settings to create an "almost CSV" file:

• Identify the four essential export data columns: row ("find") identifier, part number, quantity & reference designators
• Ensure that part numbers are treated as strings by enclosing them in quotes, e.g. "029801"
• Combine all the reference designators into a single field, also in quotes, e.g., "R1-R4, R7"

The resulting bill of materials export file, with a .bom file extension, looks like this:

Our goal is to have the file, prior to import into PDXpert, look like this:

DOS & gawk: Converting the BOM file to a CSV file

We'll use a batch file to perform 3 functions:

• Convert the input file to ANSI if it's currently in Unicode
• Initialize the CSV result file with the correct header information "Item, Number, Quantity, Reference"
• Transfer only the data rows from the BOM file to the CSV file

The first two steps are simple DOS commands, while the final step relies on the open-source gawk ("GNU awk" as ported to Windows) text processing utility.

To ensure we can parse the file, we first convert a possible Unicode text file into ANSI (ASCII) text using the TYPE command.

type sourceFile > workingFile

Separately, to ensure that we can match the data coming into PDXpert's structure markup list, we'll create a new CSV file (">") with the correct headers by piping an ECHO to the CSV file.

echo Item,Number,Quantity,Reference > resultFile

Our third step uses gawk to find all data rows from the BOM file and append (">>") each to the CSV file. Each data row begins with an item number, ≥1, that we'll be assigning as the PDXpert structure's FIND number.

gawk "/^[1-9]+[0-9]*/ { print $0 }" workingFile >> resultFile

We'll also specify some default filenames to handle running the batch from either the DOS command line or from Windows Explorer. The conversion batch file syntax is:

cleancsv input output

input: file with ordered columns Item,Number,Quantity,Reference

output: non-data rows removed

If input is not specified, then the batch looks for input.bom as input file. The output file will be named output.csv

If output is not specified, then output file will be same as input but with .csv extension

Using the bill of materials to CSV conversion batch file

To set up your tools:

1. Create a working folder where your conversions will occur. It should be on your local hard disk and accessible to the OrCAD export utility and to the importing application's importer.
2. Download and unzip the batch file, gawk utility and XSLT files into the working folder. You may want to place gawk into a Program Files folder, which is described in the ReadMe.txt file.
3. Set up your OrCAD export utility to create the "almost CSV" file.
4. If necessary, use the XSLT files to create a new member of the PDXpert Data transformation collection (see the PDXpert client help guide: Contents > How to use the Collection Explorer > Adding a new collection member). Set the Name to "Sync Generic BOM (csv)" and mark the Import/export structure checkbox as TRUE. Open the GenericImportXslt.txt file in Windows Notepad (not in a word processing application). Copy the entire contents (Ctrl+A, Ctrl+C), and paste it (Ctrl+V) into the new member's Import XSLT textbox. Close or lock the window to save your changes.

To convert your bill of materials text file into a CSV file:

1. Export the design BOM file from your CAD tool.
2. Run the batch file to obtain your well-formatted CSV file. If you run the batch file by double-clicking within Windows Explorer, the exported filename must be named input.bom (unless you edit the batch file - see below). When you run the batch file from Windows Explorer, the resulting file will be output.csv.

You can now import the file into your BOM management software. For example, to import the BOM file into a PDXpert assembly record:

1. Create the new assembly record (or create a new revision of an existing assembly).
2. From the assembly's Structure tab Markup list, open the context menu and select Import Markup from File
3. In the Import Markup from File dialog, select the Files of type: "CSV (Comma delimited)" and then Apply the "Sync Generic BOM (csv)" importer template.
4. Select your CSV file, and then click the Open button. The components in the file are imported and matched to the current item revisions.

Extending the BOM to CSV converter batch file to other applications

If you examine the CleanCSV.bat batch file, you'll see that this solution is quite generic. We look only at the data rows, and the requirements are that the data rows

• start with an integer (and non-data rows do not), and
• are already in proper CSV format.

The number of data columns to be imported, the column header names, and the default filenames can be easily changed in the first few lines of the batch file. If you make any changes to the header, you must ensure that the data rows have the same number of fields - and in the same order - as the header value you specify in the batch file. And, of course, the import specification of the importing application must be updated to accept the new header/data fields.

All irrelevant header & footer rows, including blank lines, will be ignored during batch file processing. Note that the tempfile will overwrite any other file of the same name within your working folder, and then will be deleted.

gawk: http://gnuwin32.sourceforge.net/packages/gawk.htm

This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.