Welcome to the CADSTARguys Blog - Information, hints, tips and my waffle on the CADSTAR Printed Circuit Board design suite.

Please note that all names used are completely fictitious and any thing written is my own personal opinion or knowledge and not related in any way to either my employers or their customers (or Zuken).
Also this is not a replacement for proper Maintenence/support and you should read the help files before asking anything techy:).

Monday, 13 February 2012

Using a BOM spreadsheet to quickly add parts into a CADSTAR design.

Recently a colleague has been spending his time reverse engineering an old design that someone only has Gerber's, schematic diagram as PDF and a parts list for so that it can be reworked, modified and bought up to current standards.

At best this is a time consuming task requiring a printout of the schematic, a search of the spreadsheet for a component, copy the part name then paste it into the slide out library search field and then left drag the found part into the schematic, hit escape once then select the symbol properties and change the component name to suit the schematic.  Then join the symbols up with connections etc.

For a schematic with hundreds of parts in I thought there has to be a better way to do this, and then I remembered an old method I had tried several years ago that enabled me to add 600+ parts into my schematic within 20 minutes.

What you need is a spreadsheet that contains the component names and part names that they use also this will only work if the source of the spreadsheet has the part names that match your CADSTAR parts library.
Having your parts library part names match your company database part names can be a good way of naming them anyway, however if you do not have them matching then you will need to do some more processing to get what you want. I can recall recreating my parts library, changing all the part names to match the company MRP system part numbers in a 2nd library allowing me to keep the old library for legacy designs. Or you can always maintain a CADSTAR part name field in your MRP system.

So how did I do it?

In essence the spreadsheet needs turning into a RINF netlist format text file that just contains components to be added into the design.

To start the file it needs some header information as follows:

Then we have the lines taken from the spreadsheet in the format of:
.ADD_COM Compname Partname Partname
The spaces and double part name must be included.

Then the file must have a footer as:

So what you want to end up with is:

.ADD_COM R1 Partname1 Partname1
.ADD_COM R2 Partname1 Partname1
.ADD_COM R3 Partname1 Partname1
.ADD_COM R4 Partname1 Partname1
.ADD_COM R5 Partname1 Partname1
.ADD_COM C1 Partname1 Partname1
.ADD_COM C2 Partname1 Partname1
Save as "filename.frs" and then you can import this into a CADSTAR schematic as a RINF format file.

This will place all the added symbols/parts in the corner in a matrix ready for you to then move them into place and add connections.

(Simply get into move mode [F2] and type the component name  and it will be on the end of the cursor, drop it down and type the next name and so on.)

This is all easy if you can obtain a spreadsheet that has a single row per component with compname and partname in 2 columns.
However, most parts lists have a single part per row with the comp names all comma separated in a single cell.
The problem now is to change them into a single row per component and include the partname on each row. To do this it involves a multi step process, the first step is to remove ALL header and footer information, columns that are not needed leaving ONLY the part name and component name columns and data.

I am using Microsoft Excel 2007 and your spreadsheet program/version may be different so you will have to find an equivalent routine.
Sort the columns so that part name is in column A and Component names are in column B.
Select the column that has all the component names in (B).
Use the Data tab and "Text to Columns".
Choose "Delimited" then Next
Choose only "Comma" then Next
Keep the format as "General" and then Finish.
This will then sort the rows so that each component name is in a cell of its own in the part name row.

What we need to do next is take each component name and put it on its own row with the partname it uses in the cell next to it. (All this is best done with the part name in column A).
In order to do this there is a macro that Jerry Beaucaire has written available on his Excel Assitant site at excelassistantSee Part 2, "Data in a delimited string".

Download his example spreadsheet at the top to obtain a working example of this.
Once this macro is in your spreadsheet all you need to do is hit ALT+F8 and run the ParseByColumn Macro.

This puts the data as per the above image.

Then we have a little more manipulation before we can finish this.
Select column A (part name) and cut it then paste it into column C, copy and paste it to column D.
In cell A1 enter .ADD_COM and by selecting the bottom right corner of the cell you can drag copy this down into all the other cells in that column.

Then save the spreadsheet.
Now use File\Save As and save it as an MS-DOS text file with no formatting.

You end up with the same format as at the top of this page:
.ADD_COM R1 Partname1 Partname1
.ADD_COM R2 Partname1 Partname1
.ADD_COM R3 Partname1 Partname1
.ADD_COM R4 Partname1 Partname1
.ADD_COM R5 Partname1 Partname1
.ADD_COM C1 Partname2 Partname2
.ADD_COM C2 Partname2 Partname2
and so on.

Open this in a text editor and add the header and footer lines and save as a .frs file ready to import.

The benefit of this method is that it is quicker than manually adding parts and more accurate as it prevents user error.

If only I had thought of this earlier he would not have needed to spend all that time doing it manually.
N.B. this format only works for starting a new design, I am trying to find out the format to update a design like this so will revisit this when I know more..


Anonymous said...

Just a point....
If the rinf file calls a part symbol that does not exist in the part library then it will fail and the part needs creating first.

The above description is assuming that the parts library is already up to date.

Anonymous said...

Just one question pops to mind. How are multiple gates of a symbol handled?

The CADSTAR Guy's Blog. said...

As far as I can recall you get all gates of the symbol bought into the design as expected.
Try it an see.

Post a Comment