search

AeybelV / PartPal

A tool for generating, optimizing, management, and sharing of Bills of Materials (BOMs). Integrates with EDA tools to streamline component sourcing and assembly workflows.
GNU General Public License v3.0
5 stars 0 forks source link

Implement BOM CSV/Excel Parsing #2

Open AeybelV opened 3 weeks ago

AeybelV commented 3 weeks ago

Write function parse BOM files in CSV or Excel format. They should most importantly extract product number and quantity, as well as any other relevant data. The BOM should be validated to ensure all required fields are filled, and handle missing or incorrect data gracefully.

dimembermatt commented 3 weeks ago

current CSV format used for BOM export for all of my KiCAD projects:

"Reference","Value","Datasheet","Footprint","Qty","DNP","#","Cost","Distributor","LCSC Part #","Manufacturer","Notes","P/N","Description"
"C1,C6","2.2u","https://product.tdk.com/system/files/dam/doc/product/capacitor/ceramic/mlcc/catalog/mlcc_automotive_midvoltage_en.pdf?ref_disty=mouser","Capacitor_SMD:C_2220_5750Metric_Pad1.97x5.40mm_HandSolder","2","","1","3.47","Digikey","","TDK","X7T, 250VDC, 20% tol","CGA9P3X7T2E225M250KA","Unpolarized capacitor, small symbol"
"C2,C3,C7","18u","https://www.mouser.com/datasheet/2/447/KEM_A4072_A759-3316694.pdf","Capacitor_THT:C_Radial_D10.0mm_H16.0mm_P5.00mm","3","","2","2.27","Digikey","","KEMET","","A759MS186M2CAAE090","Polarized capacitor"
"D1,D4","150","https://www.vishay.com/doc?86138","Diode_SMD:D_SMA","2","","3","0.51","Digikey","","Vishay Electronics","Rated above Vpk*1.05 but below V_DS of FET.","BZG03C150-HM3-08 ","Zener diode"
"D5","SMAJ150CA","https://www.littelfuse.com/media?resourcetype=datasheets&itemid=75e32973-b177-4ee3-a0ff-cedaf1abdb93&filename=smaj-datasheet","Diode_SMD:D_SMA","1","","4","0.43","Digikey","","Littelfuse","","SMAJ150CA","400W bidirectional Transient Voltage Suppressor, 150.0Vr, SMA(DO-214AC)"
"F1,F2","Fuse","https://www.mouser.com/datasheet/2/240/media-3321374.pdf","footprints:littelfuse_65600001009","2","","5","1.70","Digikey","","Littelfuse","","65600001009","Fuse"
"J1","IN-","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","6","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J2","IN+","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","7","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J3","OUT+","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","8","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J4","OUT-","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","9","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"L2","L_Ferrite","~","","1","","10","30.27","Remington Industries","","Remington Industries","","21SNSP","Inductor with ferrite core"
"Q3,Q4","EPC2059","https://epc-co.com/epc/Portals/0/epc/documents/datasheets/EPC2059_datasheet.pdf","footprints:epc_EPC2059","2","","11","3.32","Digikey","","EPC","","EPC2059","170V 25A 9mE"
"U1","CPV-PQ32_20-1S-12P-Z","https://www.ferroxcube.com/upload/media/product/file/Acc_ds/433506026791.pdf","footprints:ferroxcube_CPV-PQ32_20-1S-12P-Z","1","","12","2.37","Digikey","","Ferroxcube","","CPV-PQ32/30-1S-12P-Z","COIL FORMER VERTICAL"

Added fields:

Ideal expectation is Distributor, P/N (manufacturer P/N, typically), Qty, and Cost are pulled in, Distributors are sourced for all P/N, and Cost per part at Qty is updated. A more comprehensive report at the PCB or order level might provide additional detail like Qty discount that affects final order distributors.

AeybelV commented 2 weeks ago

current CSV format used for BOM export for all of my KiCAD projects:

