Towards better BOM management


#1

Foreword: I’ve recently started to create proper BOMs for my KiCad projects (Part/order numbers for each part). For my last board, I did it manually (click on part in schematics, hit E, fill in fields, rinse-and-repeat), and it got tedious after 20 parts or so. This time, I was looking at doing this for 120 parts, which triggered a lust for automation :smile:

Initially I cooked up a simple python script which parses eeschema files but I soon realized it’s a bad idea for two-way editing.

Then I set on adding this feature to KiCad (thanks, opensource) and arrived at this:
http://faili.wot.lv/tmp/2016-01-03_22-11-20.jpg (can’t embed images, argh)

Current implementation uses an editable list/table for default fields + user specified fields (set in eeschema options) thus allowing to edit properties/fields for multiple components easily.

At the moment only single row can be edited at one but I plan on extending this to be able to select multiple rows and update cells for all of them at once.

For the adventurous among us, code for this can be found here: https://code.launchpad.net/~reinis-veips/kicad/eeschema-batch-properties

And now, the questions:

  1. Would anybody else be interested in something like this in mainline KiCad?
  2. Is there a better way to solve this problem? I don’t really like the prospects of creating my own library just to set the default field values for components from sources I prefer.
  3. Any ideas for improvements?

BoM creation -> CSV + Python
Edit component properties as a table
Kicost is amazing tool, but for me it didn't work!
Is there any way to massively change a footprint?
#2

Please see this wishlist bug I opened: https://bugs.launchpad.net/kicad/+bug/1521430
My idea was to have CSV export/import in Eeschema as dedicated spreadsheet editors can do the rest.
But having a built-in spreadsheet-like component properties editor would be great too. Just make sure you process, show and allow editing all fields in all components.

I’d suggest starting a discussion on KiCad developer’s mailing list if you’d like to get it into mainline.


#3

Hmm, using a dedicated spreadsheet would definitely allow far more flexiblity than is realistic to embed in KiCad.
I was only aware of this feature suggestion which is similar to what I wanted: https://blueprints.launchpad.net/kicad/+spec/multi-edit

Actually, adding CSV export and import might not be too hard (at least for the first 90% of functionality, haha)- the way I have implemented is actually similar (looking at values of columns and cells as in table).


#4

Well, whatever way you select to go forward (export/import or built-in), it will be highly appreciated by community as such functionality would simplify design flow and minimize human errors. My personal preference is CSV export/import :wink:


#5

Rather than edit EESchema files directly (which is tedious, as you note), what I did was to create a master parts list spreadsheet, where each part I use (or would consider using) has an entry. Each entry is keyed by a private part number. That part number matches what is in a custom PN field in each of the symbols in my schematic libraries. Included in this master parts list are manufacturers, manufacturer part numbers, # of the parts I have on hand, approximate cost per part, the name of the representing symbol and the library in which the symbol lives. The spreadsheet is maintained in Apple’s Numbers format; when I go to use it I export a CSV version.

I use the standard Kicad “bom2csv.xsl” processor to generate a CSV-format BOM. This file has fields for ref-des, value, footprint, data sheet, and my PN, because those are the fields defined for each symbol. I wrote a Python script to read in the master parts list CSV file and the project BOM CSV file and spit out a nicely formatted BOM with my part number, part count, vendor part number, vendor name, refdes list, and running cost total.

The reason for the custom part number field in the symbol, rather than adding manufacturer and manufacturer part number fields, is to try to not pollute the schematic with stuff that might be more easily handled externally. Plus many parts have multiple sources with different part numbers (Panasonic vs Vishay resistors, for example).

The parts list, the script, and example raw and final BOMs are available upon request.


#6

Seems like your tool would be able to do what KiCost is currently lacking… a simple way to get the manufacturer ID etc into KiCAD.
You might want to try to contact @devbisme and see if you two can go along and collaborate: Latest version of KiCost: Faster! Custom Pricing!

As for ideas for improvement… the important thing I’m interested in would be to make it work on local libraries too. Not just in eeschema, but also in the schematic library editor/browser.
If I am to put in this kind of data I would want to have it editable conveniently in the libraries, I wouldn’t want to fill these fields in via KiCAD means in the lib editor even as it’s just too much clicking and error prone.


#7

I think if we had a generic Python module for writing info from CSV and/or XLSX files back into SCH and LIB files, then everybody would be able to make convenient front-ends for updating the schematics.


#8

Here is something I did:

Scenario:

  • Export the BOM from EEschema as .csv
  • Edit it in a spreadsheet program (Excel, Calc) or as text. You can add columns (Manufacturer, Voltage specs, …)
  • Save it back as .tsv/.csv (tab separated values, this is the easiest export of spreadsheet programs)
  • Run ./tsv2sch.py exported_file.tsv kicad_sheet1.sch kicad_sheet2.sch to import the changes back into KiCad.

