BOM grouped by reference


#1

Hi,
I was searching for a way to group reference designators for identical parts (same footprint and value) in my BOM. I didn’t found a solution so I extended the bom2csv.xsl file to bom2groupedCsv.xsl:

<!--XSL style sheet to convert EESCHEMA XML Partlist Format to grouped CSV BOM Format
    Copyright (C) 2014, Wolf Walter.
    Copyright (C) 2013, Stefan Helmert.
    GPL v2.

	Functionality:
		Generation of Digi-Key ordering system compatible BOM 
		
    How to use this is explained in eeschema.pdf chapter 14.  You enter a command line into the
    netlist exporter using a new (custom) tab in the netlist export dialog.  The command is
        on Windows:
            xsltproc -o "%O.csv" "pathToFile\bom2groupedCsv.xsl" "%I"
        on Linux:
            xsltproc -o %O.csv pathToFile/bom2groupedCsv.xsl %I
-->

<!DOCTYPE xsl:stylesheet [
  <!ENTITY nl  "&#xd;&#xa;">    <!--new line CR, LF, or LF, your choice -->
]>


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:output method="text"/>

	<!-- for Muenchian grouping of footprint and value combination -->
	<xsl:key name="partTypeByValueAndFootprint" match="comp" use="concat(footprint, '-', value)" />
	
	<!-- for table head and empty table fields-->
	<xsl:key name="headentr" match="field" use="@name"/>
	
	    <!-- main part -->
	<xsl:template match="/export">
	    <xsl:text>Reference, Quantity, Value, Footprint, Datasheet</xsl:text>

	    <!-- find all existing table head entries and list each one once -->
	    <xsl:for-each select="components/comp/fields/field[generate-id(.) = generate-id(key('headentr',@name)[1])]">
		<xsl:text>, </xsl:text>
		<xsl:value-of select="@name"/>
	    </xsl:for-each>

	    <!-- all table entries -->
	    <xsl:apply-templates select="components"/>
	</xsl:template>
	
	<xsl:template match="components">
	    <!-- for Muenchian grouping of footprint and value combination -->
	    <xsl:for-each select="comp[count(. | key('partTypeByValueAndFootprint', concat(footprint, '-', value))[1]) = 1]">
		<xsl:sort select="@ref" />
		<xsl:text>&nl;</xsl:text>
		<!-- list of all references -->
		<xsl:for-each select="key('partTypeByValueAndFootprint', concat(footprint, '-', value))">
			<xsl:sort select="@ref" />
			<xsl:value-of select="@ref"/><xsl:text> </xsl:text>
		</xsl:for-each><xsl:text>,</xsl:text>
		<!-- quantity of parts with same footprint and value -->
		<xsl:value-of select="count(key('partTypeByValueAndFootprint', concat(footprint, '-', value)))"/><xsl:text>,</xsl:text>
		<xsl:value-of select="value"/><xsl:text>,</xsl:text>
		<xsl:value-of select="footprint"/><xsl:text>,</xsl:text>
		<xsl:value-of select="datasheet"/>
		<xsl:apply-templates select="fields"/>
	    </xsl:for-each>
	</xsl:template>
	
	 <!-- table entries with dynamic table head -->
	<xsl:template match="fields">

	    <!-- remember current fields section -->
	    <xsl:variable name="fieldvar" select="field"/>

	    <!-- for all existing head entries -->
	    <xsl:for-each select="/export/components/comp/fields/field[generate-id(.) = generate-id(key('headentr',@name)[1])]">
		<xsl:variable name="allnames" select="@name"/>
		<xsl:text>,</xsl:text>

		<!-- for all field entries in the remembered fields section -->
		<xsl:for-each select="$fieldvar">

		    <!-- only if this field entry exists in this fields section -->
		    <xsl:if test="@name=$allnames">
			<!-- content of the field -->
			<xsl:value-of select="."/>
		    </xsl:if>
		    <!--
			If it does not exist, use an empty cell in output for this row.
			Every non-blank entry is assigned to its proper column.
		    -->
		</xsl:for-each>
	    </xsl:for-each>
	</xsl:template>
    
 </xsl:stylesheet>

Maybe somebody finds it useful too, so i decided to share it.


#2

Just tested it: works as expected!
Many hanks :slight_smile:


#3