"Reference","Value","Datasheet","Footprint","Qty","DNP","#","Cost","Distributor","LCSC Part #","Manufacturer","Notes","P/N","Description"
"C1,C6","2.2u","https://product.tdk.com/system/files/dam/doc/product/capacitor/ceramic/mlcc/catalog/mlcc_automotive_midvoltage_en.pdf?ref_disty=mouser","Capacitor_SMD:C_2220_5750Metric_Pad1.97x5.40mm_HandSolder","2","","1","3.47","Digikey","","TDK","X7T, 250VDC, 20% tol","CGA9P3X7T2E225M250KA","Unpolarized capacitor, small symbol"
"C2,C3,C7","18u","https://www.mouser.com/datasheet/2/447/KEM_A4072_A759-3316694.pdf","Capacitor_THT:C_Radial_D10.0mm_H16.0mm_P5.00mm","3","","2","2.27","Digikey","","KEMET","","A759MS186M2CAAE090","Polarized capacitor"
"D1,D4","150","https://www.vishay.com/doc?86138","Diode_SMD:D_SMA","2","","3","0.51","Digikey","","Vishay Electronics","Rated above Vpk*1.05 but below V_DS of FET.","BZG03C150-HM3-08 ","Zener diode"
"D5","SMAJ150CA","https://www.littelfuse.com/media?resourcetype=datasheets&itemid=75e32973-b177-4ee3-a0ff-cedaf1abdb93&filename=smaj-datasheet","Diode_SMD:D_SMA","1","","4","0.43","Digikey","","Littelfuse","","SMAJ150CA","400W bidirectional Transient Voltage Suppressor, 150.0Vr, SMA(DO-214AC)"
"F1,F2","Fuse","https://www.mouser.com/datasheet/2/240/media-3321374.pdf","footprints:littelfuse_65600001009","2","","5","1.70","Digikey","","Littelfuse","","65600001009","Fuse"
"J1","IN-","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","6","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J2","IN+","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","7","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J3","OUT+","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","8","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"J4","OUT-","https://www.mouser.com/datasheet/2/215/771-SEMS-1622293.pdf","footprints:keystone_electronics_7771_SEMS","1","","9","0.53","Digikey","","Keystone Electronics","","7771-SEMS ",""
"L2","L_Ferrite","~","","1","","10","30.27","Remington Industries","","Remington Industries","","21SNSP","Inductor with ferrite core"
"Q3,Q4","EPC2059","https://epc-co.com/epc/Portals/0/epc/documents/datasheets/EPC2059_datasheet.pdf","footprints:epc_EPC2059","2","","11","3.32","Digikey","","EPC","","EPC2059","170V 25A 9mE"
"U1","CPV-PQ32_20-1S-12P-Z","https://www.ferroxcube.com/upload/media/product/file/Acc_ds/433506026791.pdf","footprints:ferroxcube_CPV-PQ32_20-1S-12P-Z","1","","12","2.37","Digikey","","Ferroxcube","","CPV-PQ32/30-1S-12P-Z","COIL FORMER VERTICAL"

Added fields:

  • Distributor
  • Manufacturer
  • P/N
  • LCSC Part # (Pulls into JLCPCB plugin for assembly)
  • Notes
  • Cost

Ideal expectation is Distributor, P/N (manufacturer P/N, typically), Qty, and Cost are pulled in, Distributors are sourced for all P/N, and Cost per part at Qty is updated. A more comprehensive report at the PCB or order level might provide additional detail like Qty discount that affects final order distributors.

When you refer to Distributor and Cost, should those be required fields? Shouldn't PartPal instead fill those out. I was thinking at minimum minimum fields be P/N and Qty. PartPal will query all distributors and strategies the optimal BOM. That is find the best way to divide up the components between the distributors to find the best BOM-dstributor makeup optimized for cost (component costs and shipping).

Unless you mean something like "Preferred Distributor". For example, heres a OctoPart BOM where you provide a preffered distributor and it will query that distributor only for that component. OctoPart gets stocking and cost from the distributor and determines procurability and cost.

My idea is PartPal does the same, but unless specificaly provided with a distributor, will query everything to get the best BOM distributor makeup. Of course you can choose different strategies as well.