Simple, flexible product lifecycle management software for growing companies™
Training videos ♦ System requirements ♦ Download PDXpert software ♦ How to renew/upgrade license ♦ Price list ♦ Ask us
Importing an OrCAD bill of materials into PDXpert PLM softwareNormally, these types of preprocessing tasks are easily handled within PDXpert's built-in XML/XSLT data transformation process. (In fact, an 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:
The resulting bill of materials export file, with a .bom file extension, looks like this: Revised: Friday, November 29, 2009 Revision: E00
Bill Of Materials November 29,2009 08:30:14 Page1
Item,Number,Quantity,Reference ______________________________________________
1,"32099",3,"R2,R3" 2,"30007",1,"C2" 3,"31005",2,"C1,C3" 4,"20001",1,"Q1" 5,"32003",1,"R1" Our goal is to have the file, prior to import into PDXpert, look like this: Item,Number,Quantity,Reference 1,"32099",3,"R2,R3" 2,"30007",1,"C2" 3,"31005",2,"C1,C3" 4,"20001",1,"Q1" 5,"32003",1,"R1" DOS & gawk: Converting the BOM file to a CSV fileWe'll use a batch file to perform 3 functions:
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 fileTo set up your tools:
To convert your bill of materials text file into a CSV file:
You can now import the file into your BOM management software. For example, to import the BOM file into a PDXpert assembly record:
Extending the BOM to CSV converter batch file to other applicationsIf 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
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. set header=Item,Number,Quantity,Reference set infile=input.bom set outfile=output.csv set tempfile=a7s9sk2q.tmp 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. |
ResourcesShare This Page
|
Copyright © 2004-2010 by Active Sensing, Inc. All rights reserved.