Linking to Excel?

[Heath_Raftery]

This is a useful thread and I’m watching with interest.

The OP described only applying a “Comment” and a “Footprint” to the generic symbol and then “Updating from library”. For that to work there needs to be a bit of a fuzzy search that finds all records that match a few things. I like the framing of this as a “search” rather than a “lookup”.

Can the OP’s goals be achieved by choosing “Change Symbol…” rather than “Update Symbol…”? So plonk down generic resistors and modify their fields as you go. Then when you’re ready, you search for all components with generic identifier. For each one, “Change Symbol” and pick the best match from your atomic parts database library.

The iteration over each symbol could be scripted within KiCad, but KiCad is explicitly not a GUI for querying a database, so picking the right part might still be a manual step. However I could imagine, if this workflow was important, the script could do the query search on KiCad’s behalf, and return the correct identifier.

FWIW, this is essentially my workflow at the moment, except instead of my own database I use the rather substantial databases maintained by FindChips/DigiKey/etc. And instead of selecting the component, I enter its details using the Symbol Fields Table interface, which makes this pretty fast. But I’m always interested in ways to streamline it, and doing database lookup and retrieval is definitely high on my list of opportunities.

[craftyjon]

This is the kind of thing that I expect people to implement with add-on plugins once the schematic editor has an API.

KiCad’s libraries don’t work this way: you can’t put down a generic symbol and then pick and choose certain fields that you want to take from a database after the fact. You need to set up your database so that it contains all information, including which symbol to use, ahead of time.

[marekr]

@dzid26

In addition, I have a suspicion that Excel is not SQL

| | | Documentation | KiCad

The purpose of the ODBC driver is to make Excel into SQL. The driver translates. The documentation just has bad wording.

[BlackCoffee]

@supton / other’s… I still think this post has gone astray from Kicad’s main focus.

That said, and because we Geeks like to ‘geek’, I think it may be helpful for you to post a pared-down db file that you want Kicad to be able to interface with - that will go further than re-hashing the same thing and, just may lead to some usable solution…

We need only a few representative lines of stuff in db file as long as they contain the stuff you want us to work with…

[supton]

@BlackCoffee

I still think this post has gone astray from Kicad’s main focus.

Before I go down the path of paring down a db for you to look at, can you quantify the main focus? I really don’t want anyone to go off and feel obligated to do something for me, I have no expectations for someone else to go write code for me, not since it seems there is some consensus that the path I’ve been using is not quite industry standard. Let alone outside of KiCAD’s main focus.

[BlackCoffee]

Leaving it where it is (the end) is a good solution.

[gkeeth]

I have to say I don’t agree that this is getting off topic. I think discussion about whether a built-in KiCad feature would work for a user’s workflow is totally on topic and relevant here.

[craftyjon]

@supton

the path I’ve been using is not quite industry standard. Let alone outside of KiCAD’s main focus.

While KiCad does not support the lack of a primary key (and probably won’t start supporting that), the rest of what you want to do is definitely within KiCad’s focus.

[BlackCoffee]

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…)

[supton]

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.

[craftyjon]

@supton

Something that I do, but does not seem industry standard, is to list all DNP’s

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)

[BlackCoffee]

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…

[supton]

@craftyjon

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)

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.]

@BlackCoffee

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…

Hey I am in no position to complain. 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!

[BlackCoffee]

@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)

[BlackCoffee]

@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

[supton]

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.

[supton]

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)

[BlackCoffee]

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…

[supton]

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!