Converting text BOM to spreadsheet

Quite a few BOMs here are text files, not spreadsheets. I don't know why

I am looking for a way to convert the variable-size columns from the text files to a tab-delimited file suitable for import into a spreadsheet. I think this can be done with Grep in something like BBEdit, but my brain can't wrap itself round the logic of Grep. IMHO this kind of thing is what the GUI and the Macintosh were invented for

Any suggestions for turning this kind of file into something useful?

1 1N914 DIODE CR1
2 FDH333 DIODE HIGH CONDUCTANCE CR2,CR3 DO NOT SUBSTITUTE
1 1N4740A ZENER CR6 10V
4 1N4003 RECTIFIER CR7,CR8,CR9,CR10
2 2N5457 TRANSISTOR FET Q1,Q11
4 2N3391 TRANSISTOR BIPOLAR Q2,Q3,Q4,Q14
7 2N3707 TRANSISTOR BIPOLAR Q5,Q7,Q8,Q9,Q10,Q12,Q13 Q7-Q10 HFE >250;Q12-Q13 MATCHED
1 2N3053 TRANSISTOR BIPOLAR Q6 USE TO-5 HEATSINK
1 270R RESISTOR 1/4 WATT R4
2 27k RESISTOR 1/4 WATT R5,R28
2 2.2M RESISTOR 1/4 WATT R6,R7
3 1k RESISTOR 1/4 WATT R8,R81,R82
1 560k RESISTOR 1/4 WATT R9
4 10k RESISTOR 1/4 WATT R10,R31,R35,R66
1 82 RESISTOR 1/4 WATT R11
1 920 RESISTOR 1/4 WATT R12
2 1M RESISTOR 1/4 WATT R13,R36
1 22k RESISTOR 1/4 WATT R14
2 6.8k RESISTOR 1/4 WATT R15,R17
1 3.6k RESISTOR 1/4 WATT R16 ON METER BOARD
3 180 RESISTOR 1/4 WATT R18,R50,R84
2 68k RESISTOR 1/4 WATT R19,R26 R19 RATIO BOARD
3 56k RESISTOR 1/4 WATT R20,R21,R78 R78 RATIO BOARD
5 47k RESISTOR 1/4 WATT R22,R38,R46,R52,R53
1 250k POTENTIOMETER R23 AUDIO TAPER
3 2.7M RESISTOR 1/4 WATT R24,R25,R74
6 1.5k RESISTOR 1/4 WATT R27,R63,R64,R68,R69,R72 R63 RATIO BOARD
1 1.2M RESISTOR 1/4 WATT R29
1 150k RESISTOR 1/4 WATT R30
1 39R RESISTOR 1/4 WATT R32
2 560R RESISTOR 1/4 WATT R33,R62
3 8.2k RESISTOR 1/4 WATT R34,R76,R77
1 470k RESISTOR 1/4 WATT R37

Bonus points, of course, for recognising the BOM


Nick Froome

Pretty much every spreadsheet program (Open Office, Google Sheets, MS Excel, etc) allow importing a document with a custom delimiter.

Just import with the record delimiter changed from tab to a ' ' (e.g. a space) as the delimiter, and it should go right in to the sheet.

I'll go on a limb and say that there can be no simple grep for this.

Problem is that " " is used both as field delimiter and word delimiter.

1 3.6k RESISTOR 1/4 WATT R16 ON METER BOARD

Hand-fixing with "*" for field and "_" for word we should arrive at:

1*3.6k*RESISTOR*1/4_WATT*R16*ON_METER_BOARD

If it were _only_ these resistor records, we could write a grep to recognize that "WATT" always merges with the number (fraction!) before it, and that stuff after a series of "R??" strings is a final field on its own. But the transistors, diodes, diode_high_conductance, caps(?) all need their own rules.

"Column mode" text editor is some slight help. When a bunch of lines are or can be made same-format, column select lets you change a vertical bunch of spaces to tabs.

> this kind of thing is what the GUI

Bah, give me a VT-100. If you gonna have to type, a good keyboard is much more useful than fancy fonts.

