Feature Request - produce bom.csv as part of the build

[martinbudden]

As well as bom.txt and bom.json it would be useful to have bom.csv in a format that could be imported into a spreadsheet and then used to estimate the cost of a project.

[nophead]

CSV isn't going to work because the part descriptions contain commas.

[nophead]

Perhaps the description can be quoted.

What is really needed is a database of parts, urls where they can be bought and the price, so the parts on the BOM can cross reference that to produce a costed BOM.

[martinbudden]

Enclosing the description in quotes works fine for importing into a spreadsheet.

What is really needed is a database of parts, urls where they can be bought and the price, so the parts on the BOM can cross reference that to produce a costed BOM.

Well, let's not the better be the enemy of the good. Just getting a plains .csv file would be very useful.

But one way this could be implemented - the build system could look for a parts.csv file which would contain entries of the form:

part description, price per unit, optional url of vendor

and use that to populate the spreadsheet. Not sure how items that are priced per length (ie belts, wires etc - eg 2 GT2 belts, 500mm) should be handled.

[nophead]

Rather than the description it should be the vitamin call (as shown on the library readme) with a mechanism to mark one or more of the arguments to be wildcards for matching and multiplying into the price.

[martinbudden]

Thinking about this a bit more, I'm not sure that there is anything to be gained by having the parts.csv file - maintaining it will be no easier than maintaining the spreadsheet created by importing the bom.csv file.

Having a parts.csv will mean adding new parts is probably easier, that means regenerating the bom.csv and re-creating the spreadsheet. However the spreadsheet may have had work done on it, ie formatting and possibly re-ordering rows, so that work would have to be redone.

There is also the issue of handling rounding of part lengths. For example some vendors sell belts by the meter, so belt lengths would need to be rounded up to 1m. However 2 belts, each of 400mm will up to 1m, not the 2m obtained by rounding each belt up individually.

There is also the issue of parts that are often purchased together, so for example stepper motors often come with cables. But your project might need a cable longer than the cable that comes with the stepper motor.

In short, the parts database will be complicated to implement and won't necessarily save the project owner any effort in maintaining their BOM spreadsheet.

[nophead]

When we sold Mendel90 kits I processed the BOM with a Python script that drove OpenOffice to make the costed BOM. It had all the logic for belts, price breaks, etc and did all the cell formatting. The part specific code was just extract fields from the part number and then quantise but there were odd special cases like adding postage per order and things like the sheet that makes the bed was free because it was cut out of the hole in the gantry. Might be difficult to encode all that.

[nophead]

Perhaps it is as simple as writing a function that matches the vitamin module call that returns a price.

[martinbudden]

Perhaps it is as simple as writing a function that matches the vitamin module call that returns a price.

Except that actually makes things more difficult for the project owner. It replaces the simple action of editing a number in a spreadsheet with the need to write a function.

I think you are trying to solve the wrong problem. The difficulty is getting the BOM into the spreadsheet, ensuring that no parts are omitted and that the part counts are correct. Once that has been done, the act of putting the prices into the spreadsheet is straightforward, and indeed probably easier than recording the prices anywhere else.

[nophead]

Well I have made it optional. If you don't supply a file of functions it just puts the description and count columns in. If you have a parts.py then it calls functions in which return a price and a url. If a function is missing the call is printed.

[nophead]

Pushed a782d43e67f4091f44bd9018817e7263e2944477, see https://github.com/nophead/NopSCADlib/blob/master/docs/usage.md#costed-boms

[martinbudden]

Great. However it turns out LibreOffice doesn't like single quotes. If I try and directly open this file with LibreOffice, then it tries to split the columns on the commas within the quotes. If I globally replace single quotes with double quotes in a text editor, then I can open the file directly with LibreOffice.

[nophead]

It can't use double quotes because they are used as inches in various descriptions. Just change the string delimiter in the Text Import dialog. It gets remembered. This is LibreOffice:

[martinbudden]

Yes, I tried that. Look at row two in your dialog. Column 1 is 'Wire blue 30/0.25mm strands - note no closing single quote Column 2 is len - can't see the rest, but presumably it contains the closing single quote.

As for the use of " for inches, how do you deal with that in bom.json?

[nophead]

Although it looks wrong in the dialogue it imports correctly.

I probably use html escape codes in the html.

[nophead]

This is what gets imported:

Double quotes don't matter elsewhere because they are not quoted when they appear in HTML tables.

[nophead]

The dialog also corrects itself if you toggle the delimiter back and forth. Seems to be a buglet in LibraOffice. Version: 7.0.6.2 (x64)

[martinbudden]

Yes, this seems to work fine, and you're right it does look like there is a bug in the LibreOffice dialog code.

I think this issue can be closed.