The best place I’ve found to generate a BOM in KiCad is in pcbnew. It’s well hidden, but it doesn’t give any of the “we’ll implement the BOM functions in an XML file (maybe)” that you see in the schematic editor. Go to File -> Fabrication Outputs -> BOM File, and then it will give you options to save the BOM as a CSV. This file groups reference designations with the same footprint and value, which is pretty much exactly what you want for a BOM generator.


#4

Very well hidden indeed! I didn’t know it was there, thank you for the tip of the day :wink:


#5

@mbarlow, do you know by any chance how the field “Supplier and ref” from generated BOM gets populated? It is obviously empty in the csv file, where does this data gets entered?


#6

Many months later I found this thread via Google (go figure). Many thanks for the help.


#7

I am a windows user and it is a pain in the *** to install this. So I would like to share my setup to make it easy for others.
I gathered all files needed and put them in a single folder.
All you have to do is download KicadBOM.zip (1.2 MB), unpack, place the folder on your harddrive and add the path to the PATH variable in windows.

Read the readme.txt for more info.

This includes the standard BOM script and the one from this post.


BOM generation in "new" Kicad (Windows)
#8

Ah yes, I forgot about the build on Windows.

To be honest, I found this change to be a bit annoying. I get that some people want control over their BOM output, but why not have some sensible defaults, you know?


Kicost is amazing tool, but for me it didn't work!
#9

Totally agree, the bom option in the first versions were ok with me.
Or at least they should have included all files necessary to make a basic BOM.


#10

Thanks fritsjan for the .zip file and others for working on this. The latest builds of KiCAD were not liking Saxon for some reason, so I tried this. One note is that it is seeming to group together components with same values, regardless of other differences. Such as two “560uF” caps - if one is 400v and the other 6v, they are still grouped together with data from just one.

What KiCad desperately needs is a Metadata Manager, like a spreadsheet mode, where all of the schematic’s metadata can be seen and modified instantly. On bigger boards for production, metadata management becomes a time-consuming nightmare.


#11

If nothing else works, the grouped BOM can still be easily generated from pcbnew without any xml wizardry (File > Fabrication Outputs > BOM File). But you need to create the board first, obviously.


#12

Hello,

I have just finished Tutorial on KiCad.
Successfully created a schematic, PCB, 3D view and modified them
to get the feel.

I was stuck at BOM generation in schematic.
Here

Helped a lot.
Thanks.

I will seriously use and pursue KiCad usage (Under Ubuntu at this stage).

Regards,
Vinay


#13

After following instructions in readme file this worked first time! bom2groupedCsv.xsl gave me a csv file in my Kicad directory. This is exactly what I wanted with grouped components e.g.
R2 R4 2 2.7K

( I was not sure to add the KicadBOM path to the User Path or the System Path - I added it to the System Path )


#14

Here is a Windows program to extract BOMs from KiCAD schematic, https://github.com/akouz/KiCadBOM/releases It instantly shows extracted BOM in a GUI and then allows to save it as a CSV file.


#15

Hi akouz, thank you very much for sharing your SW it is very helpful!! :slight_smile:
How it works the “Stock” tab? it seems that is always empty


#16

I still get this error with the .zip solution provided:

Command error. Return code 11

Error messages:
I/O error : Invalid argument
I/O error : Invalid argument

I’m guessing there’s an invalid argument in both input and output file. My project file path has spaces in it. I’ve tried escaping them with %20, then it makes folders called for instance Google%20Drive and complains there’s no files in there.


#17

This was as close to perfect as I could get it, simple and nice. Only too bad it looks for specific strings and not just lists parameters in order? Cause datasheet and MPN will not show


#18

Hi Wolf,

Thanks very much for your bom2groupedCsv.xsl . I found it works like a charm with 8.0.2-stable, including that it sorts the lines according to reference, and includes columns for all the fields I added to each component. I wrote it up as part of my initial attempt at designing these fields, and creating the grouped, sorted. BOM I wanted:

http://www.firstpr.com.au/kicad/kicad-data-structures/#group

Cheers

Robin


#19

Could someone help me modifying bom2groupedCsv.xsl so it would group components by a field named “Partnumber”?


#20

Hi,

