Linking to Excel?

[supton]

So I poked around several months ago, to make some PCB’s for a coworker, and figured out what I needed to, to make the PCB’s. Suitably impressed, and way cheaper than the tool I use in my day job (Altium). Which leads me to the one thing I haven’t figured out: linking to an Excel “database”.

Yes, yes, I know, I know… evil. But I figured out how to do that years ago, and it served me well. All my passives, and most of my commonly used parts, live in a multi-tab “database” that is easily read, edited, and updated. I link to it by library reference, comment and footprint; it works well for my needs. I can do all my schematic entry, get what I want drawn up, link to the database, pull all the parameters. Check the report to see what didn’t pull parameters, update just those manually, done.

So I’ve been poking around, and I see some InvenTree link, which I should check out. But… end of the day, if I copy my Excel file into Libre Office(?), can I wind up with a workflow that I am already used to? [Part of my goal is to move my hobby PCB’s out of Altium, on my work PC, to my home PC, which is Linux. At the same time, I do see a need at work to use KiCAD so pulling from both Excel and Libre might be something I wind up doing.]

I’m probably overlooking some feature (Active BOM?) and should just re-calibrate my workflow, but one step at a time.

I did flip through the manual, on how to link to database, but I’m not sure if ODBC linking does this? I have a few other things to read up on as I get KiCAD installed and I figure out how I want to set it up for my purposes. I am no programmer, much less database knowledgeable. Am I just missing the obvious, and need to do more RTFM?

[paulvdh]

KiCad has an interface to work with external databases, and because all databases vary, this needs some programming to set up. From what I understand, it should not matter much whether your database is from an ODBC source or from a spreadsheet (or multiple spreadsheets). It just needs a bit of “different programming”. I do not use this part (nor any database) myself, but I guess there are tools to connect a database with a spreadsheet too.

[Piotr]

@supton

I link to it by library reference, comment and footprint; it works well for my needs. I can do all my schematic entry, get what I want drawn up, link to the database, pull all the parameters. Check the report to see what didn’t pull parameters, update just those manually, done.

I don’t understand what exactly you really need - may be what I do is enough.
I don’t know what new database features inserted in KiCad V7 are and how to use them.