Faster way to update all your project sheets: ./tsv2sch.py exported_file.tsv project/*.sch
You can customize some of the script (columns to ignore for instance).


#9

That looks like a good, running start on what’s needed! I can use some of that in KiCost to update schematic fields as @Joan_Sparky requested. Thanks for posting it.


#10

@Andy_P master spreadsheet sounds like a neat way to deal with reusing parts for different projects. I’m going to give that one a try. However, I think that symbols/libraries should contain generic components and not my custom PNs- exactly for the reason you mention later (multiple sources for jellybean parts). IMO, what can be specified in a single source (schematic) should be saved there.
I might even argue that pcb layout is more appropriate place for this (for example SMD and TH layout versions for the same schematics) but pcbnew doesn’t have much support for module fields.

@devbisme I heard KiCad development team has plans for python scripting in eeschema, just like it is for pcbnew. And a new format for eeschema files (for better or for worse).

@caer your way is also interesting (and already working, so I don’t need to finish mine, haha).

I actually remembered one more benefit if this management is done inside KiCad- double-clicking a row can focus on component (just like it happens on pcbnew). This would save a mental context-switch when doing layout (especially if working on multiple monitors).


#11

[quote=“reinis, post:10, topic:1979”]
However, I think that symbols/libraries should contain generic components and not my custom PNs- exactly for the reason you mention later (multiple sources for jellybean parts).[/quote]

That’s the beauty, I think, of the “company part number.” You never have to go back and change the schematic if you choose an alternate supplier for a part.

(If you read here, you’ll see the philosophical disagreements over whether parts on a schematic should be generic, that is, op-amps called out as simply dual-op-amp and transistors given as simply npn, versus being more specific, such as calling out a TL072 or an NE5532 dual op-amp or an MMBT2N2222 transistor.)

Understood, but I suppose that what one wants to make visible on the schematic (especially when printed) can obscure the schematic itself. It is enough to have a part OPA1612AID called out on the schematic so the technician can follow it, and then the company part number (which can be hidden on the schematic) can be used by the purchasing people. (that’s what we do on the day job).

I know many people make that argument, but I am not sure how often in a professional environment one will switch from THT to SMD. (“what about prototyping?” Answer: PCB prototypes are inexpensive!) But Kicad allows people to choose their preferred workflow.

[quote=“reinis, post:10, topic:1979”]
I heard KiCad development team has plans for python scripting in eeschema, just like it is for pcbnew. And a new format for eeschema files (for better or for worse).[/quote]

Yes, scripting is coming, and the reason for the format change is to make it work more like pcbnew. Instead of the schematic library mechanism they have now, they want to implement a library tables scheme, and also embed all placed symbols into the schematic, rather than require that the libraries always be available.


#12

I’ve written two python scripts that read from csv file and fill the schematic fields. They are in the kicad-utils repo: https://github.com/KiCad/kicad-library-utils/tree/master/sch. Unfortunately, there is no proper documentation how to use them, but passing --help can give some hints.


#13

Hmm, now I am trying to create a BOM I remember how time consuming it is even for a small project. So naturally I am writing yet another BOM manager :smile:

Extracting data from the Kicad files is quite easy, thanks to the convenient text format. The S-expression format is quite nice, so I look forward to eeschema getting the same treatment.

There are obviously several views on how and where to store the data, I am quite agnostic on that. I tend to use different methods depending on case, so I would try to make it flexible. However it is done, it would be really nice to have a “one click to order” button. Live pricing is also a pretty neat idea.

Getting the data could be a hard bit, given the sheer number of SKUs. I’d also look to connect up with footprints and 3d models if possible. Octopart have a good set of part data, and a “free” APi (several restrictions). Snapeda have a lot of footprint data for various tools including Kicad, with a Freemium service. $99 per month for the advanced service is out of my price range, but I might be able to make use of the basic service.

Ultimately it would be nice to have the functionality in Kicad, but I’d be fine with a standalone tool.


#14

@Ricardo_Crudo, thanks for pointing me to your routines. The Schematic object makes it relatively easy to update a schematic from the XLSX file generated by KiCost.


#15

This is interesting. I am the author of PartsBox.io, which is a tool that helps you keep track of your parts. I’ve been thinking about KiCad integration for a while now, as I use KiCad myself. I don’t have clearly defined goals for that integration, though. What you are describing is similar to some of my notes.

Is your goal to import additional data into KiCad, or to export a “proper BOM”?

I’ve been planning on introducing “Projects” (BOMs) into PartsBox, into which you could import KiCad data (not sure what form yet) and maintain a mapping from KiCad components to PartsBox parts. That would give you a way to export a BOM with manufacturer part numbers, datasheet links, and also distributor/sourcing information (soon, after I get around to adding that to PartsBox). However, it would not import anything into KiCad.

The other idea was to use KiCad to store mapping information, but the maintenance cost might be too high for me. I guess I could expose an API that people could use to write such plugins. But I’m not sure if this is the right way to go: I have a feeling that managing your parts inventory and choosing suppliers do not necessarily belong in KiCad itself.

Any suggestions are welcome.


#16

I signed up as a beta user some time ago. I haven’t done much with it, I’m afraid.

My goal – others obviously have different workflows – is to take a “parts list” exported from Kicad and import it into something which will generate a proper BOM. I don’t see the point of polluting the schematic with extra pricing and other data.

[quote=“jwr, post:15, topic:1979”]
I’ve been planning on introducing “Projects” (BOMs) into PartsBox, into which you could import KiCad data (not sure what form yet) and maintain a mapping from KiCad components to PartsBox parts. That would give you a way to export a BOM with manufacturer part numbers, datasheet links, and also distributor/sourcing information (soon, after I get around to adding that to PartsBox). However, it would not import anything into KiCad.[/quote]

That sounds great. The missing link is a “company part number.” That’s the link between the components on the schematic and the components maintained in the database. The database will have manufacturer, manufacturer part number, cost, data sheet links, etc.

The only things the board parts list (as generated from EESchema) really needs to have, for each component in the design, are:

a) “company” part number, which is the key into the data base,
b) reference designator
c) part value (vital for passives like resistors, less so for op-amps and FPGAs)

The BOM generator (outside of Kicad) takes those three things in and looks them up in the parts database, and spits out a BOM with a line for each distinct component:

a) Company part number
b) Quantity
c) Manufacturer
d) Manufacturer part number
e) List of reference designators

Things can get a little more interesting when you consider that you might need to order parts for a “production run” (defined as “building more than one article”). This is because of price breaks per quantity. So you might wish to add a “Number of Boards to Build” option, which is a simple multiplier of all the individual parts quantities. Then the BOM processor can look up prices with the correct price breaks and add them to the final BOM against which parts are ordered.

A second complication is that the BOM processor should take into account quantity of parts on hand, so that the “ordering” BOM doesn’t include parts you already have.


#17

Caer, wonderful work! I had to tinker with this for awhile before getting it to work; seems it will accept .tsv files with tab delimiters, but only if each entry is not enclosed in quotes. Used WinMerge to find it does some interesting things:

  1. Blank entries ("") it replaces with ("~"), which seems like a good idea.
  2. It won’t run if ‘Reference’ is enclosed in double quote chars. This eliminates double-quoting all fields. (Shouldn’t be an issue if using tab delimiters, but could be problematic with comma delimiters, as some part numbers do contain commas.)
    EDIT/
  3. Nevermind, I entered backslashes instead of forwards-slashes. My bad! /EDIT

4, On a component with many of the other fields missing, there appears to be no order in which they are added. Or when rearranging the spreadsheet columns, the new order isn’t necessarily what happens. It would be awesome if it wrote all of the fields in the same order as the .tsv file.

This was on Win7 x64, Python 2.7.11, KiCad BZR 6425. Awesome job!


#18

His is so weird. 2 month ago I asked for exactly a script like this (thread) and now it turns out that there are even more than one :smiley:
The last month i tried to write a script for this (asks user which fields should be updated, reads the .sch, looks for #comp, searchs in .csv and replaces values/writes new)… i never done this before and the script is now 300 lines long … and now its almost useless :smiley: :frowning:


#19

Can I please get a copy of the script, parts list and example raw/final BoMs? I’d like to try it out. Thank you


#20

Margaret, if you are using Windows,

  1. Download the WinPython installer for Python 2.x.x (32-bit, MUST be 2.x.x version!!! Does not play well with 3.x.x versions): https://www.python.org/downloads/
  2. Run the installer. In addition to installing Python proper, it will also install pip and easy_install (under \Python2x\scripts) automatically.
  3. After the install has finished, open a command window and run the command ‘easy_install kicost’ (prefered) or ‘pip install kicost’. That will install kicost and any dependencies it has (into \python2x\scripts).
  4. Now you should be able to run kicost in a command window using the command ‘kicost -i file.xml’ or ‘python -m kicost -i file.xml’ without quotes.
    If you get the message [“easy_install” or “pip” command not found], then you have to add \python2x\scripts to the path.