This is quite different from what OP is describing. I think you’re talking about putting metadata into symbols manually, and exporting to a spreadsheet (as a BOM).
The critical thing OP wants is for the data to be pulled from a spreadsheet that already contains the metadata. This is what the database libraries feature does, although as mentioned it’s typically a “real” database rather than a spreadsheet.
No, you need a single key for KiCad.
True, I suppose but, to me the Important aspect to Know is that 'Once the Meta Data (Label Fields) are associated with a Symbol, using them in CSV or Database is simple and is a Choice user can make.
Further, user can Edit one of the Kicad BOM (python) scripts to customize it with Labels/Fields… Then, use the BOM tool. I’ve done a few of them - easy to do with basic knowledge… Once the BOM is created, use the desired Database program…
That said, To me, this his Morphing beyond using Kicad
There are several ways to get a Customized database from Kicad and the choice is up to User.
I conclude (my contribution) with Two Video’s showing:
• Adding Custom Fields to a Symbol (I used some of OP’s Field-Names and Values. Could be a Stock Kicad Symbol or User’s Symbol) And, can create a default template for it…
(can do individually and/or in Bulk Copy&Paste, shown in Video)
• Exported CSV from the Bulk-Edit Panel (I did Not use a BOM) for this one but, I did use a BOM in previous posts
(Note: In Libre, use ‘Spreadsheet Connection’ to ensure compatibility with Database…)
Video resolution is low (to keep file size within 4meg limit for posting). Did Not care about making these for Academy Award…
I am not sure if you understand this or not, but with the OP’s desired workflow (database libraries), you don’t put the metadata in the symbols. You keep symbols without any metadata, and instead store that metadata outside of KiCad (in a database)
So the user doesn’t want to “get a database from KiCad”, they want to have KiCad talk to a database.
Yes, I understand that. Thus, that is why I stated,
OP’s info/screenshot of what to put in a file is posted above…
Have you actually tried that?
It looks like a different kind of junk:
In addition, I have a suspicion that Excel is not SQL 
KiCad currently only supports ODBC connections to SQL databases.
Excel is actually a SQL able database if you wish. You can run sql queries towards excel yes. I wouldn’t do it but it does work.
Appreciate the help from all. I was trying to avoid asking something that was in the “just go read the manual” category… sounds like, the single key lookup nixes what I am after. Which is fine, I will just have to figure out different method for my KiCAD projects, that’s all. The Excel file I use in Altium, I started that like 15 years ago and once I had a process, just stuck to that process, right way or not–it worked for me.
I’ll go back to reading the basics now and poking around, and then later on figure out how, if possible, to merge two EDA tools to use one database. [As it is, after I started on this, I realized, at work I’m not allowed to create an Access database, not without lots of approvals. So making an LibreOffice database, while very interesting, isn’t something I can take back to work.] Or have a different workflow for KiCAD projects, different methods for a different EDA tool.
Worst case (best case?), I could learn how to do simple string programming, read in a file, look for keywords, edit, etc. Several years ago I had a coworker write a program that would import an Excel BOM, compare against the Excel database, and update as necessary. I wonder if a similar workflow could be done in KiCAD: close the schematic file, run some script that opens the schematic, looks for any element, finds a match to the DB, and then updates parameters accordingly. When the schematic file was opened up, all the updated parameters would be seen. Way, way beyond my ability at the moment.
Again, thank you all.
Why? Can you say more about why this is a dealbreaker? It is extremely common to need a “primary key” in a database (whether that databse is an Excel spreadsheet or not). If by “lookup” you just mean search, you can bring in as many columns as you want and search all those columns, you just need a column that contains unique values per row. This is typically an internal or manufacturer’s part number.
Because I don’t do that now. I do not have a unique p/n in my Altium schematics; by using 3 keys I can make a pretty dumb schematic (drop a resistor, assign the 1206 footprint, give it a value of 1.15k - run database linking - that finds a link to a MPN, VPN and any other parameters I want). In my day job I never saw a need to give to every component some sort of unique “never to be used again” internal reference number. We sell few circuit boards, at least of my designs, internal use only, and low qty runs.
Put differently, to use a “primary” key, I would have to place the resistor, remember how to manually write the key into the value, then do linking. That key could be “res_1206_1.115k” or “res_1206_1%_1k15” or what have you. I agree that could be done, as a new process, a new method of doing things. It would not be impossible to come up with some manner to create this “smart number” key entry.
If you will never have more than one 1206 1.15k resistor in your database, you can generate a key column as a generated column in the database (equivalent: excel column with a formula containing string concatenation). Extend this idea as far as you want if there are other columns you want to merge together into the unique ID.
The main reason to do this is because “RES-12345” is a lot shorter than “resistor thin-film 1206 1% 250mW”, and the schema stays the same even if you later realize that you need to distinguish between that one and a pulse-withstanding variant, or something like that. You can then assign any number of different manufacturer part numbers to map to that internal part number (an approved vendor list, or AVL).
When you try to use a subset of the part’s properties to uniquely identify it, you run the real risk of the system breaking down and failing to disambiguate two different parts, because it’s really hard to guess at all the properties you need to tell apart when establishing the system.
At the moment, that is correct, we only really use low power parts. If I need a pulse rated resistor, then that’s “not standard”. I’ll look it up on Digikey, manually add the fields, and put an asterix into the value field (or something similar to break linkage, maybe “1.15k pulse rated”) so that it will never find a match and overwrite. Sometimes we’ll make a dedicated library for just that symbol, perhaps local to just the project, depends on a few particulars.
Limited? yes, but it’s been working for years. It starts to fray around the edges once I get out of passives and into anything active, those it starts to be easier to not have in the database, and rather just have all the data in the symbol itself… until it’s a jellybean 24C04 or LM358 or OP-27, which change MPN&VPN every few years. Then “always” updating the DB with all commonly used items has value.
There’s another method, where the schematic symbol library has a symbol for every allowed resistor value. I want to say, there’s yet another method where you have one symbol but somehow the value (and footprint?) are a pulldown, again somehow to make all that inside of the EDA tool. I never investigated those methods.
Library management, no small task once your team gets past a few people sitting in adjacent cubes.
Don’t disagree. Maybe as I play with KiCAD and learn some new methods, I’ll figure out what new method I want to use in KiCAD, and maybe I’ll migrate that back to Altium. 
Part of the team was looking into something like this, giving every DB entry a unique ID, but the tyranny of the urgent got in the way, and the idea died on the vine.
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.
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.
The purpose of the ODBC driver is to make Excel into SQL. The driver translates. The documentation just has bad wording.
@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…
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.
Leaving it where it is (the end) is a good solution.
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.
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.
