Have you considered using InvenTree, PartsBox, PartKeeper or similar? I have no experience with them since we have developed our own PLM, but I would look at the available PLM options.
I am curious - what is the Master Part list?
Is it your list of approved components? Or is it a list of PNs that reference to approved components?
Or something else? I’ve never heard of this term before. I am generally use our PLM to create BOMs and it just uses internal Part IDs to get the real PNs for the fabricator.
A master parts list entry might look like this:
Description: Resistor, thin film, 100k ohms, 1%, 0.10W, 25ppm, SMD 0603
Vendor: DigiKey
Vendor P/N: 13-RT0603FRD07100KLTR-ND
Manufacturer: Yageo
Manufacturer P/N: RT0603FRD07100KL
Where the schematic shows the component value as ‘100k, 1%’ the database manager would link the component value to the database entry, and create a composite entry in the output BOM showing all of this information.
I was hoping for something more integrated into KiCad . . . but I’m still searching for an optimum solution.
Well, the database functionallity added to kicad in v7 opened the door to really customizing your own work system. I set up a sqlite db and it has been great. You need to do a bit of work to get there.
I also tweaked a bom script so I could access my fields as I wanted and create a bom to my liking, to generate this for example:
Here is my script if you wish to tweak it to your own needs (I just hacked on one of the kicad-provided scripts to customize it):
bom_csv_gil_script.zip (8.7 KB)
Hi TTYguy, this appears to be what I am looking for. I’m still not clear where the database functionality was added to KiCAD. Version 8 is my first experience with it. I don’t see any indications of integral database support, unless you are referring to some scripting.
I’m not a ‘scripting guy’, so while I grasp the concept, I don’t know where to begin in implementing it. I will have to do some experimenting . . . while my client waits (patiently, I hope) for a usable BOM. Thanks!
A.
See:
Kicad does not have “integral” database anything, as everyone wants something different. I used sqlite since the entire db is just one file. No sever process or anything complicated. Above, a post from eelik linked to my db case study. It takes time to set up but is awesome once it is running.
Then, using the fields you have defined in the db, you need a custom BOM script to extract what you need, and order the columns to print the way you want.
Or take a look at KiVar . . . works very well for me.
Having thought about it what I really need is the ability to generate a ‘good’ BOM directly without manual intervention. Then I could do as Piotr suggested and have a schematic for each build version (which I’d really like). I couldn’t however get Piotrs process to work I’m sorry to say.
I decided to describe as detailed as I can. I will bookmark this for myself to may be link if someone in future will ask the same questions.
I used it with Protel may be from 2006. Those time it was simpler as in Protel I fully identified the part by its name. Moving in 2017 to KiCad I decided that I identify by name+footprint. Before designing my libraries according this assumption I have checked if I can do it in LibreOffice and found I can. So in my KiCad version formulas are little more complicated than they were in Protel version.
As in LibreOffice language selection is more complicated than in KiCad (for user interface, regional settings, currency settings, date formats, documents language) and may be (not sure) changing needs the net connection (to download needed dictionaries and…) while I was preparing my example at PC not connected to net I decided to left everything as is just believing that spreadsheet opened in LibreOffice (or OpenOffice) configured to other language will just show everything in that language.
I downloaded zip from those my post to have the same what you can have.
The key to realize the whole task is one formula you see at the top:
And I believe shown bigger:
I don’t know how it exactly looks opened in another language LibreOffice.
It says:
- search.vertically(concatenate.texts …
It says spreadsheet to:
- concatenate texts from $C6 and $D6 so making one texts having Value and Footprint,
- then use this text to search in tab named Elem (it is tab with all elements ‘database’) in the rectangle from $C$1 to $E$51. This have to contain all database so 51 will be much bigger number, but spreadsheet works that way that if you insert new rows at Elem tab this number in formulas are automatically modified.
- search is done in column 0 of this rectangle region (last formula parameter says this),
- when text is found than text from column 2 of rectangle region is then used as the content of this field.
$s used in formula allows it to be copied into following rows and still referring to the same rectangle (without them formula when copied from row 6 into 7 will be also shifting those numbers what we don’t want).
Now look at Elem tab:
Formula you see says: concatenate.texts so in column C I have name (from column A) concatenated with footprint (from column B). Here there are no $ in fields identyfication as when I copy this formula to next row I want it to use also values from next row and not always from this one row.
So when in search formula the rectangle is specified to be from C to E than column 0 is here column C containing concatenated name and footprint that is exactly the same as we use to search.
Now lets get back to Bom tab:
To the right from H column are the texts taken directly from KiCad BOM in csv file.
The formula I used is to take from footprint given me by KiCad only the footprint name without library name. I used the fact that all my footprint libraries are single letter name. If they are not you have to find the other way of selecting only what you need or in Elem tab use footprint names containing also library names. I am not very proficient in spreadsheet functions but believe there should be the some way to select text after ‘:’. It could need some experiments and may be even a hour or more of time.
Having in BOM needed fields on the left and copied from KiCad on the right allow to adopt spreadsheet when something from KiCad changes.
In KiCad V4/V5 I have used modified by me (with forum help) Python script generating BOMs to get it like I want it (only fields needed and footprint without library). But with KiCad V6 it was not up to date and instead of modifying it (I don’t know Python) I decided to make what is needed in spreadsheet (so this footprint with library name - it was not here in previous version).
With V8 I have first modified it assuming that I will set BOM columns in KiCad in order I want and then decided to use default column order in KiCad to just not have to set it for each BOM generation and needed selection done in spreadsheet (just in Bom tab correct columns from right copied to columns on left).
When you copy data from KiCad you have them on the right, but you have nothing on the left. You have to select one row (not whole, but only part on the left) and copy it into rows under it. You do it by dragging the small square on the bottom right of rectangle showing selected cells. When you do it everything is filled accordingly.
Column F is filled the same way as column E with numbers (from Elem tab) used then to sort BOM. This I do manually but it is few second task.
You select row set and from menu use function Data-Sort selecting first column to be sort by as F and second as C.
Now you have the BOM on the left in Bom tab.
The only what left is to copy it and paste into another spreadsheet but in ‘only text’ mode as you need texts and not formulas.
May be it looks long. But in use:
- copy BOM in KiCad (in V8 I don’t use csv file),
- paste into BOM tab,
- select one row on the left and copy it down,
- select all rows and use Data-Sort,
- copy into other spreadsheet.
Numbering rows I have done in that ‘other’ spreadsheet.
If all elements used are in Elem tab it takes less than a minute. But typically at that moment I find that I have used some new element I have defined in KiCad and not have it in Elem tab and spend time on searching at all distributors what to write here with what alternative parts.
Hope this helps and hope someone will use it 
as for me it is all I need. We use external assembly houses since 2004 and I use my spreadsheet to generate BOMs we send them.
2 Likes
Thanks Piotr. I had some time yesterday afternoon and looked again into it and figured out how it works. While I cant complete the process as you describe (I think my version of excel is probably different) I think can probably craft something using the same idea. Its all about using concatenate to create a search term and vlookup to find it.
Thanks for the lead.
David
My schematics always contained full info about components that are not generic or have multiple approved options.
As far as I understand you want either a DB library that will contain all the needed information or you want some linking to PLM type system(or some BOM management system).
What is you latest workflow?
OrCad always supported DB libs, that’s true(at least since I’ve started using it in 2010).
Entering all of the information in that manner seems a bit unwieldy. Your symbol library would be quite large with this technique, would it not?
Even the old DOS OrCAD supported the use of an ‘include file’. The BOM generator would search the include file for a key that matched a component value in the schematic, and it it found one, it would link the rest of the information from that entry.
Does it matter where the information lies? It still has to go somewhere. You can take a look at what Altium tries to achieve with integration of Octopart. However, it still stores that information somewhere.
Database based library is relatively standard way of solving this problem. Reason for this is that big companies have other tools to mange that library (some kind of PLM, EDM or other fancy thing like SAP).
What was your workflow before? Did you have a tool to manage all this before coming to KiCAD? What are your expectations and what are you trying to solve for?
I had been using OrCAD V17.2, which supported the use of an ‘include file’, a database of components that included approved manufacturers & part numbers, vendors, etc. Generating a BOM that linked to this database took no more effort than clicking a box and pointing to the file. The resulting BOM had all information for each part in the schematic.
Well, if you are used to OrCAD CIS with an ODBC db driver, than you can probably use the same DB with KC.
As far as I remember .inc file is actually a plain text file, not a DB. And it’s OrCad capture specific function, if I’m not mistaken. I am sure that neither Altium nor Mentor Graphics support this.
So if you want the merge function from Orcad, it’s actually trivial to achieve with python. I’ve been doing such tricks before there was KiBom and DB support.
Using DB library is a way better and more standard way of managing approved components, as you can have proper lifecicle management attached to it.
(Alt Azi is Telescope talk… I code/write and Design/Build Telescope DSC’s and App’s…)
I’ve read through all of the above posts (my head is spinning!) and, I have Comment and Question’s.
My Long-Winded-Response…
But, first, to help you answer the question’s in a clear and useful way for my simple-minded thinking, know that I’ve authored many DataBases’s (SQL, SQLite, Access, Base, Java… the list goes on…) and have written Database interface GUI’s… Thus, I know something about the number of hour’s needed for producing a Bugless App/Interface.
I’m sure other’s reading this post also have plenty of experience…
Once upon a time, I was a Debugging consultant to Oracle and Microsoft.
It all comes down to your clearly stating what you want…
For example: You use the word Database and also stated that TTG’s CSV is exactlty what you want. A CSV is NOT a Database (in the sense of the ‘Database’ meaning). Most Text-Document programs that create Spreadsheet’s can also Open/Import some Database’s.
Question #1: Is what you want to create and/or get data from a, Spreadsheet or a Database? Syntax/Format is very important and a progammer must know the Type of Database.
Question #2: Does it matter to you if Spreadsheet or Database? If it does Not matter to you, then Spreadsheet is the way to go and makes the ‘Free, donated time/work’ more doable.
Question #3: More of a Request than a Question… Post a Sample of what you call your Database (post the file or snippit of it…)
Question #4: What are you trying to do with respect to Kicad?
- Have the Schematic and/or PCB grab parts from the so-called Database by Name, Part-Number…etc ?
- Auto ‘Magically’ generate a BOM from some file? You’ve got to tie/relate to some related parts/names/numbers to something you’ve done in Kicad ?
I wouldn’t touch this without specific info. Below is an example of a Kicad BOM Plugin/App I wrote that might give you idea of what you really want… But, even that Does require a user to enter info into Kicad’s Schematic and/or PCB…
Lastly, though I understand the BOM generator’s are going away/changing, curently you may be able to still use the Schematic’s BOM script. I still use v7 and won’t upgrade to v8 for another 6+months…
The v7 BOM script shown below enables User to Add Fields. And, if using some software, such as LibreOffice’s CalC (their Spreadsheet program) you can make a Watch-Me-Do that can replay/prompt/etc and can add code… last screenshot shows my BOM-It macro…
At this point and with some free time, I’m Amusing myself… just to show the usefulness of doing it with .CSV
Let’s assume there are a variety of similar items in the CSV and a Schematic has a particular item, such as the 100k, 1% (I’ll just use that and I tweaked, copied, pasted. Thus, there are different items and the only difference is the R value). Therefore will search for that item and grab the rest of it’s info and print it’s full data… Could create a BOM and add it to the BOM… you get the idea…