thank you for your good work with bom2groupedCsv.xsl Wolf.
I am but playing around with kicad, so I found the time to adapt it a little to my “needs”:slight_smile:.
So I thought, that breaking up the Reference field into multiple lines and also adding some parameters for influencing the generated CSV file would be nice. And so I added the following parameters:

  • refcount - determines the maximum count of references per line (default 10).
  • d - the CSV field delimiter character (default “,”).
  • s - the delimiter character for the references in the “Reference” field (default “,”).
  • q - the CSV quote character (one of [’"]) (default ‘"’").

To change them, you can edit the xsl file or add the changed parameter to the command line of the BOM wizard.
As an example, to change the CSV field delimiter to a semicolon ‘;’, you can invoke xsltproc as follows:

xsltproc --stringparam d ";" -o ... (the rest of the command line is unchanged).

Here is the code:

<!--XSL style sheet to convert EESCHEMA XML Partlist Format to grouped CSV BOM Format
    Copyright (C) 2014, Wolf Walter.
    Copyright (C) 2013, Stefan Helmert.
    GPL v2.

    Functionality:
        Generation of Digi-Key ordering system compatible BOM 

    How to use this is explained in eeschema.pdf chapter 14.  You enter a command line into the
    netlist exporter using a new (custom) tab in the netlist export dialog.  The command is
        on Windows:
            xsltproc -o "%O.csv" "pathToFile\bom2groupedCsv.xsl" "%I"
        on Linux:
            xsltproc -o %O.csv pathToFile/bom2groupedCsv.xsl %I

    Extended Functionality:
        Quote all fields, so that delimiter characters in a field isn't misinterpreted as a field delimiter.
        Limit the count of references per row in the "Reference" field.
        Make parameters of some values, so that they can be changed at run time.
        Parameters are:
            refcount - count of references per row in the field "Reference" - default is 10.
            d        - field delimiter character - default is ','.
            s        - reference delimiter character in the field "Reference" - default is ','.
            q        - field quote character - default is '"'.  (the escaped quote character is composed of two quote characters in the CSV format)
        To change a parameter, you can edit this file or insert &#45;&#45;stringparam param_name param_value after xsltproc in the command above:
             xsltproc &#45;&#45;stringparam "refcount" "5" &#45;&#45;stringparam "d" ";"  -o %O.csv pathToFile/bom2groupedCsv.xsl %I
        Here &#45;&#45; is used instead of a double hyphen because double hyphens are not allowed in xml comments.
        Also only the Linux variant is shown, because the  changes are identical for Win and Linux.
-->

<!--
    @package
    Generate a Tab delimited list (csv file type).
    Components of the same type grouped per line
    Fields are
    Ref, Quantity, Value, Footprint, Datasheet, Field6, Field7, ...
-->

<!DOCTYPE xsl:stylesheet [
    <!ENTITY nl  "&#xd;&#xa;">    <!--new line CR, LF, or LF, your choice -->
]>


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="text"/>

    <!-- parameter for count of references -->
    <xsl:param name="refcount">10</xsl:param>
    <!-- parameter for field delimiter -->
    <xsl:param name="d">,</xsl:param>    <!-- a field delimiter character (FDC) of your choice - one of ',' or ';' -->
    <!-- parameter for reference delimiter -->
    <xsl:param name="s">,</xsl:param>    <!-- an in-field reference delimiter character of your choice -->
    <!-- parameter for quotes -->
    <xsl:param name="q">"</xsl:param>    <!-- a quote character (QC) of your choice - one of '"' or "'" -->

    <!-- for Muenchian grouping of footprint and value combination -->
    <xsl:key name="partTypeByValueAndFootprint" match="comp" use="concat(footprint, '-', value)" />

    <!-- for table head and empty table fields-->
    <xsl:key name="headentr" match="field" use="@name"/>

    <!-- replacing template -->
    <xsl:template name="string-replace-all">
        <xsl:param name="text" />
        <xsl:param name="replace" />
        <xsl:param name="by" />
        <xsl:choose>
            <xsl:when test="contains($text, $replace)">
                <xsl:value-of select="substring-before($text,$replace)" />
                <xsl:value-of select="$by" />
                <xsl:call-template name="string-replace-all">
                    <xsl:with-param name="text" select="substring-after($text,$replace)" />
                    <xsl:with-param name="replace" select="$replace" />
                    <xsl:with-param name="by" select="$by" />
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$text" />
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <!-- main part -->
    <xsl:template match="/export">
        <xsl:value-of select="$q" />
        <xsl:text>Reference</xsl:text>
        <xsl:value-of select="$q" />
        <xsl:value-of select="$d" /><xsl:text> </xsl:text>
        <xsl:value-of select="$q" />
        <xsl:text>Quantity</xsl:text>
        <xsl:value-of select="$q" />
        <xsl:value-of select="$d" /><xsl:text> </xsl:text>
        <xsl:value-of select="$q" />
        <xsl:text>Value</xsl:text>
        <xsl:value-of select="$q" />
        <xsl:value-of select="$d" /><xsl:text> </xsl:text>
        <xsl:value-of select="$q" />
        <xsl:text>Footprint</xsl:text>
        <xsl:value-of select="$q" />
        <xsl:value-of select="$d" /><xsl:text> </xsl:text>
        <xsl:value-of select="$q" />
        <xsl:text>Datasheet</xsl:text>
        <xsl:value-of select="$q" />

        <!-- find all existing table head entries and list each one once -->
        <!-- if the QC is found in the value of the column header, replace it with two QC -->
        <xsl:for-each select="components/comp/fields/field[generate-id(.) = generate-id(key('headentr',@name)[1])]">
            <xsl:value-of select="$d" /><xsl:text> </xsl:text>
            <xsl:value-of select="$q" />
            <xsl:call-template name="string-replace-all">
                <xsl:with-param name="text" select="@name" />
                <xsl:with-param name="replace" select="$q" />
                <xsl:with-param name="by" select="concat($q,$q)" />
            </xsl:call-template>
            <xsl:value-of select="$q" />
        </xsl:for-each>

        <!-- all table entries -->
        <xsl:apply-templates select="components"/>
    </xsl:template>

    <xsl:template match="components">
        <!-- for Muenchian grouping of footprint and value combination -->
        <xsl:for-each select="comp[count(. | key('partTypeByValueAndFootprint', concat(footprint, '-', value))[1]) = 1]">
            <xsl:sort select="@ref" />
            <xsl:text>&nl;</xsl:text>
            <!-- list of all references -->
            <xsl:value-of select="$q" />
            <xsl:for-each select="key('partTypeByValueAndFootprint', concat(footprint, '-', value))">
                <xsl:sort select="@ref" />
                <xsl:value-of select="@ref"/>
                <xsl:if test="position() != last()">
                    <xsl:choose>
                        <xsl:when test="(position() > 0) and (position() mod $refcount) != 0">
                            <xsl:value-of select="$s" /><xsl:text> </xsl:text>
                        </xsl:when>
                        <xsl:when test="(position() > 0) and (position() mod $refcount) = 0">
                            <xsl:value-of select="$s" /><xsl:text>&nl;</xsl:text>
                        </xsl:when>
                    </xsl:choose>
                </xsl:if>
            </xsl:for-each>
            <xsl:value-of select="$q" /><xsl:value-of select="$d" />
            <!-- quantity of parts with same footprint and value -->
            <xsl:value-of select="$q" />
            <xsl:value-of select="count(key('partTypeByValueAndFootprint', concat(footprint, '-', value)))"/>
            <xsl:value-of select="$q" /><xsl:value-of select="$d" />
            <xsl:value-of select="$q" />
            <xsl:value-of select="value"/>
            <xsl:value-of select="$q" /><xsl:value-of select="$d" />
            <xsl:value-of select="$q" />
            <xsl:value-of select="footprint"/>
            <xsl:value-of select="$q" /><xsl:value-of select="$d" />
            <xsl:value-of select="$q" />
            <xsl:value-of select="datasheet"/>
            <xsl:value-of select="$q" />
            <xsl:apply-templates select="fields"/>
        </xsl:for-each>
    </xsl:template>

    <!-- table entries with dynamic table head -->
    <xsl:template match="fields">

        <!-- remember current fields section -->
        <xsl:variable name="fieldvar" select="field"/>

        <!-- for all existing head entries -->
        <xsl:for-each select="/export/components/comp/fields/field[generate-id(.) = generate-id(key('headentr',@name)[1])]">
            <xsl:variable name="allnames" select="@name"/>
            <xsl:value-of select="$d" />

            <!-- for all field entries in the remembered fields section -->
            <xsl:for-each select="$fieldvar">

                <!-- only if this field entry exists in this fields section -->
                <xsl:if test="@name=$allnames">
                    <!-- content of the field -->

                    <!-- if the QC is found in the value, replace it with two QC -->
                    <xsl:value-of select="$q" />
                    <xsl:call-template name="string-replace-all">
                        <xsl:with-param name="text" select="." />
                        <xsl:with-param name="replace" select="$q" />
                        <xsl:with-param name="by" select="concat($q,$q)" />
                    </xsl:call-template>
                    <xsl:value-of select="$q" />
                </xsl:if>
                <!--
                If it does not exist, use an empty cell in output for this row.
                Every non-blank entry is assigned to its proper column.
                -->
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>