Pcb component order csv parsing to kicad library database table?

External Plugins
1

I am kind of thinking a tool that would read the component type, quantity, price, manufacturer number, etc… so that it could be used in Kicad component database library.
It would need to be able to parse multiple csv files and in case the component is already in existing db row, then it would need to just increase the quantity instead of creating a new row to table.

In simple resistor and cap type components it could even set the footprint information as 0402/0603 information is usually available in component description for those components and could thus be parsed from there.

Before starting to write this myself, I am wondering has anybody made available a script or plugin that would parse the digikeys or mousers component order csv file (mouser offers excel that can be converted to csv…) and store the information to sqlite database table/tables?

2

A web search shows heaps of CSV importers for sqlite, not KiCad specific.

3

Yes, I looked also some python examples. Most of them thought read whole CSV and just dumped that (or some selected columns) into db table with single command call. I want however use some kind of for loop and check each CSV row separately and then check whether to create new entry to db table or just increase the quantity count of some component that is already in db.

I watched Jon Evans 5 month old youtube introduction to kicad db libraries and based on that I found that there is example db in kicad source code in qa/data/dblib/database.sqlite

This shows one way to implement the db based library. Instead of having single table, there the each component type has own table. (Well only the Caps and resistors in that example). If that’s becoming the golden standard, I rather follow that than try to define own db schema.

$ sqlite3 qa/data/dblib/database.sqlite
SQLite version 3.39.2 2022-07-21 15:24:47
Enter “.help” for usage hints.

sqlite> .tables
Capacitors Resistors

sqlite> .schema Capacitors
CREATE TABLE IF NOT EXISTS “Capacitors” (
“Part ID” TEXT,
“Manufacturer” TEXT,
“MPN” TEXT,
“Symbols” TEXT,
“Footprints” TEXT,
“Description” TEXT,
“Value” TEXT,
“Capacitance” TEXT,
“Voltage Rating” TEXT,
“Capacitance Tolerance” TEXT,
“Dielectric Type” TEXT,
“Case/Package Size” TEXT,
“Verified” INTEGER DEFAULT 0, Cost numeric, Mass double,
PRIMARY KEY(“Part ID”)
);
sqlite> .schema Resistors
CREATE TABLE IF NOT EXISTS “Resistors” (
“Part ID” TEXT,
“Manufacturer” TEXT,
“MPN” TEXT,
“Symbols” TEXT,
“Footprints” TEXT,
“Description” TEXT,
“Value” TEXT,
“Resistance” TEXT,
“Power Rating” TEXT,
“Resistance Tolerance” TEXT,
“Case/Package Size” TEXT,
“Verified” INTEGER DEFAULT 0,
PRIMARY KEY(“Part ID”)
);

4

The way to do this is with an SQL ‘view’. This is a form of virtual table that is available on the database. server. You need to write a little SQL on the server to select a subgroup of your database by, for instance selecting the 0603 resistors. You can have multiple tables for different component types - it’s up to you how you split it up. The view tables can be used as an import source for the KiCad database config file. One of the advantages is you can set different permissions on the tables.

5

I was thinking that I would propably create the “digikey” and “mouser” tables where I would just dump the content of their specific CSV files without any modifications. After that I played with the idea of creating the “components” table as a view compining those.

But for now after testing a little ended up just creating single “components” table which will contain all different types components on same table. There is single “value” column which content is parsed both in the digikey and mouser case from the “descriptor” column.
The value is component type specific, so LEDs will have colors, while caps and resistors, etc. will have numbers.

6

I have now created the first prototype version of parser that can parse at least those CSV files I have from digikey and mouser and store their content to sqlite database.
It’s written in python and tested on linux. For leds, resistors, diodes, capacitors and inductors it will also try to find the values for footprint and value-columns as that information is usually included somewhere in the descriptor field.

If somebody is interested in for testing or giving feedback, the project is avaiable on

There is example script that will parse one csf file both from digikey and mouser that is included in the project. It can be executed simple by running script:

./db_content_import_test.sh

Mika

7

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