Linking to Excel?

The ‘Truth’ is, I was reluctant to post ‘…Leaving it…’ because I do like to use my brain and solve problems. But, it appeared that OP was not interested so it made sense to post. That didn’t mean (to me) that I should not fool around with it if for no other reason than to keep my 75yr-old brain from rotting… New_Problems = New_Neural-Connections.

If OP posts a db file, all of us will look at it (no doubt…)

So I was able to convert to Libre database one file, but that was the other day, and I’m not figuring out again. I was able to pare down the Excel file from multiple tabs into just a few rows of a few tables.

AltiumPartDatabase.xls (56.5 KB)

I’ll try again in a bit to import into a DB, my brain needs a rest (I was going to take the day off, wound up working this morning, now it’s time for the afternoon nap).

I should point out, that there is a field called “comp_code”. It is not a unique ID. On my passives it is unique, but its purpose in life was a sorting code. I wanted my R’s listed first, followed by C’s, D’s, transistors, IC’s, hardware, PCB last on the BOM. Bit OCD I know. Anyhow, the R’s and C’s wound up with a unique code (as assigned to the symbol by the database) when linked, so as to get 1k’s to list before 2k’s–but the IC’s, not so much. They are grouped by function but not actually sorted, they come out willy-nilly. I have some OCD tendencies, but unlike my wife, it’s not spelled CDO.

I have a list on my computer that tells me what comp_code to use, for when setting up stuff.

Thing is, if I insert a new component into the list, I could change every comp_code to a new value, and when doing linking… overwrite any prior code. So don’t think of it as a key of any sort, it was used strictly for BOM ordering. Every time it links to the database, there is a chance that the code could be renumbered. And as you can now see, any given code could be used multiple times. Not a key!

I’m no python programmer at all, and as I look back what I did in the past, somehow I was able to modify the sorting program to use that… no idea what I did, nor how I did it…

Something that I do, but does not seem industry standard, is to list all DNP’s ('cept I call them DNI). I had too many problems with assembly if I did not list every component on the BOM. Thus DNI’s are listed. They get grouped together, all the DNI’d R’s together, then C’s, etc. That way an assembly house (or person) could see, when doing visual inspection, what was meant to be not populated, going from the BOM.

I think it is actually fairly standard in industry to include all DNP parts on the pick and place file, but not always the BOM (usually the BOM means “what needs to be purchased/sourced to build this”, so including DNP things does not make sense, but you do need a listing of all possible parts somewhere, and for that I have typically used the PNP file)

I’ll fool with it but not very soon - I’m teaching a FreeCad class next week and need to prepare and do winter yard work… may be able to squeeze some time in but, can’t commit to it…

Tip: Libre>Base_Database… that starts the Wizard - then… as follows:

Results of the above without any other/further clicking… Except for selecting the db and the tab (shown in Blue, to present the data…) that appears after it’s created…

Maybe it’s just my own experience, with the subset of vendors and internal persons I worked with? Dunno, just found it “best practice” for us to have everything on the BOM (or nearly so, fiducials and PCB markings are a don’t care). YMMV. But nice to know others don’t run into it, I would like to not pass along bad practice. [But if you look at my DB, that’s why DNI’s get parameters passed, its so they pass to BOM “properly” for my process.]

Hey I am in no position to complain. :slight_smile: If you find something amazing in your spare time, I’ll be eternally grateful. Well until the following week… something like that. Again, if KiCAD doesn’t do what I’m used to, well that’s just the way it is. I’m not looking to do the 100+ line item BOM’s that I do in my day job, some days I’m not sure I want to do that any more in my day job either…

Thanks on the pointer on importing, will play with that later.

Whaddya mean winter yard work? I was doing that last weekend, but I think it’s over with now, just got a couple inches of snow, guessing anything I didn’t do this year will be there for me come spring!

@supton

Can’t commit to taking this any further (maybe, maybe-not)…

Here’s what I did and it may be enough for you to continue from…

NOTES:
• Kicad needs a Symbol placed in the Schematic in order for it to use User’s added fields. Thus, I created a Symbol (named Null). I added Field’s that parrot those in your XLS.

• I Added a BJT transistor and entered some dumb values in the Fields - you can delete it from the Tempate.

• I created a User’s Template from it (titled: Your_Template1)
(Place it in location and set the Symbol’s Path to it). Video shows it but, I previously created the new project ‘hoping’ from it… Screenshot shows result of opening the schematic (in Text-Editor) and shows the Fields.

• See the_process graphic - these steps will get you to having a usable/readable text file from the db.

That’s where I leave it (it takes 7-weeks for all the Leaves to fall and get cleaned up (Maples, Oaks, Firs, Cedars…) so may not follow-up with this…

Now you know the process and once getting the .txt file, you can jimmy-up a script to read/parse it and create a lib that Kicad might be happy with.

Video shows some of it… Notice that I selected .csv (knowing that it wouldn’t be read by the code - needs to be a HRF (human readable file) so, then I select the .txt and you see the results.

If you want the Python code to hack and create a script, let me know…

The Template and the file, ‘hoping’ are in this Archive.zip
Archive.zip (614.4 KB)

@supton

This was Easy!

After I did what’s showed in Notes and Video (above), from the Schematic’s menubar I clicked BOM and selected the “bom_csv_grouped_extra” and I added my custom fields (some of your fields) to the end of the command-line. Bingo!!

And, after quitting and re-opening, the command-line with the fields remained as I had entered them.

I copied that bom script and edited it, now the comment section shows the additional fields! And, the fields/contents are in the BOM. Screenshot below

Just want to acknowledge I see that you responded; I took a quick look but work gets in the way of life; life gets in the way of play.

Finally getting back to this.

You said you had some python code you could share? If you could, that’d be great, I just spent a couple hours just reading up on python, and have gotten to the point where I could read in an xlsx, which is about 1% of what I could use.

[Hadn’t played with python before, what I know of C I could write on a postcard. Funny how the usage of colon and semicolon is completely opposite! ha! My first attempts at anything past “hello world”, I would write something and forget that colon, every time.]

Edit: spent much of my weekend coming up to speed on python. Took a stab at. Figured out how to read in Excel, and the schematic file, and to find links between the two. Next step: editing the schematic file from this script, which might wait for next weekend… I think I need to first spend some time learning python, as I know I’m not doing it right. [But it’s been a fun diversion on a rainy weekend.]

pythonTestLinking.zip (27.7 KB)

Congrat’s! Looks like progress! You’re on the way towards the finish-line…

When you get to the point of wanting to create a GUI panel and pop-up’s, you can code it in Python or, use the wxFormBuilder - it takes some learning but, it’s well worth it…

Thanks! I think it slows down a bit here, as I have a bit of reading left to do, then there’s plenty of polishing. Still, I think I have the tools now to do what I want, if I chose to go that way and finish up using a workflow similar to what I do in my day job. If I ever get it finished, I’ll make sure to post the end product, in case anyone else finds it useful.

Will look into that wxFormBuilder, not heard of it before (not surprising as I’m not a programmer), thanks for the reference. One step at a time! :slight_smile:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.