Importing with a space as a delimiter just makes the problem worse

I'll settle for isolating the quantities. So I want to change

number - space - description

into

number - tab - space - description


Sounds easy, doesn't it? isolate the first digit in the list (there are no quantities >9 so one digit will do) and replace the space after it with tab-space

In Grep, ^ finds the start of a line which is a nice shortcut. I think a period (.) represents any character so I want to convert

^..

into

^. \t.

I have got that far but the syntax for Grep eludes me and always has done

Surely there must be a simple GUI-based way of doing this? A Better Finder Rename does a great job of simplifying renaming. This is just a variation on that

I'll have a look for a column mode text editor

I used to use Filemaker Pro for this kind of thing as it makes text calculations easy to do. I don't have an OSX version and it's too darned expensive to buy

Nick Froome

Your sample BOM is so irregular and small that I'd just open it in a text editor and insert tabs manually. Finding some universally applicable algorithm for tokenizing your sample BOM is impossible - it's too irregular.

All is very well, but I don't really get the underlying assumption: WHY would you want a materials list inside a spreadsheet?

Jakob E.

Some assembly houses like that format? Seems less error prone than a pile of ASCII separated into lines.

gyraf said:

All is very well, but I don't really get the underlying assumption: WHY would you want a materials list inside a spreadsheet?

Click to expand...

That's a good question. Because a spreadsheet allows you to:

* add unit costs & part numbers for ordering
* calculate the total cost before committing to a project
* add checkboxes to check off parts after ordering
* add notes for specific components
* add notes for errata

I tend to break projects into case hardware, psu, semiconductors, caps, pots, resistors, switches and knobs so I know where I am


Nick Froome

..but all this could as well be done on a text file or even on a hand-written piece of paper, couldn't it? I mean, this is DIY after all, not really process-optimized mass-production necessitating colorful graphs in board briefings..? I'm trying to figure out what sort of workflow would be eased by spreadsheeting, but come up empty?

(Not meant as criticism of the OP request!)

Jakob E.

I have tried a few ways and this seems to be ok.

Paste to Word document.
Select Show Paragraph Marks/Symbols or Ctrl shift 8.
Replace all offending spaces with no-break spaces Ctrl shift spacebar. Only change one ¼ Watt.
The spaces will appear as a grey dot and the no-break spaces will appear as a degree symbol while the Show Paragraph Marks is active.
Copy and paste to Xcel.
Select the first column. Select Data-Text to Columns.
Accept Delimited-Next.
Choose Space as delimiter-Next
Format column data-Set Column C to text so ¼ does not change to Jan 4-Finish.
Select the one ¼ Watt cell, Paste to the ¼ cells.
Select the Watt columns Delete-Delete Cells-Shift left.
Fix anything you missed.
Example: On Meter Board at cells F20,G20 and H20.
Select F20-Insert Cells-Shift Right.
Select G20-Enter formula:    =CONCAT(H20," ",I20," ",J20)
Select G20-Copy- Paste Special Values to F20.
Select cells G20 to J20-Delete-Delete Cells-Shift Left.

Jakob

I think knowing what a project will cost before you start is pretty important

You can do that on paper or in a Word document but  a spreadsheet makes it so much easier

Nick Froome

:%s/\v(d*)\s(\S+)\s(%([^\s^,]+\s)+)(%(\w+\d+)%(,\w+\d+)*)(.*)/\1\t\2\t\3\t\4\t\5/g

does the trick with vim.

It sorts pretty well apart from a couple of anomalies in Excel, very easily;

Cut and paste all the text into one cell on an Excel spreadsheet

Go to Data > Text To Columns > Delimited > Next > Space > Next > Finish

M

gyraf said:

All is very well, but I don't really get the underlying assumption: WHY would you want a materials list inside a spreadsheet?

Jakob E.

Click to expand...

You can import the spreadsheet ( xls ) format directly into many suppliers' webpages after you login (Mouser, etc) for an instant BoM or Project list. A lot of "1-click" computer things are not actually just 1-click, but uploading an xls file is pretty close.