I started with KiCad V4 and what I prepared those time (little modified recently) still works for me.
I have all my elements (not only passives) in spreadsheet (LibreOffice). I can easily edit them there.
In one column there is value, in second footprint, in third they are concatenated by spreadsheet function, in forth I have description to be used at BOM that can contain several lines with alternatives.
From KiCad I generate csv bom sorted by values and footprints (don’t remember exact name to select for bom, I’m writing from PC where I can’t have KiCad V7 because of Windows7).
That csv I insert into second tab in my spreadsheet (may be there are several insertion ways - I certainly have to set that fields are separated by commas and some other info (if you want details - ask, I was doing it half year ago last time, but I can check it and write exact). I insert it shifted several cells to the right.
There are some ‘comment’ lines in csv I skip (I open csv in text editor, select everything from some line down and Ctrl+C), but you may copy with them - your data will be few lines down.
Then in lines to left of it I have fields filled with spreadsheet functions to concatenate value and footprint from cells of just inserted csv, and to search the same text in third column of first tab to copy from there the description to this second spreadsheet tab. You see if some elements can’t be found in your database so you can add them at first tab and they are at once corrected at second tab.
That way on the left of second tab I have my BOM, but containing equations and not simple text. I select interested area, copy it and in second spreadsheet I paste special (only texts) and I have BOM.
That needed no programming at all (except using spreadsheet functions what same people name programming).
My description can look complicated but when you have that ready it works fast and easy.

To remind myself how to do everything at 2 top lines of second tab I have just description with important information like to what cell to insert csv, what is the range to copy to BOM and how to call the right paste function, and what parameters during that paste are important. As I then copy only important area to second spreadsheet those help text don’t disturb anything.

[craftyjon]

@supton

I’m not sure if ODBC linking does this?

You need an ODBC driver to connect to whatever your data source is.

I have never tried connecting to a spreadsheet file through ODBC on Linux, but a quick search found this commercial product that claims to enable it: Download ODBC Driver for Microsoft Excel. 30-Day Free Trial

I’m not sure if a free/open-source solution exists (but I didn’t search for very long)

If it were me, I’d convert the Excel file to a sqlite database, and use that. There are spreadsheet-like tabular editing tools for SQL databases (e.g. DBeaver or https://sqlitebrowser.org/ )

[marekr]

@craftyjon

have never tried connecting to a spreadsheet file through ODBC on Linux, but a quick search found this commercial product that claims to enable it: Download ODBC Driver for Microsoft Excel. 30-Day Free Trial

Excel ODBC is built into Windows (or after you install office at least). You don’t need extra commerical junk.

Simply open the Data Source Administrator and you will find “Microsoft Excel Driver”

[craftyjon]

@marekr

Excel ODBC is built into Windows (or after you install office at least). You don’t need extra commerical junk.

The OP wants to run on Linux

[BlackCoffee]

@supton

if I copy my Excel file into Libre Office(?), can I wind up with a workflow that I am already used to?

“Can I…” Try It yourself…

Curiosity got the best of me

I use Libre-Office and it’s Database ‘Base-Database’.

I Created a BOM in Kicad (using ‘bom_csv_grouped_by_value’) then used Base-Database to create a db. Very easy…

Had difficulty locating Libre’s On-Line documentation so, screenshot is posted below.

Image below shows result after loading the BOM-csv - I did not create Report/Form/etc…
I used the ‘Spreadheet’ tab - I did not try the Text/CSV tab. I imagine it too would work…

[EDIT: Yes, the Text/CSV tab worked too but, instead of easier selection approach, I needed to select the Kicad’s project folder then, Libre auto-selected the CSV file.] Results were the same…

[supton]

Great, thank you! I will give that a try in the near future, maybe tonight. I hadn’t touched KiCAD in nearly a year, and this was the one detail I couldn’t figure out last time I was in it.

[supton]

@Piotr

I don’t understand what exactly you really need - may be what I do is enough.
I don’t know what new database features inserted in KiCad V7 are and how to use them.

See if I can explain… I’m a bit new here, so I don’t know how the forum works.

In Altium, I drop a resistor onto my page, from my generic resistor library:

[supton]

I think I can upload only one picture at a time? apologies for posting several times, pictures say a thousand words and all…

I set “comment” to the 1.00k resistor I want to use, and set footprint (in my case, it’s “1206 res”).

[supton]

I run a function “update parameters from database”

[supton]

tell it what I want to update

[supton]

it makes an ECO
and then my parameters are updated.

[supton]

My “database” is an Excel file with all of those parameters in it. There is a setting file which I tell Altium how to link, what the lookup key is that is to say, and then what parameters I want to update/overwrite.

Later on I export to Excel (or csv in KiCAD).
[No picture needed here. ]

I was able to import my Excel into Libre, just have to figure out next step…

[jmk]

@supton

I think I can upload only one picture at a time?

You need to open one more topic and read nine more posts to self promote yourself, after which, you you will have no restrictions for posting pictures.

https://forum.kicad.info/t/new-member-information/38391

[BlackCoffee]

@supton

What you’ve described in posted images is darn-near (99%) Exactly the same process in Kicad - just called different words.

Doing the sequenced-steps you show (via images) BUT in Kicad is:

Assuming you have a Kicad Schematic open:
• Place a Symbol (or, Create one)
If placing a Symbol, Icon on side (can change values after placed)
If creating a Symbol, Click the Icon in Top Menubar…
You can set/change Values and Footprint anytime
• Menubar|Click Tools>Update_Symbols_From Library>Select what you want (be Careful!)
• Menubar|Click Tools>Generate_BOM
(select a CSV one…) Now, you got a CSV to use as you need…

You would have discovered all of the above if spending only 15 minutes clicking and exploring… (and, Trying it…)

[supton]

@jmk

You need to open one more topic and read nine more posts to self promote yourself, after which, you you will have no restrictions for posting pictures.

https://forum.kicad.info/t/new-member-information/38391

Ah, thanks (and apologies to all who I just spammed!).

@BlackCoffee

What you’ve described in posted images is darn-near (99%) Exactly the same process in Kicad - just called different words.

Doing the sequenced-steps you show (via images) BUT in Kicad is:

Assuming you have a Kicad Schematic open:
• Place a Symbol (or, Create one)
If placing a Symbol, Icon on side (can change values after placed)
If creating a Symbol, Click the Icon in Top Menubar…
You can set/change Values and Footprint anytime
• Menubar|Click Tools>Update_Symbols_From Library>Select what you want (be Careful!)
• Menubar|Click Tools>Generate_BOM
(select a CSV one…) Now, you got a CSV to use as you need…

You would have discovered all of the above if spending only 15 minutes clicking and exploring… (and, Trying it…)

Alright, digging into that… I see update from library, but not update from database at the moment (not that it will work, as I don’t have a lookup key set yet). Still making sense of the help documentation, I think I need to parse through this and set this up first, then update from library can point to this database.

[supton]

Ok, I see, I make this file, add it to the symbol library table (haven’t gotten that far just yet).

Question: Does this support more than single key lookup? I usually use 3 fields for the key (comment/value, footprint, symbol name). It doesn’t say “no” but it doesn’t seem to indicate yes either. Guess I’ll find out soon enough.

[supton]

Not seeing it just yet… The lookup key appears to need to be unique, but singular. I need to think about that a bit, it’s getting too late for my brain to process new data now. What I want to do is to match KiCAD “value” to what is called “comment”. Am guessing the fastest way is to change the database column heading to be “value” instead? last crazy thought of the day before turning in…

[Piotr]

@supton

In Altium, I drop a resistor onto my page, from my generic resistor library:

Don’t knowing new features in KiCad I think, understand the main difference between your way and my way.
Many years ago we tried to use Protel 3 to get all information needed for BOM but later we decided to make BOM externally. To not use element that we don’t plan to use and find it very late after generating BOM we decided that after placing element at schematic we don’t change its any parameters. So we had in Protel library separate symbol for each resistor value we use. And I copied that assumption when in 2017 I moved to KiCad. I have in KiCad libraries only elements I can use.
That way to use new element I have to add it in KiCad library and in my spreadsheet, and you do it only once - I think it is the main difference.
The habit to not edit values at schematic helps to keep our elements warehouse as small as possible and using new element is always after consideration.
We had a semi-automatic placement machine that had limited number of drawers and we prefered to have the same element set for as many as possible different PCBs so limiting the values used also helped.
I must understand what KiCad gives in this area and may be change totally my way.